[RESOLVED] form submission with php + mysql

[sgray]

Hey Guys,

I have an html table which is generated with php using a mysql database. The database contains a list of operating systems. It is shown as a form and items may be ticked or unticked dependant on whether the user wants to receive information about those OS's. What is the best way of managing the form and mysql changes when someone ticks, or unticks items on the list?

I was wondering about just truncating the existing data from the table and recreating the table based on what was ticked. With large numbers of users, or operating systems I figured this approach wasn't the most efficient. A friend suggested using javascript as a way of detecting which fields had changed, so I could write a query just to update those that had been modified? Each operating system has it's own record in a many-to-many table so the query needs to update a bunch of these records, not just one.

I'm unsure as to how to write the mysql query based on this form once it's been submitted. Each checkbox has it's own ID (which represents the OS in the database). If 30 records are changed would I best write a for loop of some sort to loop through each of the id's that have changed updating the sql database as it goes? even with a for loop I wouldnt know which records had changed.

I apologise for the length and number of questions, I've done a fair bit in php and mysql before but not anything this specific. Any help appreciated.

Cheers

[mykg4orce]

hello;

this can be tricky depending on the complexity of your options. If you will always have the same check boxes then there is no point of recreating the sql table. Simply pass the values of the checkboxes that the user has ticked off in a serialized array to mysql. Basically it will take your array of responses and convert it into a code representation of the virtual array. You can then unserialize the array when you want to show what the user had ticked off.

for example you had a check box labeled "winXP" and the user check it off:
you should store the response like this in the array "winXP_1" you can then explode each element of the array and you'll know that the first part before the underscore is the name of the check box and after the check box is the state.

I had to do this recently for a survey script, where instead of check boxes and I had to save the responses for each survey and load them back in if the user wanted to resume the survey at a later point.

[sgray]

thanks heaps for your reply, after 50+ views I'd wondered how likely I was to get anything back. I branched off your idea about using winxp_1 by having two fields for each operating system on the list. Each OS was represented as a number. Say winxp was 5, I had a checkbox on the form named "5" and another hidden field called 5_old which showed whether it was ticked or not when the form was generated. I posted the form to a script which then queried the database for all the possible OS values, ie 1-20 and looped through checking with 5 or 5_old were the same, if they were different then check to see what it had been changed to...and update the DB. I'm pretty glad I didnt need to resort to javascript as I was keen to keep it pure mysql + php.

Cheers