Multiple insert into more than one mySQL table

[davss]

Hi!

I want to insert data into two tables in one mysql query. First one would store user account details in TBL_USERS then i want on successful insertion of the primary a new record to be inserted at the same time into another table containing addresses TBL_USERS_DATA with just the username and time stamp (they will be able to add more details later on). Is this the best solution? I would like to keep login accounts/details separated from addresses thereby speed up queries etc.

function addNewUser($username, $password, $email){
$time = time();
/* If admin sign up, give admin user level */
if(strcasecmp($username, ADMIN_NAME) == 0){
$ulevel = ADMIN_LEVEL;
}else{
$ulevel = USER_LEVEL;
}

$q = "INSERT INTO ".TBL_USERS." VALUES ('$username', '$password', '0', $ulevel, '$email', $time)";

return mysql_query($q, $this->connection)
}

This is what i want to be inserted simultaneously :

$x = "INSERT INTO ".TBL_USERS_DATA." (username, timestamp) VALUES ('$username', $time)";

I have found some info at Learning Journal - Inserting Data in Multiple Tables about using mysql_insert_id() function but not sure how to implement it into mine.

Thanks for all help.

[DaveSawers]

Originally Posted by davss

I want to insert data into two tables in one mysql query.

Tough. You should use two queries. There's no advantage to be gained by trying to do it in one.

Originally Posted by davss

I have found some info at Learning Journal - Inserting Data in Multiple Tables about using mysql_insert_id() function but not sure how to implement it into mine.

The mysql_insert_id function returns the value of the id automatically generated in the previous insert. To make it work, you need an AUTOINCREMENT field in the first table which you can then use to tie the fields together in the second table. So, if your first table was created with a field defined something like:

id integer autoincrement primary key

and if your second table has something like:

id integer autoincrement primary key
id_tbl_users integer

then you can set the id_tbl_users in the second query to be equal to the result returned from mysql_insert_id after completing the first insert query