Update Multiple Records same form fields

Hi All, I have spent hours scouring the web looking for a similar situation, unfortunately, cant find one exactly like mine.

I have a query which returns a table of students that are owned money for referring other students to a course.
I need the admin to be able to process payments, by ticking the box of what he is going to pay, and then typing a check no and check date for the entire transaction.
Meaning the check no and check date will be used to update the fields in the referrals table.

See attachment if I have confused you.
The records that dont get ticked obviously go unedited but the ones that do, could be 1 or could be 20, need to be updated.

Thanks in advance!

I can’t see your attachement because it hasn’t been approved yet, but I probably don’t need to.

The checkbox is redundant, you can just update records when the check date and number are non-empty.
To do this you need to get all those fields to be treated as an array.

The HTML your script will output to build the form should look a bit like this:

<thead>
<tr>
   <th>Student Name</th>
   <th>Amount Owed</th>
   <th>Check Number</th>
   <th>Check Date</th>
</tr>
</thead>
<tbody>
<tr>
   <th scope="row">John Jones</th>
   <td>$100</td>
   <td><input type='number' name='check_number[5]'></td>
   <td><input type='date' name='check_date[5]'></td>
</tr>
<tr>
   <th scope="row">Sally Shephard</th>
   <td>$70</td>
   <td><input type='number' name='check_number[8]'></td>
   <td><input type='date' name='check_date[8]'></td>
</tr>
<tr>
   <th scope="row">Dave Diamond</th>
   <td>$55</td>
   <td><input type='number' name='check_number[12]'></td>
   <td><input type='date' name='check_date[12]'></td>
</tr>

The square brackets after input names mean those $_POST values will be arrays you can loop over. The number inside would be the ID of the DB record.

So when processing:


//$_POST['check_number'] and $_POST['check_date'] are parallel arrays

foreach( $_POST['check_number'] as $id =&gt; $number ) {
  $id = (int)$id; 
  $number = trim( $number );
  $date = trim( $_POST['check_date'][$id] );

  //If not empty - update DB
  if( $number AND $date ) {

      $query = "UPDATE `student_payments` SET check_number='$number', check_date='$date' WHERE ID = $id";
      //execute query
  }
}

Obviously you’ll need better error checking on the check number and date fields (I’ve just tested that they are both not empty),
and escaping your SQL, but this gives you the idea.

Thanks for the reply.
Your code places a check date and number on each row. In my design, there are rows which contain the results of the query with a tick box on each line. Right under the results table, there is the check date and check number field. Only once.

So how can you pay multiple different people with the same check? Surely the check number is unique to a payment to a particular person?

The table we are dealing with returns rows of commissions for one particular student.
Example, table says student Jon Doe has made

$5.00 for student Jane signing up
$3.00 for student Jim
$1.50 for student Paul

at the end of each commission i need a checkbox Then at the bottom of the form one field for check number and check date.
Check will be to same payee.

here is link to picture

https://www.dropbox.com/gallery/5151743/1/Sitepoint?h=e5eff5#/

Okay, no problem. So remove the check_date and check_number I had on each row and just use a checkbox (I’ve called it paid for the code below), same square brackets with the row ID to identify.

Processing will look a bit like this:


if( isset($_POST['paid']) ) {
  $check_number = trim($_POST['check_number']); //validate this
  $check_date = trim($_POST['check_date']); //validate this

  $paid_ids = array_map('intval', $_POST['paid']);  //cast all record IDs to integer
  $paid_ids = implode(', ', $paid_ids);

  $query = "UPDATE `student_payments` SET `check_number`='$check_number', `check_date` = '$check_date' WHERE ID IN ($paid_ids)";

  echo $query;  //see what I made?
}

ok awesome. a few questions though.
As my table is generated from a MySQL database, how to do I code the checkbox names? Need it to auto incrament. Im guessing its something like $checkboxnum = $checkboxnum + 1??

<tr>
   <th scope="row">John Jones</th>
   <td>$100</td>
   <td><input type='number' name='<?php echo $checkboxnum + 1?>'></td>
   <td><input type='date' name='check_date[5]'></td>
</tr>

And how do we pass the referralID’s? like each row has a unique ID that its updating, how is that passed over to the processing code? do i need a hidden form field with the id on each row? Im referring to the value in the $sql code of WHERE id =

The database row will have an ID field. That is the value you must put inside the square brackets when generating the form.
There’s no need to create pseudo-ids in the form that don’t relate to anything.

So when building the form echo that ID in the field name.

When processing the form $_POST[‘paid’] (or whatever you call the checkbox) will be an associative array with the key matching the DB ID.

Then the code in my previous post will work, to update those records using the SQL IN clause.

Actually scrap that.
Your form generator must create HTML that looks like this. The value is the ID from the Database.


<input type='checkbox' name='paid[]' value='5'>
<input type='checkbox' name='paid[]' value='8'>
<input type='checkbox' name='paid[]' value='23'>

Then $_POST[‘paid’] will be a simple (not associative) array of the row IDs.

Then my code above will work.