sql syntax error

Titus

Ok, I got this script off the internet, on a PHP tutorial site. The only problem is, it won't work! I've bashed my brains out modifying it, and adding stuff from the books I have, but it won't work at all.

Thanks for any help you can give. I tested the script on two different accounts too.

The code:

<? 
    $usr = "@@@@"; 
    $pwd = "@@@@"; 
    $db = "@@@@"; 
    $host = "localhost"; 

# connect to database 
$cid = mysql_connect($host,$usr,$pwd); 
if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    } 

# setup SQL statement 
# create links table 
$SQL = " CREATE TABLE home  ("; 	
$SQL = $SQL . " id INT NOT NULL AUTO_INCREMENT, "; 
$SQL = $SQL . " main TEXT, "; 
$SQL = $SQL . " features TEXT, "; 
$SQL = $SQL . " PRIMARY KEY(id) );"; 
$SQL = " CREATE TABLE administer  (";
$SQL = $SQL . " main TEXT, "; 
$SQL = $SQL . " doing TEXT, "; 
$SQL = $SQL . " done TEXT, "; 
$SQL = " CREATE TABLE flavor  (";
$SQL = $SQL . " main TEXT, "; 
$SQL = $SQL . " options TEXT, "; 
$SQL = $SQL . " exoptions TEXT, "; 
$SQL = " CREATE TABLE how  (";
$SQL = $SQL . " main TEXT, "; 
$SQL = $SQL . " you TEXT, "; 
$SQL = $SQL . " it TEXT, "; 
$SQL = " CREATE TABLE order  (";
$SQL = $SQL . " main TEXT, "; 
$SQL = $SQL . " quick TEXT, "; 
$SQL = $SQL . " order TEXT, "; 
# execute SQL statement 
$result = mysql_db_query($db,"$SQL",$cid); 

# check for error 
if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    } 

# display result for table creation query 
# this should be 1 on a success 
echo ("Links Results RESULT = $result\n\n"); 

mysql_close($cid); 

?>

And this is the error I keep getting:

ERROR: You have an error in your SQL syntax near
'' at line 1 CREATE TABLE how ( main TEXT, you TEXT, it TEXT, Links Results RESULT =

Thanks in advance.

harlowhair

you have called one of your tables order, and it also has a field of the same name. order is a reserved word (as in ORDER BY in an sql statement) and you can't use it. Change the names to something like custorder and it will work. its not good practice to have tabls and rows with the same name either, i know from experience that it can get confusing

jerdo

Try not adding each part that way. Also the manual suggests not using the mysql_db_query() function.
Try this

$SQL = "CREATE TABLE home  (id INT NOT NULL AUTO_INCREMENT, main TEXT, features TEXT, PRIMARY KEY(id) )";
$result = mysql_query($SQL,$cid); 
$SQL = "CREATE TABLE administer (main TEXT, doing TEXT, done TEXT) "; 
$result = mysql_query($SQL,$cid);
$SQL = "CREATE TABLE flavor  (main TEXT, options TEXT, exoptions TEXT)"; 
$result = mysql_query($SQL,$cid);
$SQL = "CREATE TABLE how  (main TEXT,you TEXT, it TEXT) "; 
$result = mysql_query($SQL,$cid);
$SQL = "CREATE TABLE order  (main TEXT, quick TEXT, order TEXT)"; 
$result = mysql_query($SQL,$cid);

You could also continue to do it all in one statement like you were but you need to close the parathenses after you open them, which you didn't do after the first create statement.

Titus

Thanks for the replies, I'll try this out.

Titus

Mk, this is what I got:

ERROR: You have an error in your SQL syntax near 'order (main TEXT, quick TEXT, cusorder TEXT)' at line 1 CREATE TABLE order (main TEXT, quick TEXT, cusorder TEXT) Links Results RESULT =

The changed code, incase I did anything wrong:

<? 
    $usr = "heh..."; 
    $pwd = "heh..."; 
    $db = "comforwebsites_com"; 
    $host = "localhost"; 

# connect to database 
$cid = mysql_connect($host,$usr,$pwd); 
if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    } 

# setup SQL statement 
# create links table 
$SQL = "CREATE TABLE home  (id INT NOT NULL AUTO_INCREMENT, main TEXT, features TEXT, PRIMARY KEY(id) )";

$result = mysql_query($SQL,$cid); 

$SQL = "CREATE TABLE administer (main TEXT, doing TEXT, done TEXT) "; 

$result = mysql_query($SQL,$cid);

$SQL = "CREATE TABLE flavor  (main TEXT, options TEXT, exoptions TEXT)"; 

$result = mysql_query($SQL,$cid);

$SQL = "CREATE TABLE how  (main TEXT,you TEXT, it TEXT) "; 

$result = mysql_query($SQL,$cid);

$SQL = "CREATE TABLE order  (main TEXT, quick TEXT, cusorder TEXT)"; 

$result = mysql_query($SQL,$cid);


# check for error 
if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    } 

# display result for table creation query 
# this should be 1 on a success 
echo ("Links Results RESULT = $result\n\n"); 

mysql_close($cid); 

?>

Thanks

harlowhair

No matter how you break up your code you have still used the reserved word order. rename that table and the column and it will work!

I'm feeling pretty ignored here...........

Titus

OH! I gotcha! I thought you meant I couldn't have a cell named the same as a table (which I did). I'll change the table name asap.

Titus

Ok, one bug squashed, and another pops up. The code is the same as posted above , but the last table is renamed "buy".

The error is:

ERROR: No Database Selected CREATE TABLE buy (main TEXT, quick TEXT, cusorder TEXT) Links Results RESULT =

Dang, I hate it when C&P scripts don't work...

jerdo

Try making this the first line of your if statement:

mysql_select_db($db,$cid);

Don't feel bad about the being ignored on these boards, harlowhair, it happens all the time. You post an answer for someone they ignore it until 12 posts later when someone posts exactly what you already told them.

Titus

Hmm... now I get an access denied message.

ERROR: Access denied for user: 'brett@localhost' to database 'comforwebsites_com' CREATE TABLE buy (main TEXT, quick TEXT, cusorder TEXT) Links Results RESULT =

This wouldn't be a wrong password thing, would it? It still pops up with the last table I'm trying to make, and not the first.

jerdo

Make sure it's the right pass and that username is authorized to create tables on the db you've selected.

Titus

Yup, I'm authorized. I just changed the password after the error, and no banana. I'll test it on another domain now, might have to contact my host.

Titus

Ok, I tested it on my other domain, and didn't get a not authorized error. I did get this though.

ERROR: CREATE TABLE dynamic_buy (main TEXT, quick TEXT, cusorder TEXT) Links Results RESULT =

The only thing I have running on that database is my miniBB forums, and phpBB forums (not active).

Titus

Thanks much, it's working now. I had put the database select thing in the wrong if section.