Redirect Error Reported As SQL Syntax Error

I just spent waaaaaaaaaaaayy too much time trying to track down an error that was incorrectly reported just now, and I would like to see if someone
can explain to me why it was reported that way.

The purpose of the code is simply to delete a record and then redirect back to the page where the delete was started. The code looks like this:

elseif ($_GET[action] == "delete")
{
$query = "delete from product_subcategory2 where product_sku=$_GET[product_sku] and subcategory2_id=$_GET[subcategory2_id]";
$result = mysql_query($query) or die (mysql_error());
$affected_rows = mysql_affected_rows();

if ($affected_rows == 1)
{
header("Location:product.php?action=edit&product_s ku=$product_sku");
}
else
{
do_header();
echo("<center><p class=\"body\">Unable to delete Subcategory2. Please try again.</p>");
echo("<a href=\"product.php?action=edit&product_sku=$produc t_sku\">Edit Product</a>");
do_footer();
}
}

The SQL was find, but the error I had was in the header line. What I had was this:

header("Location:product.php?action=edit&product_s ku=<?php echo $product_sku?>");

so since I was already in PHP, I didn't need the "<?php echo" for $product_sku.

The problem was that the error message I got was "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near '' at line 1." Aside from being extremely descriptive (yeah, right), the error message had nothing to do with my SQL
syntax, since it executed fine. Can anyone explain why an error with header() was reported as an SQL syntax error?

Thanks.

Steve

Steve wrote:
> I just spent waaaaaaaaaaaayy too much time trying to track down an error
> that was incorrectly reported just now, and I would like to see if
> someone can explain to me why it was reported that way.
>
> The purpose of the code is simply to delete a record and then redirect
> back to the page where the delete was started. The code looks like this:
>
> elseif ($_GET[action] == "delete")
> {
> $query = "delete from product_subcategory2 where
> product_sku=$_GET[product_sku] and subcategory2_id=$_GET[subcategory2_id]";
> $result = mysql_query($query) or die (mysql_error());
> $affected_rows = mysql_affected_rows();
>
> if ($affected_rows == 1)
> {
>
> header("Location:product.php?action=edit&product_s ku=$product_sku");
> }
> else
> {
> do_header();
> echo("<center><p class=\"body\">Unable to delete Subcategory2.
> Please try again.</p>");
> echo("<a
> href=\"product.php?action=edit&product_sku=$produc t_sku\">Edit
> Product</a>");
> do_footer();
> }
> }
>
> The SQL was find, but the error I had was in the header line. What I
> had was this:
>
> header("Location:product.php?action=edit&product_s ku=<?php echo
> $product_sku?>");
>
> so since I was already in PHP, I didn't need the "<?php echo" for
> $product_sku.
>
> The problem was that the error message I got was "You have an error in
> your SQL syntax. Check the manual that corresponds to your MySQL server
> version for the right syntax to use near '' at line 1." Aside from
> being extremely descriptive (yeah, right), the error message had nothing
> to do with my SQL syntax, since it executed fine. Can anyone explain why
> an error with header() was reported as an SQL syntax error?
>
> Thanks.
>
> Steve

Because the problem with your header statement was responsible for a
malformed SQL query. In your redirect, product_sku was filled with junk
and you then used it to form a query. Something I learned from the
perl world is to ALWAYS AND WITHOUT EXCEPTION quote values in your SQL
queries, even when they are (supposed to be) numeric. It is also
standard practice to use uppercase for SQL keywords. This makes it
easier to spot SQL syntax errors.

$query = "DELETE FROM product_subcategory2 WHERE
product_sku='$_GET[product_sku]' AND
subcategory2_id='$_GET[subcategory2_id]'";

The above query, with '$_GET[product_sku]' quoted, should have prevented
the SQL error because the junk was quoted and therefore the SQL parser
ignored it. Of course, your application would not have worked...

Also, rather than just using die(), I find it helpful to print out the
SQL I am sending the server when things break:

if (!$result = mysql_query($query))
{
echo "<br />$query<br />\n";
die (mysql_error());
}

Better yet, while you are developing a new application, have a var
$debug that you can set/clear and use it throughout your code to print
all SQL queries prior to sending them to the server. I find LOTS of
stupid mistakes this way.

if ($debug)
{
echo "<br />$query<br />\n";
}
if (!$result = mysql_query($query))...

Hope that helps!

NM

--
convert uppercase WORDS to single keystrokes to reply