Insert auto_increment to multiple tables

Anon

Hi
I have a php script with mysql, which users use to register, in the table the 1st column is a primary key which auto increments every time a new user registers, is it possible to get this to be entered into another table where it will be just normal data, (not a primary key).

apatel

As a new record ??

Anon

Yes, I'll explain in more detail if it helps. I did search the fourm and found stuff on inserting into multiple tables, but not if its an auto incrementing number.
Basically..
Users register, table 'users' gets updated, and the 1st column is 'user_id', (primary key, auto incrementing). What I want now, is for user_id to be copied from table 'users', to table 'dates' (which has its own primary key, auto increment called date_id).
Does this explain better?
Thanks for the quick reply
Ben

apatel

Hi again,

Thanks for that, i think i understand now.

Try using this.

$last_id = mysql_insert_id();

Then insert $last_id into your next table using the normal INSERT command in the place where you want it.

Note - mysql_id(); must follow the SQL execution statement thet you want the ID from otherwise you may get the last autoincrement ID from the incorect table.

Hope this Helps.

Regards, Anand

Anon

Excellent, many thanks for that, I'll give it a try and see how I get on.
Just out of interest is there any chance that if i us mysql_insert_id() and 2 people are registering at the same time, that it could pull the wrong last_id? Or does it work by a client/session?

apatel

Im not sure exactly how it works, but im guessing that because the insert_id() will follow the SQL execution then it "shouldn't" effect it. Only way to really know is if you try it.

Regards, Anand

Anon

I added this to my script

function updatetable2($last_id) {
$last_id = mysql_insert_id();
$sql="INSERT INTO table2 (user_id) ".
"VALUES ('$last_id')";
}

but it didn't seem to work. Can you see anything wrong with it?

Am I putting the $last_id = mysql_insert_id(); in the right place? Or should it be where the ($last_id) is at the top? Or mebe completly separetly

apatel

I just have seen that their are problems with using this command, What your gonna have to so is use the MySQL equivalent.

Your code will change to this..

function updatetable2()
{
$sql="INSERT INTO table2 (user_id) ".
"VALUES (LAST_INSERT_ID())";
}

This should work (i havn't tried it)

Anand

Anon

Hmm it didn't work, it should, I can't see anything wrong!
Argh, /me bangs head on keyboard!
I looked on MySQL.com and they have something close to that! I can't see why its not working. Maybe I won't put it in its own function. Just stick it right after the 1st table is update!

dmcinnis

mysql_insert_id() gets the value from the previous sql insert. Right after the insert, assign it to the variable and then pass it to the function.

kparker

Only way to really know is if you try it

In one sense that's true, but I would also advise consulting the manual, which does in fact promise that mysql_insert_id() works on a per-connection basis. (It wouldn't be all that useful if it didn't.)

Anon

Do you know what function would up date the 2nd table? I have tried everything I can think of! Inserted it in its own function, added it to another function (which worked, but cocked up the function it was added to).
So far I have

function updatetable2() {
$sql="INSERT INTO table2 (user_id) ".
"VALUES (LAST_INSERT_ID())";
}

The above doesn't work, is there anything I need to add?

I even tried adding..

$result=db_query($sql);
if (!$result) {
$feedback .= ' ERROR - '.db_error();
return false;
}

Because this is whats after another function that updates a table (which works).

Soryy about this, I only statred learning PHP last week.

dmcinnis

what database are you using? MySQL?

Anon

Yes, I have a register script, which users register with, and the new record gets a user_id, which i then want inserted into a 2nd table. Only it doesn't want to work. Its very much the same thing Griffith wants to do in his post! Hopefully someone can help us both.

dmcinnis

Okay, here is what I use and it works like a charm.

Maybe there is a better way, but this gets the job done.

$sql = "INSERT INTO pr_tbl1 (acctno, headline, summary) VALUES ($acctno, '$headline', '$summary')";

$result = @($sql, $connection);

$prid = mysql_insert_id();

$sql = "INSERT INTO pr_tbl2(prid, body) VALUES ($prid, '$body')";

$result = @($sql, $connection);

Anon

Excellent, thanks for that, works perfectly.

apenticoff

Thank you thank you thank you! You've saved me SO much time. This works perfectly