...

View Full Version : Simple MYSQL Syntax error



hackerzlab
09-14-2011, 08:34 AM
I'm getting this error when i used the code below in my server. it works perfectly in my localhost. Could you please rectify the codes?

Error:
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 'Book ( ID INT(10) NOT NULL auto_increment, Timestamp VARCHAR(14) NOT NULL, Name ' at line 1

This is the MYSQL Code:

if($create == 'yes') {
$sql = "CREATE TABLE $tbl_name ( " .
"$fld_id INT(10) NOT NULL auto_increment, " .
"$fld_timestamp VARCHAR(14) NOT NULL, " .
"$fld_name VARCHAR(50), " .
"$fld_email VARCHAR(75), " .
"$fld_subject VARCHAR(50) NOT NULL, " .
"$fld_text TEXT NOT NULL, " .
"PRIMARY KEY ($fld_id))";
if(!mysql_query($sql)) echo '<div class="cssError">' . mysql_error() . '</div>';
else $table_exists = true;
}

abduraooft
09-14-2011, 10:53 AM
Please post the output of

echo $sql = "CREATE TABLE $tbl_name ( " .
"$fld_id INT(10) NOT NULL auto_increment, " .
"$fld_timestamp VARCHAR(14) NOT NULL, " .
"$fld_name VARCHAR(50), " .
"$fld_email VARCHAR(75), " .
"$fld_subject VARCHAR(50) NOT NULL, " .
"$fld_text TEXT NOT NULL, " .
"PRIMARY KEY ($fld_id))";

hackerzlab
09-14-2011, 01:53 PM
I get this error when i tried it from the phpmyadmin using the code given above in the SQL tab:


#1064 - 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 'echo $sql = "CREATE TABLE $tbl_name ( " . "$fld_id INT(10' at line 1

abduraooft
09-14-2011, 02:59 PM
I haven't asked you to put that PHP string in phpmyadmin. Just echo that string as shown above and post the result here so that we may be able to track the source of error.

Old Pedant
09-14-2011, 08:37 PM
Error is obvious:


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 'Book ( ID INT(10) NOT NULL auto_increment, Timestamp VARCHAR(14) NOT NULL, Name ' at line 1

TIMESTAMP is a reserved name in MySQL.

If you are going to use code like this to generate tables (which I personally think is a really really bad idea, but that's a topic for another discussion), then you really need to "escape" EACH AND EVERY table and field name, just in case of conflict. In MySQL, you "escape" using `...` (backticks...usually on same key as the ~ tilde).

So:


$sql = "CREATE TABLE `$tbl_name` ( " .
"`$fld_id` INT(10) NOT NULL auto_increment, " .
"`$fld_timestamp` VARCHAR(14) NOT NULL, " .
"`$fld_name` VARCHAR(50), " .
"`$fld_email` VARCHAR(75), " .
"`$fld_subject` VARCHAR(50) NOT NULL, " .
"`$fld_text` TEXT NOT NULL, " .
"PRIMARY KEY (`$fld_id`))";

Old Pedant
09-14-2011, 08:39 PM
By the by, why in the [censored] would you *EVER* use a field named TIMESTAMP and give it a data type of VARCHAR??????

The implication of $fld_timestamp, to me, is that the field *should* be of type TIMESTAMP.



"`$fld_timestamp` TIMESTAMP NOT NULL, " .

hackerzlab
09-15-2011, 08:14 AM
Thank You guys.. really appreciate the help. i've got it working :) :)

its an open source (2005) and i didn't code it myself. You guys are the best. Thanx again.

Old Pedant
09-15-2011, 05:12 PM
Ahhh...the joys of open source. It's free...and it's often bad coding. So you have to take the bad with the good. Best of luck with it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum