How to Update Multiple Rows in a MySQL Database Using a Form?

Post Form and Update Multiple Rows with MySQL

Issue:

You have created a form that retrieves all photos with a specific gallery ID and allows users to update their titles and tags. However, the current code is not updating any rows in the database.

Form Structure:

The form generates an HTML list of photos, with input fields for updating their titles, tags, and hidden fields for identifying their IDs.

Update Query:

The update query iterates over each submitted form and executes a separate UPDATE statement for each photo. However, as coded, the query is not able to access the form data correctly.

Solution:

To successfully update multiple rows, you need to submit the form data as arrays and loop through them in the update query.

Form Update:

Modify the input fields to submit values as arrays:

echo "<input type='text' name='photo_title[]' value='$title' /><br />";
echo "<input type='text' name='photo_tags[]' value='$tags' />";
echo "<input type='hidden' name='photo_id[]' value='$id' />";

Update Query Rewrite:

Iterate through the submitted form data arrays:

foreach ($_POST['photo_id'] as $key => $photo_id) {
    $id = $photo_id;
    $title = $_POST['photo_title'][$key];
    $tags = $_POST['photo_tags'][$key];

    $sql = "UPDATE photos SET title=?, tags=? WHERE id=?";
    $query = $db->prepare($sql);
    $query->execute(array($title, $tags, $id));
}

This updated code will correctly loop through the submitted form data, extract the updated values, and execute the UPDATE statement for each photo, successfully updating multiple rows in your MySQL database.

The above is the detailed content of How to Update Multiple Rows in a MySQL Database Using a Form?. For more information, please follow other related articles on the PHP Chinese website!