Page 1 of 1

MYSQL table data agreement problem

Posted: Sun Jan 04, 2009 6:20 pm
by cap2cap10
Happy New Year! :drunk: PHP Technorati.
So let start off the new year with another simple mysql problem. I am adding data into 2 tables of the same
database, but I am having problems with the userID. How do I get the userIDs to be equal to each other during
the initial insertion of data into two tables?
One of the userID in js_login is auto incremented, the other userID in
js_account is not. Here is the code:

Code: Select all

....
mysql_query("INSERT INTO js_login( username, password, FName, MName, LName, Email, survey) VALUES ( '$username', '$password',
 '$FName', '$MName', '$LName', '$Email', '$survey') ") or die(mysql_error());
 
$query = "SELECT userID FROM js_login";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
    $userID;
}
 
mysql_query("INSERT INTO js_account(userID, FName, MName, LName, Email,  Address, Suite, City, State, Zipcode,
Country, Hphone_1, Hphone_2, Hphone_3, Mphone_1, Mphone_2, Mphone_3, Wphone_1, Wphone_2, Wphone_3, Extension, acc_stat)
VALUES ('$userID', '$FName', '$MName', '$LName', '$Email', '$Address', '$Suite', '$City', '$State', '$Zipcode',
'$Country', '$Hphone_1', '$Hphone_2', '$Hphone_3', '$Mphone_1', '$Mphone_2', '$Mphone_3', '$Wphone_1',
'$Wphone_2', '$Wphone_3', '$Extension', '$acc_stat') ") or die(mysql_error());
....
Please enlighten this novice so that I may have a functional website this Year!!
Any takers? Thanks in advance!

Bator

Re: MYSQL table data agreement problem

Posted: Sun Jan 04, 2009 6:56 pm
by requinix
Use mysql_insert_id to find out what the user ID just created was. Then use that in your next query.

If you don't need the ID later you can just use MySQL's LAST_INSERT_ID() function in place of a number.

Code: Select all

INSERT INTO... (userID...) VALUES (LAST_INSERT_ID()...)

Re: MYSQL table data agreement problem

Posted: Sun Jan 04, 2009 7:26 pm
by cap2cap10
Thanks, can you please insert your code into mine so I can see how it should look? :banghead:

Thanks again,

Batoe

Re: MYSQL table data agreement problem

Posted: Sun Jan 04, 2009 8:24 pm
by requinix
cap2cap10 wrote:Thanks, can you please insert your code into mine so I can see how it should look? :banghead:

Thanks again,

Batoe
There's a bit of code where you're trying to get $userID. Instead of trying to figure it out yourself (incorrectly, at that) use the function I linked to to get the value.