[SOLVED] Populating two tables using mysql_insert_id()

[codebyren]

Hi.

 

I have been researching how to insert values into two separate (but related) tables in one shot.

The first table has a primary key, of course, and I want the second table to share that key e.g:

 

The first table will contain details of a user posting and have columns something like this:

[posting_id] [user_id] [posting_title] [etc...]

 

(posting_id is the Primary Key there and is set to Auto Increment)

 

The second table will contain the actual body of the posting and so needs to share the posting_id key:

[posting_id] [posting_body]

 

I can insert data into the first table fine but then I want carry the posting_id from there to the next table. Keeping in mind that the two queries will definitely be executed consecutively within my code, will using mysql_insert_id() fetch the correct key reliably?

 

If two different users were to make a posting at the same time, is it not possible that the server might process the query populating the first table (by both users) and then move onto the query populating the next table? Wouldn't mysql_insert_id() then return the wrong value for at least one of my queries?

 

I hope that is clear enough, thanks for reading my problem.

[PHP_PhREEEk]

I assume you are familiar with the usage behind mysql_insert_id(), so to answer your actual question, yes, it will work fine. The reason is, even though there might be two users posting, each of those mysql Inserts will have unique resource ID's, so the call to mysql_insert_id() will be based on the last query for that resource ID. Won't be a problem at all.

 

PhREEEk

[codebyren]

Awesome.

 

I wasn't too sure php/mysql would be inherently capable of identifying that the two users are in fact different.  Well capability isn't really the question, I just wasn't sure it would implement in this situation.

 

What you referred to as  "unique resource ID's" is what I was hoping for - is that actually the official name for this sort of identity crisis thing?

 

Thanks very much for your help PhREEEk, you've helped me more than once now.

[PHP_PhREEEk]

What you referred to as  "unique resource ID's" is what I was hoping for - is that actually the official name for this sort of identity crisis thing?

 

Thanks very much for your help PhREEEk, you've helped me more than once now.

 

Dunno about 'official name'.. lol... but it is what it is...

 

When you say:

 

<?php
$result = mysql_query("some query");

 

$result is populated with a Resource ID. It's like if you go to buy concert tickets and you get a wristband, that's your ID... lol.. so $result is the 'wristband' for that query, and you can then use $result for doing anything further, such as requesting num_rows, or insert_id, whatever. It is unique to that query at that moment, so if another user simultaneously accesses the database, they will get their own 'wristband' for that same query.

 

Same query, different wristbands.

 

Your welcome for the help...

 

PhrEEEk

[codebyren]

Fantastic explanation. Not sure why i didn't work this out because i do use numRows() etc. on $result frequently and as you say, these will all work in a similar way.

 

Back to work now.

Thanks again.