Page 1 of 1

What's wrong with my sql code?

Posted: Thu Nov 10, 2005 6:58 am
by Coxster
I have some sql code passed to php's mysql_querry() function built like this:

Code: Select all

$sqlTable = "CREATE TABLE " . DB_USERS_NAME . ", ";									// Table Name
		$sqlTable .= DB_USERS_0 . " INT NOT NULL AUTO_INCREMENT, ";							// User ID
		$sqlTable .= DB_USERS_1 . " VARCHAR(" . DB_USERS_1_SIZE . ") BINARY NOT NULL, ";	// Username
		$sqlTable .= DB_USERS_2 . " VARCHAR(" . DB_USERS_2_SIZE . ") BINARY NOT NULL, ";	// Password :?: 
		$sqlTable .= "PRIMARY KEY (" . DB_USERS_0 . ")";									// Set Primary Key
The error says there is a problem with the sql but I don't know enough of sql to see what the problem is. Can anyone else see what's wrong with my sql?

Posted: Thu Nov 10, 2005 7:08 am
by jayshields
First off, make sure your not using @ before your query execution line in your script.

If not, comment out the query execution and then echo out your query string right before you execute it in your script.

Now if you've got PHPMyAdmin try run the echo'd query through there, if it gives an error it will be more specific and you will probably be able to fix it from there.

If you can't, post PHPMyAdmin's error. If you don't have PHPMyAdmin, post the query your script echo'd after making the above changes. Also, post your full script so we can see how you are executing the query and what you defined the constants as.

Posted: Thu Nov 10, 2005 2:48 pm
by Coxster
Here's the SQL:

Code: Select all

'CREATE TABLE users, user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(20) BINARY NOT NULL, user_password VARCHAR(10) BINARY NOT NULL, PRIMARY KEY (user_id)'
Here's the function:

Code: Select all

function createUsersTable($passedLink)
	{
		$sqlTable = "CREATE TABLE " . DB_USERS_NAME . ", ";									// Table Name
		$sqlTable .= DB_USERS_0 . " INT NOT NULL AUTO_INCREMENT, ";							// User ID
		$sqlTable .= DB_USERS_1 . " VARCHAR(" . DB_USERS_1_SIZE . ") BINARY NOT NULL, ";	// Username
		$sqlTable .= DB_USERS_2 . " VARCHAR(" . DB_USERS_2_SIZE . ") BINARY NOT NULL, ";	// Password
		$sqlTable .= "PRIMARY KEY (" . DB_USERS_0 . ")";									// Set Primary Key
		
		echo "<br>SQL '" . $sqlTable . "'<br>";
		
		if (mysql_query($sqlTable, $passedLink))
		{
			echo "Table " . DB_USERS_NAME . " created successfully<br>";
		}
		else
		{
			echo 'Error creating table: ' . mysql_error() . "\n";
		}
	}
And here is the error from phpmyadmin:

Code: Select all

#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 ' user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(20) BINARY NOT NULL, use' at line 1

Posted: Thu Nov 10, 2005 3:07 pm
by feyd
invalid create table syntax..

http://dev.mysql.com/doc/refman/5.0/en/ ... table.html

Code: Select all

CREATE TABLE lookup
  (id INT, INDEX USING BTREE (id))
  ENGINE = MEMORY;

Posted: Tue Nov 15, 2005 2:59 pm
by Coxster
Thanks feyd, that fixed it (adding brackets) but now I get a 'No database selected ' error from mysql_error().

I've looked on google and some people say you need to use mysql_select_db().

I thought I wouldn't need something like that as it would use the same linkid that was last used, or is that not how it works?

Posted: Tue Nov 15, 2005 3:02 pm
by Coxster
Just fixed it by using mysql_select_db first.