brem13 Posted May 29, 2010 Share Posted May 29, 2010 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(); Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/ Share on other sites More sharing options...
premiso Posted May 29, 2010 Share Posted May 29, 2010 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. Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065059 Share on other sites More sharing options...
brem13 Posted May 29, 2010 Author Share Posted May 29, 2010 its for messages, and the usernames will work with a dash n underscore, but wont go in this way... Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065063 Share on other sites More sharing options...
premiso Posted May 29, 2010 Share Posted May 29, 2010 If I were you, I would read up on proper database design and the 3rd Normal Form. Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065075 Share on other sites More sharing options...
Spikerok Posted May 29, 2010 Share Posted May 29, 2010 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? Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065081 Share on other sites More sharing options...
brem13 Posted May 29, 2010 Author Share Posted May 29, 2010 i tried that and it still gave the same result Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065083 Share on other sites More sharing options...
kenrbnsn Posted May 29, 2010 Share Posted May 29, 2010 Do not create a table for every user. Rethink your database design. Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065085 Share on other sites More sharing options...
brem13 Posted May 29, 2010 Author Share Posted May 29, 2010 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 Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065088 Share on other sites More sharing options...
ignace Posted May 29, 2010 Share Posted May 29, 2010 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? Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065104 Share on other sites More sharing options...
ignace Posted May 29, 2010 Share Posted May 29, 2010 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; Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065106 Share on other sites More sharing options...
brem13 Posted May 29, 2010 Author Share Posted May 29, 2010 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; Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065116 Share on other sites More sharing options...
ignace Posted May 29, 2010 Share Posted May 29, 2010 It sets up data-integrity constraints, so that when you remove a user from the system you also delete his inbox/outbox. Link to comment https://forums.phpfreaks.com/topic/203285-trying-to-create-username/#findComment-1065151 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.