views:

176

answers:

5

What is the best way to carry an auto incremented ID from one form to another?

I have 3 tables (users, residences, and users_residences). I want a user to register, be added to the users table, be redirected to a form to create a residence which will add a row to the residence table and simultaneously add the the residence tabe row's id and the users table row's id to the users_residences table.

I have been playing around with uri segments but can't think of a way to store (or call) the just added user's ID for use in the second form.

I could probably use sessions to store an session id in the users table, then find the row ID from that, but it seems pretty hacky.

I am quite new to programming so feel free to tell me that I fail, but at least point me in the direction of where I can learn some of these concepts.

Thanks
Al

A: 

In the second form you could include:

<input type='hidden' name='user_id' value='whatever' />
Jonathan Fingland
Yeah, I saw that in the blog tutorial, but my issue is more getting the correct value (the just added user's id) into that hidden input.
Allansideas
A: 

I'd say you are better off with the session key in the users table; it's more secure (assuming you are doing this over https).

Storing a value in a hidden field is easy to manipulate from a security perspective. If your fields use auto increment, It's also easy to guess what the next one will be. A user could just pretend to be the next user registering and take that user's info. You are also giving info about what other user's ids are, how many users are registered for your site, etc.

Also, unless you are planning on doing something ajaxy, I'm not sure there is much of a choice; the user would click submit, get redirected to another webpage while the save is happening, the only information that could be transmitted would be url you redirect them to or session. You could pass it in the url, but it would also have the above problems.

Todd Gardner
Thanks! I am pretty new to all this, but come to think of it, the session key will probably be useful in a whole bunch of other places, so its not so hacky after all.
Allansideas
A: 

In MySQL you can get the id of the last inserted row with this statement:

select LAST_INSERT_ID();

Then you can pass that value around in your forms or session for your future inserts.

Hardwareguy
Ah yes, I was wondering about that. The issue would be that if the app started to grow really quickly would there be a chance of someone else submitting a form in the time that the query took to complete?
Allansideas
Just stack the last_insert_id() query with the insert so they execute at the same like this:Insert into users(name) values('taco'); select last_insert_id();
Hardwareguy
Thanks, in 30 mins on this site I have learned more than 2 hrs of googling,
Allansideas
@Hardwareguy - That is actually not necessary. LAST_INSERT_ID is on a per-connection basis, so as long as no other queries are executed by that connect you are safe: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
Todd Gardner
A: 

You can call

 $id = mysql_insert_id();

immediately after running the insert statement to get the last id generated by the database for the autoincrement field. This returns 0 if the statement did not generate an id. This page explains in more detail.

You can then use that id on the next form

 <input type="hidden" value="<?= $id ?>"/>
Vincent Ramdhanie
Neat, thanks! If I run the query immediately after the insert then there is no chance that another record will have been inserted in the time it takes to execute (if two people click submit simultaneously) or does this never happen?
Allansideas
You will need to test this assumption. It is supposed to be safe but you could get unlucky. The page that is linked in the answer has some discussion about thread safety.
Vincent Ramdhanie
A: 

Hi,

Well I think that first you should use Database transactions during the process. Second thing to do is to get the last inserted id inside the transaction with:

like:

$this->db->trans_start();
..(QUERIES)...
$this->db->insert_id();
..(QUERIES)...
$this->db->trans_complete();

Regards,
@pcamacho

Pedro