CODEWITHSUNDEEP
phpmysqlpdo
Joseph Gregory
Joseph Gregory

Reputation: 589

Updating multiple rows from PDO fetch query

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

Upvotes: 0

Views: 574

Answers (1)

Scuzzy
Scuzzy

Reputation: 12332

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.

Upvotes: 1

Related Questions