What's wrong with my sql code?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Coxster
Forum Newbie
Posts: 14
Joined: Tue Nov 01, 2005 3:22 am

What's wrong with my sql code?

Post 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?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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.
Coxster
Forum Newbie
Posts: 14
Joined: Tue Nov 01, 2005 3:22 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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;
Coxster
Forum Newbie
Posts: 14
Joined: Tue Nov 01, 2005 3:22 am

Post 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?
Coxster
Forum Newbie
Posts: 14
Joined: Tue Nov 01, 2005 3:22 am

Post by Coxster »

Just fixed it by using mysql_select_db first.
Post Reply