Mysql insert into and create table

[WatsonN]

Hey yall!

I'm working on a new site idea and I've run across a problem that I know is simple enough but I'm stumped. It's in the signup form

What I want to do is insert the new user into the 'Login' table and get the user id that was just created and use that to create a table with the user id in the name.

 

Here is what I have:

 // now we insert user into 'Login' table
mysql_real_escape_string($insert = "INSERT INTO `Login` (`UID`, `pass`, `HR`, `mail`, `FullName`) VALUES ('{$_POST['username']}', '{$_POST['pass']}', '{$_POST['pass2']}', '{$_POST['e-mail']}', '{$_POST['FullName']}')");
mysql_query($insert) or die( 'Query string: ' . $insert . '<br />Produced an error: ' . mysql_error() . '<br />' );
 $error="Thank you, you have been registered.";
 setcookie('Errors', $error, time()+20);

// Get user ID
mysql_real_escape_string($checkID = "SELECT * FROM Login WHERE `mail` = '{$_POST['e-mail']}'");
while ($checkIDdata = mysql_fetch_assoc($checkID)) { 
	$userID = $checkIDdata;


// now we create table 'Transactions" for the user
mysql_real_escape_string($create = "CREATE TABLE  `financewatsonn`.`Transactions_{$userID}` (
`ID` INT( 20 ) NOT NULL AUTO_INCREMENT COMMENT  'Transaction ID',
`name` VARCHAR( 50 ) NOT NULL COMMENT  'Name/Location',
`amount` VARCHAR( 50 ) NOT NULL COMMENT  'Amount',
`date` VARCHAR( 50 ) NOT NULL COMMENT  'Date',
`category` VARCHAR( 50 ) DEFAULT NULL COMMENT  'Category',
`delete` INT( 1 ) NOT NULL DEFAULT  '0',
UNIQUE KEY  `ID` (  `ID` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT =  'User ID {$userID}'");
mysql_query($create) or die( 'Query string: ' . $create . '<br />Produced an error: ' . mysql_error() . '<br />' );
}

I know in  'Get user ID' that I need to get the ID but I'm not sure how to get that information.

 

i get the error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

at 166 which is the while line under Get user ID

[WatsonN]

That code is actually

// now we insert user into 'Login' table
mysql_real_escape_string($insert = "INSERT INTO `Login` (`UID`, `pass`, `HR`, `mail`, `FullName`) VALUES ('{$_POST['username']}', '{$_POST['pass']}', '{$_POST['pass2']}', '{$_POST['e-mail']}', '{$_POST['FullName']}')");
mysql_query($insert) or die( 'Query string: ' . $insert . '<br />Produced an error: ' . mysql_error() . '<br />' );
 $error="Thank you, you have been registered.";
 setcookie('Errors', $error, time()+20);

// Get user ID
mysql_real_escape_string($checkID = "SELECT * FROM Login WHERE `mail` = '{$_POST['e-mail']}'");
while ($checkIDdata = mysql_fetch_assoc($checkID)) { 
	$userID = $checkIDdata;
}
// now we create table 'Transactions" for the user
mysql_real_escape_string($create = "CREATE TABLE  `financewatsonn`.`Transactions_{$userID}` (
`ID` INT( 20 ) NOT NULL AUTO_INCREMENT COMMENT  'Transaction ID',
`name` VARCHAR( 50 ) NOT NULL COMMENT  'Name/Location',
`amount` VARCHAR( 50 ) NOT NULL COMMENT  'Amount',
`date` VARCHAR( 50 ) NOT NULL COMMENT  'Date',
`category` VARCHAR( 50 ) DEFAULT NULL COMMENT  'Category',
`delete` INT( 1 ) NOT NULL DEFAULT  '0',
UNIQUE KEY  `ID` (  `ID` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT =  'User ID {$userID}'");
mysql_query($create) or die( 'Query string: ' . $create . '<br />Produced an error: ' . mysql_error() . '<br />' );

[PFMaBiSmAd]

Here are some things not to do -

 

1) You don't use mysql_real_escape_string() on the whole query string as that would break the sql syntax of the query. You use mysql_real_escape_string() on each individual piece of string data that is put into the query.

 

2) mysql_real_escape_string() returns the escaped string, so the way you are using it doesn't do anything because you are not assigning the result that it returns to anything.

 

3) You don't create a separate table for each user as that creates a database management nightmare. You must query your user table to get the user id before you can even find the user's data. What you do need to do is use one table to hold all the Transactions with a column for the user id.

 

4) If you do have a legitimate reason to get the id from the INSERT query, you don't need to execuite a SELECT query to do it. You can use mysql_insert_id()

 

[WatsonN]

1&2) Thank you. I did not know i would break the syntax. And for bringing to my attention the fatal error I made with not assigning it.

 

3)I promise, it was a good idea in my head. I had it set up to be all one table then I got that bright idea. . .

 

Thank ya much.