trying to create username

[brem13]

i keep getting an error when trying to create a table in a database using a username with a dash or underscore in it. the error is

"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 '-2010 ( `id` INT( 50 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `username` VARCHAR(' at line 1"  any help? is there a problem with the way i use $username?

 

here is the code i'm using

$username = htmlspecialchars($_POST['username']);
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();

[premiso]

Why are you trying to create a table just for the user?

 

If I were you, I would read up on proper database design and the 3rd Normal Form.

[brem13]

its for messages, and the usernames will work with a dash n underscore, but wont go in this way...

[premiso]

If I were you, I would read up on proper database design and the 3rd Normal Form.

[Spikerok]

If problem with $username then use next code

$username = htmlspecialchars($_POST['username']);
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();

 

btw why do you need to create table for every new user?

[brem13]

i tried that and it still gave the same result

[kenrbnsn]

Do not create a table for every user. Rethink your database design.

[brem13]

how would i do that, im going to have a table for every user, that way i can have only messages for that user in the table

[ignace]

how would i do that, im going to have a table for every user, that way i can have only messages for that user in the table

 

K then now assume you have 10k users?

[ignace]

A proper database design could be:

 

CREATE TABLE message (
  id int(4) NOT NULL AUTO_INCREMENT,
  from_user_id int(4) NOT NULL,
  to_user_id int(4) NOT NULL,
  subject varchar(128),
  message text,
  
  FOREIGN KEY fk_message_from_user_id (from_user_id)
    REFERENCES user (id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY fk_message_to_user_id (to_user_id)
    REFERENCES user (id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

[brem13]

ok, i see where you're going, what does the bottom half do exactly?

 FOREIGN KEY fk_message_from_user_id (from_user_id)
    REFERENCES user (id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY fk_message_to_user_id (to_user_id)
    REFERENCES user (id)
      ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

[ignace]

It sets up data-integrity constraints, so that when you remove a user from the system you also delete his inbox/outbox.