Weird php/mysql error

corblimey

In the admin section of my php site, a user can add a new function (just a string) via a form. After posting the form, I'm checking that the function doesn't already exist in the table:

SELECT COUNT(*) FROM table WHERE field = '$postvalue';

This worked fine on my own machine before deploying to the server. However, I found out this morning, that this SELECT is failing on the server, with the message "error near ;"

After some trail and error, I remove the semi-colon and it works fine, but I've got semi colons at the end of every SQL statement in that file, and this is the only one throwing up an error, and only on the server.

Adam

Well if it works on your dev environment but not live, then 99% chance there are different versions of mysql. Look into that first.

seamus

Have you got a semicolon at the end of the SQL statement or at the end of the PHP statement? As Mirror says, it's most likely a version difference error.

corblimey

seamus wrote:

Have you got a semicolon at the end of the SQL statement or at the end of the PHP statement?

I have both . Different versions of mySQL was the first thing that sprang to mind, but why only this particular piece of sql? I have an INSERT two lines further down that works fine with the end ;

I don't use COUNT() anywhere else in the website, so I thought there was something there, but changing it to a standard SELECT without the COUNT returned the same error. It's rather weird.

I also tried running the statement as is (with the in the mysql administrator they provide on the server and it worked fine, although I guess that's no proof of anything really.

Adam

Can you post the chunk of code containing the query, and an example of another query on the same page?

louie

try this and se what you get:
[php]
$sSql = "SELECT COUNT(*) AS `count_records` FROM `table` WHERE `field` = '$postvalue'";
//if you want to see how it looks before executting uncomment next line
//echo $sSql; exit();
[/php]

corblimey

Mirror wrote:

Can you post the chunk of code containing the query, and an example of another query on the same page?

$sql  = "SELECT COUNT(*) FROM table WHERE field = '$value'; ";
//print $sql;
if (!$result = mysql_query($sql, $db))
  dieerror ("Query failed: ", $sql, "service.php");

$curcnt = 0;
if ($myrow = mysql_fetch_array($result))
  $curcnt = $myrow[0];
					
if ($curcnt == 0) {
  $sql = "INSERT INTO table (fields) VALUES (values); ";
} else {
  $error = "That section already exists";
}

...

The first one (COUNT) fails, but if I comment it out (or remove the semi-colon) the second one (INSERT) works fine. This is the chunk of code as is, there's no code removed.

I tried your change, louie, but same error:

SELECT COUNT(*) AS `count_records` FROM `table` WHERE `field` = '$postvalue'; 
You have an error in your SQL syntax near '; ' at line 1

louie

give us the echo line from this code:
[php]
echo "post value = ". $postvalue;
if(empty($postvalue)){
echo "you have no value to execute the sql";
die();
}
$sql = "SELECT COUNT(*) AS `count_records` FROM `table` WHERE `field` = '".$postvalue."' ";
//if you want to see how it looks before executting uncomment next line
echo $sql;
exit();
[/php]

corblimey

I don't think there's anything wrong with the $postvalue - the error I'm getting is showing the value that's been entered on the form. And as I said, I can copy the SQL verbatim into mysql admin in my server control panel and it works fine. It just appears to have a problem with the ; in this one particular SQL statement.

louie

That's because you shouldn't have an ";" in your statement.
If you echo the sql and postvalue you might get the answer to your problem.

corblimey

louie wrote:

That's because you shouldn't have an ";" in your statement.
If you echo the sql and postvalue you might get the answer to your problem.

Well, this is new one on me. I always always always put a semi colon at the end of my sql statements when running from php. The INSERT in my code above works fine with a semi colon at the end. It's obviously not required, since it works without it, but why is this one particular statement kicking up a fuss?

In the grand scheme of things, it doesn't much matter, the code is working now without the semi-colon, I was just confused.

louie

the semi-colon is used for ending a php line and shouldn't be part of the sql statement.

corblimey

Sorry all who replied to this thread. It was totally my fault. I don't endevery SQL statement with a semi colon, I never do. The only reason it works on the INSERT is because I actually had some old code whereby I originally had 2 SQL queries separated by a semi colon and then I was using a preg_split to run them separately. I then copied that piece of code (the INSERT statement) and used it for the COUNT but that uses a standard mysql_query. Hence error.

In other words, I'm a buffoon.