Jquery -> php -> mysql and dynamic form fields

Question

I have a multi page form which uses multiple tables in mysql for the entered values. One of the pages has a form which needs to have the option of enabling the user to click a + to add an extra line if they want to include extra information. There can be any number of lines added.

When the user clicks the +, jquery and php inserts the current userid in a row in the database table, this is then updated when more information is entered.

The problem I've got is to how to insert any number of rows for that particular id - when the data is entered, a blur event sends the data to the php code for updating the database. However, the problem I have is that it will update ALL the rows with that particular user id and not the current one.

What would be the best way of taking the data entered on the new rows and updating the database - I assume that I will have to use the ID value rather than userid value as that will be unique, or is there another way of doing it?

Thanks for any suggestions you can give, this one is clouding my mind currently...

Answer 1

You are correct when you state that you should use the primary key of the table you are updating, rather than the foreign key (UserId in your case) which occurs many times on your table. This guarantees you will update the row you expect.

An alternative would be to narrow down the rows you update - for example by only updating the most recent / the one with the highest ID or using a combination of fields that make the record unique. However, using the primary key is the best way to solve your issue.

Answer 2

Yes, you need the ID value of the rows you're inserting, not just the userid. I presume what you'd want to do is pass back the mysql LAST_INSERT_ID() from your Ajax script that does the insert when you click +, and push that ID into the information that you use later for updating the row.

Answer 3

Well, I think that you need to use a pair of identifiers, your userid and the field id. For editing only filedid would be enought but when you need to list all the user fields another reference will be necessary.

Answer 4

yeah, your best bet is to have mysql return a unique id for every row created, then just make sure whenever you edit a row on your form, to send the id along with it. Now you can edit one row at a time.