Jump to content

Mysql insert into and create table


WatsonN

Recommended Posts

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
Link to comment
Share on other sites

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 />' );

Link to comment
Share on other sites

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()

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.