How to Update Multiple Rows in MySQL with Posted Form Data?

Updating Multiple Rows with Posted Form Data in MySQL

When working with forms that require updating multiple rows in a database, it's essential to handle the data submissions correctly. This article addresses a specific scenario where a form is used to collect information (title and tags) for multiple photos and update their records in a MySQL database.

The Form

The form gathers data for each photo linked to a specific gallery ID. For each photo, it includes inputs for title and tags, as well as a hidden input for the photo ID. This information is submitted when the form is processed.

The Update Query

The update query is intended to update each photo's title and tags based on the posted data. However, the issue arises when trying to bind parameters to the query.

Solution

The key to resolving this issue is to submit the input fields with array names, as multiple fields with the same name cannot be handled as individual fields. By submitting them as arrays, we can loop through the posted data and update each row using the corresponding ID.

Adjusted Code

The modified code snippet for the HTML input fields is as follows:

<code class="html">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">";</code>

The updated code for the update query loop:

<code class="php">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));
}</code>

By submitting the form data as arrays and iterating over the values, you can effectively update multiple rows in the database based on the posted information.

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