Carrying row values from MYSQL after form submission.

Question

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

Answer 1

In the second form you could include:

<input type='hidden' name='user_id' value='whatever' />

Answer 2

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.

Answer 3

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.

Answer 4

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 ?>"/>

Answer 5

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