Updating multiple rows from PDO fetch query

Question

I understand to call and update since rows using prepared statements but I'm having trouble understanding how to update multiple records.

I have a simple attendance register with a select box to define if they attended or not. I call the rows in a table format:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table>
    <tbody>
        <tr>
            <th scope="col">Name</th>
            <th scope="col">Attendance</th>
        </tr>
    <?php if($Event_list->rowCount() > 0) {
    foreach ($Event_list->fetchAll() as $Event_list_row) { ?>
        <tr>
            <td>
                <?php echo ucwords($Event_list_row['firstname'])." ".ucwords($Event_list_row['surname']); ?>
            </td>
            <td>
                <input type="hidden" name="id" value="<?php echo $Event_list_row['booking_id']; ?>">
                <select name="outcome">
                    <option value="0">Choose</option>
                    <option value="1">Yes</option>
                    <option value="2">No</option>
                </select>
            </td>
        </tr>
    <?php } } ?>
    </tbody>
</table>
<input type="submit" value="Update" name="update">
</form>

I thought I could just use foreach again to update each record, but clearly I'm doing something wrong:

if(isset($_POST["update"])) {

    $Update_ID = $_POST["id"];
    $Update_Outcome = $_POST["outcome"];

    foreach($_POST['id'] as $count => $id) {
        $Attendance_Update_SQL = "
        UPDATE event_booking SET
        `confirmed`= :outcome
        WHERE `id`= :id";
        $Attendance_Update = $dbconn->prepare($Attendance_Update_SQL);    
        $Attendance_Update->bindParam(':id', $Update_ID[$count], PDO::PARAM_STR);
        $Attendance_Update->bindParam(':outcome', $Update_Outcome[$count], PDO::PARAM_STR);
        if($Attendance_Update->execute()) { 
        // Add in success message?
        }
    }
}

I get the following error: Warning: Invalid argument supplied for foreach() relating to the foreach row

sidenote: you only need to prepare the statement once. So it would be adviseable to move it outside the loop — DarkBee, Oct 31 '17 at 22:09
Whilst inefficient for lots of records, I'm not sure I see anything that stands out as being "wrong" with your per item update approach. If you wanted to do a batch update you'll probably need to use an "_INSERT ... ON DUPLICATE KEY UPDATE._" syntax instead. — Scuzzy, Oct 31 '17 at 22:11
@DarkBee sorry I forgot to close the loop in the example. The update is sitting outside. — Joseph Gregory, Oct 31 '17 at 22:13
are you calling Attendance_Update->execute() inside the loop? — atoms, Oct 31 '17 at 22:13
@atoms yes, sorry I realise I copied it over incorrectly. I've now updated the question — Joseph Gregory, Oct 31 '17 at 22:16
@DarkBee It's a neat hack on a table with a primary key to trigger mass record updates, since adding per record logic to an update statement would be overly complex. I have no idea how many records need updating, but this above loop would be inefficient on lots of records. [check this question](https://stackoverflow.com/questions/3432/multiple-updates-in-mysql). — Scuzzy, Oct 31 '17 at 22:18
also @JosephGregory can you explain what goes wrong anyway? that's not clear to me. To me it looks like only your last record is being updated, that's what I would expect looking at your code. — Scuzzy, Oct 31 '17 at 22:21
@Scuzzy sorry for not explaining. I want to update all records. A normal attendance list is about 30 records so I want to update them all. I have also added the error I'm getting — Joseph Gregory, Oct 31 '17 at 22:29
Yep, so the problem is `$_POST['id']` isn't an array, its a string with the last record ID, see my answer and http://php.net/manual/en/faq.html.php#faq.html.arrays — Scuzzy, Oct 31 '17 at 22:30
Ok arrays are a real week point, so using your method below would solve that? — Joseph Gregory, Oct 31 '17 at 22:32
Arrays arn't the problem, the problem is your form isn't sending an array, see my quickfix update :) — Scuzzy, Oct 31 '17 at 22:36

Scuzzy · Accepted Answer · 2017-10-31T22:49:17.217

Quick Fix:

rename name="id" to name="id[]"

rename name="outcome" to name="outcome[]"

Note: this syntax and reliance on index is problematic when you add checkbox types to a form.

Ok, the biggest problem is your input names are going to conflict

eg <select name="outcome"> will only set the last records outcome from your form.

I would use var_dump($_POST) to understand your post data

Suggestion, rename your "name" attributes as name="record[id][property]"...

<select name="record[<?php echo $Event_list_row['booking_id']; ?>][outcome]">
  <option value="0">Choose</option>
  <option value="1">Yes</option>
  <option value="2">No</option>
</select>

You would then use

foreach( $_POST['record'] as $id => $data )

where $id and $data['outcome'] is now clearer to understand and use.

Updating multiple rows from PDO fetch query

1 Answers1