Short and sweet of it:
I have a table called persons that stores instances of personal information used for different purposes. for example, when a user enter into the details of a new venue, they also enter in the details of a contact person. the venue details are stored in one table, the personal details in persons table, with the persons id added into the venues tables to the link to the two.
my questions is this: is this bad practice? basically i see that (as per the below example code), I am executing the insert query into the persons table, finding the maximum id (making the assumption that this is the id with the information that the user just added) and then executing the insert query into the venues table with the looked up maximum id included
I see this could be potentially hazardous if you had multiple people inserting venues, then potentially you could be returning and linking the wrong id from persons table.
what is the best way to do this whilst still maintaining the personal detail in a seperate table.
$query = "insert into persons (perFirstName, perLastName, perAddr_Line1, perAddr_Suburb, perAddr_Postcode, perPhone_Landline, perPhone_Mobile, perEmail) VALUES ('$firstname','$lastname','$address_line1','$address_suburb','$address_postcode','$phone_landline','$phone_mobile','$emailaddress')";
mysql_query($query) or die();
if (!mysql_error()){
print "yes";
$perID = mysql_result(mysql_query("select max(callid) from persons"),0,0);
$query = "insert into volunteers (perID, volRoleID, volPoliceCheckCompleted) VALUES ('$perID','$role','$policecheck')";
}else{
print "no";
}
I believe you're looking for LAST_INSERT_ID(), which should give you back the last inserted ID to the the persons table. See the documentation here.