error in your SQL syntax........near '' at line 1

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

i have a payment success page that should update the database when a payment is correct but am getting the error

Code: Select all

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
on the success.php page, i have looked at the code but cant seem to see the error, i have changed the ' ' to "" on line one but that didn't do it so was hoping someone could look and see the incorrect syntax

Code: Select all

<?php require_once('Connections/chic.php'); ?>
<?php
// *** Update the stock *** 
$details_table = "chicJewls_orderdetails";
$ID_column = "UniqueID";
$details_prodID = "ProductID";
$details_qty = "Quantity";
$XStock_TableName = "chicJewls_products";
$XStock_FieldName = "Stock";
$XStock_unID = "ProductID";
if (!session_id()) session_start();
if (isset($_SESSION["orderID"])) {
  mysql_select_db($database_chic, $chic);
  $details_Source = "select * from " .  $details_table . " where " . $ID_column . " = " . $_SESSION["orderID"];
  $detailsRS = mysql_query($details_Source, $chic) or die(mysql_error());
  $row_detailsRS = mysql_fetch_assoc($detailsRS);
  do {
    $XStock_qtySource = "select " . $XStock_FieldName . " from " .  $XStock_TableName . " where " . $XStock_unID . " = " . $row_detailsRS[$details_prodID] . "";
    $XStock_rsUpd = mysql_query($XStock_qtySource, $chic) or die(mysql_error());
    $row_XStock_rsUpd = mysql_fetch_assoc($XStock_rsUpd);
    if ($row_XStock_rsUpd[$XStock_FieldName] > 0) {
      $XStock_new = $row_XStock_rsUpd[$XStock_FieldName] - $row_detailsRS[$details_qty];
      if ($XStock_new < 0) $XStock_new = 0;
      $XStock_UpdSource = "update " . $XStock_TableName . " set " . $XStock_FieldName . " = " . $XStock_new . " where " . $XStock_unID . " = " . $row_detailsRS[$details_prodID] . "";
      $XStock_rsUpd = mysql_query($XStock_UpdSource, $chic) or die(mysql_error());
    }
  } while ($row_detailsRS = mysql_fetch_assoc($detailsRS));
  $XStock_rsUpd = null;
  $detailsRS = null;
  session_unregister("orderID");
}
?>
thanks in advance
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: error in your SQL syntax........near '' at line 1

Post by twinedev »

First step in debugging errors like that if it isn't clear in the actual message is to actually echo out to the actual SQL statement, as many times when you see the whole thing, you will spot where you left out a comma, missed a space between items when concatenating two strings together, have mismatched quotes, or a variable isn't outputting something you were expecting, or worse, is outputting a quote that is giving you mismatched quotes. (Hopefully in your code you check the value that is going into your $_SESSION variables to make sure it only contains what you expect.)

As it is not good for security reasons to echo such information out (or even the error), I recommend doing the following:

In a file that is included with everything, (in your case, "Connections/chic.php") add the following in the section where you define your credentials:

Code: Select all

define('DEBUG_MODE', TRUE); // Set this to False when done testing!!!
Now where you have mysql_query statements, do something such as the following:

Code: Select all

$detailsRS = mysql_query($details_Source, $chic) 
  or die((DEBUG_MODE) ? mysql_error()."<br>\n".htmlspecialchars($details_Source,ENT_QUOTES) : '[ERR:'.__LINE__.'] There was an unexpected error.');
What you are doing with this is setting a constant throughout the whole program to say if you are in Debugging mode or not, so that if you are you can display more information upon error. The die statements is saying "If you are in Debug Mode, use what is between the ? and :, if not, use what is after the : (see http://php.net/manual/en/language.opera ... on.ternary for more details).

This way when you are in debug mode, it will stop and give you the error message like you get now, but also echo out the SQL statement. If are not in debug mode, you just get a message saying that the script had an error, but also through the "error code" know what line number it stopped on (For security reasons, it is best to not also show the filename (__FILE__), as knowing the URL and line number should get you narrowed down pretty well to where it happened)

Now, once you have the SQL statement, if you can't see the error, try manually executing the error on the database. Use a program like Navicat or a web based one such as phpMyAdmin (which for cPanel hosting accounts is usually available by default). Doing this will many times give you a better error message.

Also, now that you have the constant DEBUG_MODE in there, you can also output things in other places to help you while developing:

Code: Select all

if (DEBUG_MODE) { echo '<hr>['.__LINE__.'] Your message to display here<hr>',"\n"; }
-Greg
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: error in your SQL syntax........near '' at line 1

Post by Christopher »

Do you know which of the three queries in that code is causing the error? Have you displayed the SQL for each query to see if there is an error in it. For example, if $_SESSION["orderID"] or $row_detailsRS[$details_prodID] are empty then you will get an error. You should check that they are both set and contain a value. You could put quotes around them, but that does not really deal with the error condition.
(#10850)
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

Do you know which of the three queries in that code is causing the error?
no i dont, as suggested in a previous post i think i need to debug.

i was lookin at other post regarding this problem and a lot of people say it is to do with adding quotes etc...
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: error in your SQL syntax........near '' at line 1

Post by Christopher »

jonnyfortis wrote:no i dont, as suggested in a previous post i think i need to debug.
You should add an if() after every query so you have some structure to deal with errors:

Code: Select all

if (mysql_errno($chic) == 0) {
     // success
} else {
     $errormsg = mysql_error($chic);
}
jonnyfortis wrote:i was lookin at other post regarding this problem and a lot of people say it is to do with adding quotes etc...
That does not solve the problem. You need to wrap all this code is an if() that validates the parameters that may be empty or incorrect to deal with that condition.
(#10850)
User avatar
FernandoBasso
Forum Newbie
Posts: 13
Joined: Sun Dec 05, 2010 4:05 am
Location: Brazil

Re: error in your SQL syntax........near '' at line 1

Post by FernandoBasso »

You need quotes around the values themselves. Currently, your sql is resulting something like:

Code: Select all

UPDATE tb_name SET field_name = new stock item WHERE prod_a = 200
But you need the sql output something like this (note the quotes):

Code: Select all

UPDATE tb_name SET field_name = 'new stock item' WHERE prod_a = 200;
I would write the code like this to achive the result described:

Code: Select all

$XStock_UpdSource = "UPDATE $XStock_TableName  SET  $XStock_FieldName = '$XStock_new'  WHERE  $XStock_unID = $details_prodID;";
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

I would write the code like this to achive the result described:

$XStock_UpdSource = "UPDATE $XStock_TableName SET $XStock_FieldName = '$XStock_new' WHERE $XStock_unID = $details_prodID;";
I tried replacing

Code: Select all

$XStock_UpdSource = "update " . $XStock_TableName . " set " . $XStock_FieldName . " = " . $XStock_new . " where " . $XStock_unID . " = " . $row_detailsRS[$details_prodID] . "";
with

Code: Select all

$XStock_UpdSource = "UPDATE $XStock_TableName  SET  $XStock_FieldName = '$XStock_new'  WHERE  $XStock_unID = $details_prodID;";
but still the same error
User avatar
FernandoBasso
Forum Newbie
Posts: 13
Joined: Sun Dec 05, 2010 4:05 am
Location: Brazil

Re: error in your SQL syntax........near '' at line 1

Post by FernandoBasso »

Are you sure none of your variables are empty. Let's say $XStock_FieldName is empty, the resulting qurey would be something like:

UPDATE tb_name SET = 'new stock item' WHERE prod_a = 200;

which is wrong.

Can you output those queries and show us what they look like?

When I am testing queries, I tend to echo them and exit() immediately after, and then I paste
the query directly on the sql command line client to see whether they work or not.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

Are you sure none of your variables are empty.
i need to test
Can you output those queries and show us what they look like?
how and where do i echo out the query's? to find out what the results are?


thanks
User avatar
FernandoBasso
Forum Newbie
Posts: 13
Joined: Sun Dec 05, 2010 4:05 am
Location: Brazil

Re: error in your SQL syntax........near '' at line 1

Post by FernandoBasso »

Try:

Code: Select all

<?php require_once('Connections/chic.php'); ?>
<?php
// *** Update the stock ***
$details_table = "chicJewls_orderdetails";
$ID_column = "UniqueID";
$details_prodID = "ProductID";
$details_qty = "Quantity";
$XStock_TableName = "chicJewls_products";
$XStock_FieldName = "Stock";
$XStock_unID = "ProductID";
if (!session_id()) session_start();
if (isset($_SESSION["orderID"])) {
  mysql_select_db($database_chic, $chic);
  $details_Source = "select * from " .  $details_table . " where " . $ID_column . " = " . $_SESSION["orderID"];
  $detailsRS = mysql_query($details_Source, $chic) or die(mysql_error());
  $row_detailsRS = mysql_fetch_assoc($detailsRS);
  do {
    $XStock_qtySource = "select " . $XStock_FieldName . " from " .  $XStock_TableName . " where " . $XStock_unID . " = " . $row_detailsRS[$details_prodID] . "";
    $XStock_rsUpd = mysql_query($XStock_qtySource, $chic) or die(mysql_error());
    $row_XStock_rsUpd = mysql_fetch_assoc($XStock_rsUpd);
    if ($row_XStock_rsUpd[$XStock_FieldName] > 0) {
      $XStock_new = $row_XStock_rsUpd[$XStock_FieldName] - $row_detailsRS[$details_qty];
      if ($XStock_new < 0) $XStock_new = 0;

     /* You will also need to update the stuff above to have quotes where needed. */
     $XStock_UpdSource = "UPDATE $XStock_TableName  SET  $XStock_FieldName = '$XStock_new'  WHERE  $XStock_unID = $details_prodID;";

      /* LOOK HERE */
      echo $XStock_UpdSource;
      exit();
      /* NOTE: If this is not excecuted, then your code is not dropping into one of the IFs above. */

      $XStock_rsUpd = mysql_query($XStock_UpdSource, $chic) or die(mysql_error());
    }
  } while ($row_detailsRS = mysql_fetch_assoc($detailsRS));
  $XStock_rsUpd = null;
  $detailsRS = null;
  session_unregister("orderID");
}
?>
I believe you can remove all those concatenations (with the dot operator), and leave the sql queries inside double quotes, place extra single quotes on stuff that need to be quoted in the database. Numbers (like ID's need not to be quoted).
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

hmm,,i used your code but still gave me the

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

let me explain what i have done and tell me if i have done this wrong

i have made a test purchase and the payment has gone through they are then sent to this page. It was showing the above error and i have kept that page open, change the code on the page to the above, then re uploaded the page and refreshed the browser and the error is still show, will the work or do i need to make another purchase to start the process again?

thanks
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: error in your SQL syntax........near '' at line 1

Post by twinedev »

Have you tried what I suggested, to actually output the SQL statement to see what is actually being set to the database? Have to done as Christopher said and do a check to make sure the variables being used contain data?

Form the back and forth on guessing on how to put quotes around a value (that in the line before gets treated a NUMBER, which wouldn't need the quotes), I am guessing not.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: error in your SQL syntax........near '' at line 1

Post by jonnyfortis »

Have you tried what I suggested, to actually output the SQL statement to see what is actually being set to the database?
no i am going to do that next, this will help with error reporting correct?
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: error in your SQL syntax........near '' at line 1

Post by twinedev »

jonnyfortis wrote: this will help with error reporting correct?
This is good debugging steps. You get an error, you don't know what caused it, so you step back to what info was given to cause the error (in your case a SQL statement). The code I gave will also let you know what line number actually generated the error, as right now, you are guessing between them.

So first you check to see what was actually given as the statement, usually you will see where the problem is (ie, missing data from a variable, mismatched quotes, etc.) changing anything in the actual format of the sql statement without knowing what the finally "build" of it is (echoing out the variable with the complete SQL statement), well you are just guessing as what to change, and may end up fixing one thing while breaking another...

If after that, you can see that "Hey, I am missing info in the SQL", then you work back up your code, did the "empty" variable come from the result of another query? if so, start doing var_dump()'s to see what you are getting from that query and debug what may be wrong. Was is a session variable missing data, got test where it was supposed to get set...

Just keep working your way backwards, remember, if you have something loops, don't give up debugging because the first result worked, it could be the tenth record down is missing data that causes a query being executed in the loop to die.

-Greg

PS, the first change you made suggested by Fernando, you didn't have to make the change, as the value he was telling you you need quotes around looks to be from the code before that line a number, which doesn't matter if it has quotes or not. Adding quotes to compensate for the variable possibly being blank is just patching a existing problem, you should fix it at the root, make sure there is a value as Christopher said.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: error in your SQL syntax........near '' at line 1

Post by Christopher »

I recommended adding even more error checking code. I it surprising how often I see PHP code that does not follow basic Structured Programming block structures instead of die(). Then the next step would be to encapsulate this code in a Model object and use exception for errors.

Code: Select all

<?php require_once('Connections/chic.php'); ?>
<?php
$errmsg = '';
// *** Update the stock ***
$details_table = "chicJewls_orderdetails";
$ID_column = "UniqueID";
$details_prodID = "ProductID";
$details_qty = "Quantity";
$XStock_TableName = "chicJewls_products";
$XStock_FieldName = "Stock";
$XStock_unID = "ProductID";
if (!session_id()) session_start();
if (isset($_SESSION["orderID"]) && $_SESSION["orderID"]) {     // check both isset and if it contains a value
  mysql_select_db($database_chic, $chic);
  $details_Source = "select * from " .  $details_table . " where " . $ID_column . " = " . $_SESSION["orderID"];
  $detailsRS = mysql_query($details_Source, $chic);
  if (! mysql_errno()) {     // check if query succeeded
    $row_detailsRS = mysql_fetch_assoc($detailsRS);
    do {
      $XStock_qtySource = "select " . $XStock_FieldName . " from " .  $XStock_TableName . " where " . $XStock_unID . " = " . $row_detailsRS[$details_prodID] . "";
      $XStock_rsUpd = mysql_query($XStock_qtySource, $chic);
      if (! mysql_errno()) {     // check if query succeeded
        $row_XStock_rsUpd = mysql_fetch_assoc($XStock_rsUpd);
        if ($row_XStock_rsUpd[$XStock_FieldName] > 0) {
          $XStock_new = $row_XStock_rsUpd[$XStock_FieldName] - $row_detailsRS[$details_qty];
          if ($XStock_new < 0) $XStock_new = 0;
            $XStock_UpdSource = "UPDATE $XStock_TableName  SET  $XStock_FieldName = '$XStock_new'  WHERE  $XStock_unID = $details_prodID;";
            $XStock_rsUpd = mysql_query($XStock_UpdSource, $chic) or die(mysql_error());
            if (mysql_errno()) {     // check if query succeeded
              $errormsg = "SQL=$XStock_rsUpd, ERROR=" . mysql_error();
              continue;
            }
        }
      } while ($row_detailsRS = mysql_fetch_assoc($detailsRS));
      $XStock_rsUpd = null;
      $detailsRS = null;
      session_unregister("orderID");
    } else {
      $errormsg = "SQL=$XStock_rsUpd, ERROR=" . mysql_error();
    }
  } else {
    $errormsg = "SQL=$details_Source, ERROR=" . mysql_error();
  }
} else {
  $errormsg = 'Session OrderID not set.';
}
if ($errormsg == '') {
  // show output
} else {
  // show error information
  echo $errormsg;
}
?>
(#10850)
Post Reply