php insert different records into multiple mysql tables with same unique id

[edon12]

Hi everyone!

I have what is probably a simple problem with php. I'm relatively new to using php.

I am creating the registration page for my website and I want multiple tables to be filled into mysql using different data. However, I still need the unique id number to be filled into the tables and to be the same number in every table for a single user.

My id fields are int(11) unsigned auto_increment - this field is the primary key in the tables I am updating. I am able to generate the unique id number for my first table by simply using NULL in php so mysql will assign the next highest unique id number. This works for the INSERT for the first table but then how to I INSERT thta same unique id number in another table using a separate INSERT command right after the first one?

If there are the same number of records in each table, this is no problem. But, if one table gets out of order and has more records than the other, the unique id numbers do not align correctly between the separate tables.

Thanks in advance for any help, I'm really stuck on this.

[thindenim]

Hi edon,

You can use the mysql_insert_id function (PHP mysql_insert_id() Function) to grab the id of the last inserted record. Note that below I am using a database class.

Your php would be something like: -

$db->query("INSERT INTO tbl_1 (column1, column2) VALUES ('abc','xyz');"); // 1st table insertion
$id = mysql_insert_id; // get the id of the last inserted record
$db->query("INSERT INTO tbl_2 (id, column3) VALUES (".$id.", 'def');"); // 2nd table insertion

I would however recommend having a different id field relating to the primary table for each of the secondary tables, so you would have your unique auto incremented id, and also tbl1_id - something like that. So the third line would become: -

$db->query("INSERT INTO tbl_2 (tbl1_id, column3) VALUES (".$id.", 'def');"); // 2nd table insertion

Hope this makes sense!

David

[DaveSawers]

Makes sense to me.

Basically, if you use auto_increment on several tables, you cannot make the assumption that they will stay in step. One solution would be to take the auto_increment off all but the primary table and use mysql_insert_id to get the id from the primary table and then put that id into the subsequent tables as described above.

If there's a chance that id's in subsequent tables won't be unique, then just remove the primary_key flag from that field and use the table without a key.

[thindenim]

yep, that's exactly it dave. Either take the primary key and auto increment off, or use another field. Personally I would use another field.

[wige]

Well, the matter isn't only the autoincrement, you also need to know the id assigned to the main record so you can populate the remaining tables. So, if you have a customer table, and a customer address table, the customer address will have a primary key, but it still needs to know the id of the corresponding record in the customer table.

When you create the record in the first table, you can get the primary key assigned to the record with the following PHP line:

PHP Code:

$pkey = mysql_insert_id(); 


[Clay Martin]

What has been said here is dead on, I would add one other thought. On the secondary tables (that have a field populated with the key (call this the prime id) generated for the first table that you used mysql_insert_id() to get the auto incremented key) let those tables have their own unique key, it is useful for doing updates later. Also change the field that contains the generated id(prime key) to be a index for the other tables. This will speed access when you take the prime key from the first table and go to collect the related info from the other tables.

HTH
Clay

[Les N00bian]

I'm so glad I came across this post, it was exactly my question too. I have a follow up question that is related to this problem. I too have a registration form, and after that is written to the database, I want to direct the user to 2 or 3 subsequent pages to enter demographic and other data. How do I redirect the user to the second page when the form is submitted, and will the id I grabbed from mysqli_insert_id carry over to subsequent pages, or will this need to be saved as a session variable?

Thanks you guys rock.

[DaveSawers]

You can use the action element of the form to direct them to the next page, fill in the next form and then move to the third page the same way.

You can either put the result of the mysql_insert_id into the link ...\page2.php?id=... or use session variables.

[Les N00bian]

Thanks for the reply. I'm trying to post a sample code so I can see where I'm going wrong but the forum's giving me problems saying I'm not allowed to post links or emails until after 10 posts. If anyone wouldn't mind looking I can email or PM the sample. Really appreciate all the help, already getting much further than I was previously.

[williamc]

Originally Posted by DaveSawers

You can use the action element of the form to direct them to the next page, fill in the next form and then move to the third page the same way.

You can either put the result of the mysql_insert_id into the link ...\page2.php?id=... or use session variables.

As an addendum to Daves points, most people use a form field for this ie:

<input type="hidden" name="id' value="<?php echo $_REQUEST[id]; ?>">

[Les N00bian]

Okay, sorry for the dumb question but here goes. I'm able to grab that ID now using the mysqli_insert_id($dbc), however if I use the action in the form to go to the next page, the data doesn't get written to the table. It only gets written when I have the form action direct back to itself.

[DaveSawers]

So you need to put the code that writes to the database at the top of page 2, not in page 1. You can then write page 2 to pass on the id to page 3 using a hidden form field or whatever.

[Les N00bian]

First of all, this has been so much help. My application is in a basic functioning state, so thank you so much. Where I've run into a problem, is validating data on page 1/form 1, when form 1 is not actually posted until page 2 (per your instructions above). For example, I am trying to make sure that a username is not already in use before allowing a new users info to post to the database, but I can't validate that on form 1, without posting back to itself, and I can't validate it on page 2 because by then the form action has already loaded the next form.

Suggestions?

[nowreturn]

mysql_insert_id();