MySql Error

[jwgrafflin]

I keep getting the same error:

Unable to update dataYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ssl='O',ecommerce='O',reseller='-',dedicated='-',vps='x',affiliate='CJ',green='-' at line 1


This code works just fine on another site, and all the other php pages work just fine. But this one is driving me nuts trying to figure out the problem.



<?php
$hostname=$_POST['hostname'];
$url=$_POST['url'];
$established=$_POST['established'];
$price=$_POST['price'];
$diskspace=$_POST['diskspace'];
$bandwidth=$_POST['bandwidth'];
$econplan=$_POST['econplan'];
$maillists=$_POST['maillists'];
$platform=$_POST['platform'];
$ssl=$_POST['ssl'];
$ecommerce=$_POST['ecommerce'];
$reseller=$_POST['reseller'];
$dedicated=$_POST['dedicated'];
$vps=$_POST['vps'];
$affiliate=$_POST['affiliate'];
$green=$_POST['green'];
$au=$_POST['au'];
$ca=$_POST['ca'];
$uk=$_POST['uk'];
$notes=$_POST['notes'];

include("dbinfo.inc.php");

$query="UPDATE webhosts SET hostname='$hostname',url='$url',established='$esta blished',price='$price',diskspace='$diskspace',ban dwidth='$bandwidth',econplan='$econplan',maillists ='$maillists',platform='$platform',ssl='$ssl',ecom merce='$ecommerce',reseller='$reseller',dedicated= '$dedicated',vps='$vps',affiliate='$affiliate',gre en='$green',au='$au',ca='$ca',uk='$uk',notes='$not es' WHERE hostname='$hostname'";
@mysql_select_db($database) or die( "Unable to select database" . mysql_error());
mysql_query($query) or die("Unable to update data" . mysql_error());
echo "<center><table><tr><td>";
echo "<h2>Record Updated</h2>";
echo "</td></tr></table></center>";
mysql_close();
?>

[NogDog]

Looks like you have a number of extraneous spaces in the (long) line where you define the query string. Maybe some weird word-wrap functionality in your editor? I'd suggest cleaning it and also making it easier to read and maintain by spreading the query over multiple lines:

$query = "
UPDATE webhosts
SET
hostname='$hostname',
url='$url',
established='$established',
price='$price',
diskspace='$diskspace',
bandwidth='$bandwidth',
econplan='$econplan',
maillists='$maillists',
platform='$platform',
ssl='$ssl',
ecommerce='$ecommerce',
reseller='$reseller',
dedicated='$dedicated',
vps='$vps',
affiliate='$affiliate',
green='$green',
au='$au',
ca='$ca',
uk='$uk',
notes='$notes'
WHERE hostname='$hostname'
";

[jwgrafflin]

Nope! No whitespace. And rearranging that long line as you suggested makes no difference. Still get the error.

[jwgrafflin]

Just noticed something. On the original error, it said ...at line 1

It now says ...at line 12

Line 12 is $ecommerce=$_POST['ecommerce'];

[NogDog]

If it's the same error message, then that means line 12 of the SQL statement, not of the PHP code.

I believe the problem is that "SSL" is a reserved word in MySQL (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html), so you will need to quote that column name with "`" characters:

`ssl`='$ssl',

[jwgrafflin]

I wondered about the reserved word probability, but then figured if that was the case, it wouldn't have allowed me to use it in the other mysql statements for the associated code pages. They all contain ssl and process with no errors.

Regardless, I changed the fieldname and the code works just fine now.

Thanks for your help.

[NogDog]

Glad we solved it. I think sometimes the parser knows that something is not a reserved word depending on the context where it was used. You can always play it safe by just back-tick quoting all your table and column identifiers. :)