Forum Moderators: coopster
I have written the query below to add data into two separate tables when the submit button is clicked from a form. The data goes into the database just fine, but with two different ids. Is there some way of adding to the queries so that the ids that go into the two different database tables are the same?
$query = "INSERT INTO
contacts (
contact_name,
main_category_id,
contact_description,
primary_contact_person,
pcp_title,
pcp_email,
secondary_contact_person,
scp_title,
scp_email,
primary_decision_maker,
state_id,
status_id,
last_action,
next_action
) VALUES (
'".mysql_escape_string($_REQUEST['contact_name'])."',
'".mysql_escape_string($_REQUEST['main_category_id'])."',
'".mysql_escape_string($_REQUEST['contact_description'])."',
'".mysql_escape_string($_REQUEST['primary_contact_person'])."',
'".mysql_escape_string($_REQUEST['pcp_title'])."',
'".mysql_escape_string($_REQUEST['pcp_email'])."',
'".mysql_escape_string($_REQUEST['secondary_contact_person'])."',
'".mysql_escape_string($_REQUEST['scp_title'])."',
'".mysql_escape_string($_REQUEST['scp_email'])."',
'".mysql_escape_string($_REQUEST['primary_decision_maker'])."',
'".mysql_escape_string($_REQUEST['state_id'])."',
'".mysql_escape_string($_REQUEST['status_id'])."',
'".mysql_escape_string($_REQUEST['last_action'])."',
'".mysql_escape_string($_REQUEST['next_action'])."'
)";
if(!mysql_query($query) ) {
print "Error with query: \"$query\"<br>".mysql_error();
// Simple error handling...
}
//END OF QUERY1
//BEGINNING OF QUERY2
$query = "INSERT INTO
contact_info (
web_address
) VALUES (
'".mysql_escape_string($_REQUEST['web_address'])."'
)";
[php.net...]
You should be able to retrieve the number of rows in the first table, then insert the value into the second table as the ID... Otherwise the only ways I can think of are:
1.) Select the max ID from table 1, then set the ID of table 1 and table 2 to the value returned + 1.
2.) Do the insert into table 1, then retrieve the max value from table 1 id, and use that for the value of the id in table 2.
Once you have the tables consistent, you should be able to use 'auto increment' on both as long as both tables always have a row added on an insert, but IMO it's best to double check somehow if you are going to base everything on the table 1 id.
In your first insert, use my_insert_id() [us.php.net] to get the last inserted id, store it in a variable, then apply that to the field on the second table insert.
Thanks rocknbil!
However, I'm sure your comments will come in handy the next time I try something a bit more complex.
I appreciate the response.
Bill
simplify my table structure to make it easy on myself
This is **always** better no matter how you look at it, not just to make it easy on yourself but creates faster and better applications. The KISS rule is as universal as Murphy's Law.
Just one more example, in your case, that further clarifies the idea:
... one table that holds most of the data. It is joined to several tables such as category, state, city, country by the various ids.
<kidding>Next month myself and some splinter groups are joining forces to start a revolution and divide our state. We're going to seize control of the southwest corner, we already have a charter that dispenses with the entire welfare and taxation system. (lol what a dreamer.) If you could, please add a place to your form where we can add an "other" state name.</kidding>
In this case, you would need to insert a new state in the states table, use my_insert_id() to get the last inserted id, and insert that into your main data table.
Another approach - and it's **really** debatable that this is less efficient, other than my_insert_id() is built in function so you should use it - is after your insert,
select id from states order by id desc limit 1;
Although I haven't researched it, this is probably what my_insert_id() does anyway.
