how to update sql table with a variable number of fields in the form php

Question

I have a variable number of fields in a form. The number of text fields are defined by the user with a function in jquery, but the final code of the form (example) is this:

<form id='form_educ' name='form_educ' method='post' action='form/educ.php'>   
  <div id='educ'>
    <input type='text' name='date1' id='date1'/>
    <input type='text' name='date2' id='date2'/>
    <input type='text' name='date3' id='date3'/>
    <input type='text' name='date4' id='date4'/>
    ....   
  </div> 
  <input type='submit' name='form_educ' value='Refresh'/>
</form>

These text fields when added by the user is create a sql INSERT TO (in another file):

$date = clean($_GET['date']);

"INSERT INTO educ (index_of_form, date, email) VALUES('$index', '', '" .mysql_real_escape_string($_SESSION['SESS_EMAIL']). "')";

$date is date1, or date2, or date3 or date4 (example).

Now in the file educ.php I want to update all text fields in the mysql database.

Usually it is a

$example = clean($ _POST ['example']);

I can do an update in the table and is resolved.

But in my case how can I get all the values ​​of the text field on the form and set the $_POST var if the number of fields is variable (could be date1, date2, date3, date4)?

Answer 1

I can think of no reason why form field name should be a unknown variable. Unless you're dealing with repeatable fields, in which case you would use an array like dates[], and you'd know what to expect in the process script.

For additional info see for example: http://www.web-design-talk.co.uk/58/adding-unlimited-form-fields-with-jquery-mysql/

Word of warning for future. When you make the field repeatable, allow users also to delete the fields they might have accidentally insertet. Watch out in the process script missing array keys (numerical index from 0–10 might be missing some values if the user deleted some form fields before submitting). You can reset the array keys with the array_merge function. Missing keys is an issue if you have two arrays you are trying to add into database as syncronized.

---

Updated to answer the comment.

Sorry, I don't undestand your question. You don't necessarily have to use hidden field. What you need is a database structure to match your forms function: to support one to many relationship. After all you are inserting multiple dates that relate to one person, or some specific event type, or what ever. Lets assume one user wants to add his three favorite dates in the world. Your form's source code looks like:

<input type="text" name='dateLover' id='dateLover'/>

<input type="text" name="dates[]" id="date1" /> //you need a increasing variable for these id numbers (or dont't put the id at all)
<input type="text" name="dates[]" id="date2" />
<input type="text" name="dates[]" id="date3" />

In addition you could have more fields such as <input type="text" name="extra" />. In submitted $_POST array there would be variables and arrays like: $_POST['dateLover'], $_POST['date'][0], $_POST['date'][1], $_POST['date'][2], $_POST['extra']. You'd take the non-repeatable values straight out of the $_POST array but you need a foreach (or some else loop) to handle the dates array.

Your database has to contain two tables (structure simplified):

1. person: id, dateLover
2. date: id, dateLover FK to person.dateLover, date

In your process script you have to:

1. insert a new dateLover to person and use last_insert_id to get his id
2. use a foreach to insert new dates to table date (with a dateLover's id as FK)

This all is pretty well demonstrated in the link I supplied earlier. For now, it's hard to give an complete example without undestanding the actual problem.

---

Update 2.

You are serializing the form, not the div's. So your (dynamically generated) could look like this:

<form id="form_educ" name="form_educ" method="post" action="form/educ.php">
<div id="educ">   
    <div><!--This is for layout only-->
        <input type="text" name="dates[]" id="date0" />
        <input type="text" name="names[]" id="name0" />
    </div>
    <div>
        <input type="text" name="dates[]" id="date1" />
        <input type="text" name="names[]" id="name1" />
    </div>
    <div>
        <input type="text" name="dates[]" id="date2" />
        <input type="text" name="names[]" id="name2" />
    </div>
  </div> 
  <input type="submit" name="form_educ" value="Refresh" />
</form>​

And in your process file you take these arrays from $_POST array and insert them into database maybe like this (with properly escaped and checked values of course...):

//dynamic part of the query
$qEnd = '';
$i = -1;

//this is static part of the query
$qBeginning = "INSERT INTO `date` (`id`, `date`, `name`) VALUES ";

foreach ($_POST['dates'] as $key => $date){
  $i++;
  $qValues[$i] = "(null, '{$date}', '{$_POST[names][$i]}')"; //never do this, always check values...

  //value sets are concatenated one after another to the $qEnd
  $qEnd .= $qValues . ',';
}

//combine the query parts and remove extra "," from the end
$q = $qBeginning . rtrim($qEnd, ',');

//now the (single) query ($q) is ready to be executed, echo it just for the fun of it

id should be auto increment field, or this kind of stuff doesn't work on the fly.

Again, this all should be clear in the jQuery link example so please read it carefully.

Answer 2

You should know all of the possible columns that could be updated before hand. Just check to see if those are set in the $_POST variable, then if they are append the insert or update statement with those values.

DANGER: Just looping on the $_POST variable looking at all params may end up inserting not database related POST fields into your insert statement and breaking.

Also when using these methods, be aware of SQL Injection, and use parameterized queries and never directly insert POST variable names or values into the SQL Statment.