How to Update Multiple MySQL Rows with Form Data Using PHP?

Updating Multiple MySQL Rows with Form Data

In web development, it's common to have a form where users can edit records from a database. A common scenario is updating multiple rows in the same table with modified data. This can be achieved using PHP and MySQL.

Form Structure and Data Retrieval

The initial form is responsible for presenting the data to be edited. In this example, the form retrieves all photos with a specific GALLERY_id from the database and displays them with fields to modify the photo title and tags.

Data Submission and Updates

When the form is submitted, the data is sent to a PHP script for processing. The script uses the foreach loop to iterate over the posted data and extract the values for photo id, title, and tags.

Updating Rows with Prepared Statements

The next step is to update the matching rows in the database. For this, a prepared statement is used. It involves:

1. Preparing a SQL query with placeholders for the values to be updated.
2. Binding the placeholder values with the actual data.
3. Executing the query for each row in the loop.

Looping Over Form Arrays

To allow for multiple rows to be updated, the HTML form fields are submitted as arrays. This is achieved by using the [] notation in field names:

echo "<input type='text' name='photo_title[]' value='$title' />";

In the PHP script, these arrays are accessed using the foreach loop, allowing the script to iterate over the data and update each row accordingly.

Example Code

The updated PHP script would look like this:

<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 following these steps, you can effectively post form data and update multiple rows in a MySQL table.

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