Redirect Error Reported As SQL Syntax Error

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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)
    echo("<center><p class=\"body\">Unable to delete Subcategory2.  Please try

    echo("<a href=\"product.php?action=edit&product_sku=$product_sku\">Edit

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

header("Location:product.php?action=edit&product_sku=<?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?



Re: Redirect Error Reported As SQL Syntax Error

Steve wrote:
Quoted text here. Click to load it

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

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!


convert uppercase WORDS to single keystrokes to reply

Site Timeline