error with mySQL syntax, trying to create table

[brem13]

this is the error i'm getting "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 '-girl ( `id` INT( 50 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR(' at line 1" when i run the page to create a user. here's the code i'm using, it happens sometimes, but not others, any help?

mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error());
mysql_select_db("singlese_messages") or die(mysql_error());
mysql_query("CREATE TABLE $username (
`id` INT( 50 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 75 ) NOT NULL ,
`subject` VARCHAR( 150 ) NOT NULL ,
`message` TEXT NOT NULL ,
`gift` VARCHAR( 50 ) NOT NULL,
`new` VARCHAR( 5 ) NOT NULL ,
`date_created` VARCHAR( 50 ) NOT NULL ,
`date_modified` VARCHAR( 50 ) NOT NULL 
)")  or die(mysql_error());  
mysql_close();

[brem13]

the part in the error "-girl" is part of the username thats used to create the table name

[brem13]

i tried it again, without the dash in the username and used an underscore instead and that worked, hmmmm???

[riwan]

you could enclosed the username for the code to work

mysql_connect("$server", "$db_user", "$db_pass") or die(mysql_error());
mysql_select_db("singlese_messages") or die(mysql_error());
mysql_query("CREATE TABLE `$username` (
`id` INT( 50 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 75 ) NOT NULL ,
`subject` VARCHAR( 150 ) NOT NULL ,
`message` TEXT NOT NULL ,
`gift` VARCHAR( 50 ) NOT NULL,
`new` VARCHAR( 5 ) NOT NULL ,
`date_created` VARCHAR( 50 ) NOT NULL ,
`date_modified` VARCHAR( 50 ) NOT NULL
)")  or die(mysql_error()); 
mysql_close();

[Mchl]

Seriously? A separate table for each user? That is not a good idea.

[Anzeo]

As Mchl points out, why are you creating a seperate table for each user? I suggest making one USER table (with the same create statement) and then inserting each user as a row into it.

[ignace]

i tried it again, without the dash in the username and used an underscore instead and that worked, hmmmm???

 

Surround it with ` and it will work. Otherwise the dash (-) is seen as an operator.

[brem13]

why is it a bad idea to have a separate table for each username, i figured that would be the best way, that way it doesnt have to search through a table full of everyones messages, only that user? how do you think i should make it, cuz this is the only way i know right now??

[Mchl]

It's a bad idea, because it will put (a lot) more stress on your database, than having just one table.

 

http://www.phpfreaks.com/blog/giuseppe-maxia-on-database-normalisation-and-smoking

[brem13]

duly noted, thanks dude

[Mchl]

Some more hints:

 

`date_created` VARCHAR( 50 ) NOT NULL ,
`date_modified` VARCHAR( 50 ) NOT NULL

 

use MySQL's DATE datatype

 

`message` TEXT NOT NULL ,

 

Won't VARCHAR(65000) be anough?

[brem13]

varchar(65000)?

[Mchl]

The upper limit is 65536

TEXT is limited at 4GB

[brem13]

and thats better than text? i'm not expecting a 4gb message

[Mchl]

TEXT is in fact a variation of BLOB. It requires MySQL to split your data, because TEXT and BLOB columns are kept separately from rest of your data, due to their potentially very alrge size. Try to think of some reasonable limit for message size, and just use VARCHAR.