Update MySQL Database With Data From Multiple Input Elements Inside A While Loop, And A Single Submit Button Outside of The While Loop

Question

I have a page that fetches images from a database after an initial upload, that allows the user to add titles and tags to the images.

I have set the while loop to output the individual images inside one form. Each image has two related input fields. What I would like to do is have it so when the form is submitted all of the information is updated in the database in one go (even if image details from the relevant input fields remain blank/are not filled in).

To achieve this I thought I could have the form’s submit button outside of the while loop. This isn’t working as foreseen though. It basically updates one image at a time, each time I press submit (it updates the last image in the loop).

How do I get it so it processes all of the information for all of the images inside the while loop in one go, on submit? If I place the submit button inside the loop I get a button for every image, which I don't want.

Note: the <img/> source path I've just hardcoded below to save adding the different variables that achieve this, thus hopefully keeping the code simpler.

Fetch data from database and output HMTL form

<?php isset($_REQUEST['username']) ? $username = $_REQUEST['username'] : header("Location: login.php"); ?>

<form method="post" enctype="multipart/form-data">

    <?php

        if (isset($_SESSION['logged_in'])) {
            $user_id = $_SESSION['logged_in'];
        }

        $stmt = $connection->prepare("SELECT * FROM lj_imageposts WHERE user_id = :user_id");

        $stmt->execute([
            ':user_id' => $user_id
        ]); 

        while ($row = $stmt->fetch()) {

            $db_image_filename = htmlspecialchars($row['filename']);

    ?>

    <div class="upload-details-component">                
        <div class="form-row">
            <img src="/project/images/image.jpg">
        </div>
        <div class="edit-zone">
            <div class="form-row">
                <label for="upload-details-title">Image Title</label>
                <input id="upload-details-title" type="text" name="image-title">
            </div>
            <div class="form-row">
                <label for="upload-details-tags">Comma Separated Image Tags</label>
                <textarea id="upload-details-tags" type="text" name="image-tags"></textarea>
            </div>
            <div class="form-row">
                <input type="hidden" name="username" value="<?php echo $username; ?>">
                <input type="hidden" name="image-filename" value="<?php echo $db_image_filename; ?>">
            </div>
        </div>
    </div>

    <?php } ?>

    <button type="submit" name="upload-submit">COMPLETE UPLOAD</button>
</form>

Update Database On Form submission

<?php 

if(isset($_POST['upload-submit'])) {

    $image_title = $_POST['image-title'];
    $image_tags = $_POST['image-tags'];
    $form_filename = $_POST['image-filename']; // value attribute from hidden form element

        try {

            $sql = "UPDATE lj_imageposts SET
            image_title = :image_title,
            image_tags = :image_tags

            WHERE filename = :filename";
                
            $stmt = $connection->prepare($sql);
    
            $stmt->execute([
                ':image_title' => $image_title,
                ':image_tags' => $image_tags,
                ':filename' => $form_filename
            ]);
            
            // This is the URL to this actual page (basically refreshes the page)
            header("Location: upload-details.php?username={$username}");

        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }

        // give values an empty string to avoid an error being thrown before form submission if empty
        $image_title = $image_tags = "";

}

?>

funfact, following Location: directs is optional, and its trivial for hackers to not follow Location redirects. — hanshenrik, Dec 26 '21 at 13:52
Hi @Don'tPanic - yes it did. I'll almost certainly mark it as correct and allocate the bounty to you when it expires. Very much appreciated. — pjk_ok, Dec 31 '21 at 03:12

Don't Panic · Accepted Answer · 2022-01-01T23:12:22.297

The problem with your current approach is that bcs each set of inputs uses the same name, they overwrite each other. Say you have 3 records, 3 sets of form inputs on your front end. If you enter for image-title values of one, two, and three, on the back-end you'll only ever see three.

You can verify this yourself: print_r($_POST);.

The solution is to send the data as arrays, and you can do this by simply using [] in your input names:

<?php while ($row = $stmt->fetch()) { ?>
    <input type="text" name="image-title[]">
    <textarea type="text" name="image-tags[]"></textarea>
    <input type="hidden" name="image-filename[]" value="<?php echo $db_image_filename; ?>">
<?php } ?>
<button type="submit" name="upload-submit">COMPLETE UPLOAD</button>

Now you'll get arrays of input on the back end, like (again, use print_r($_POST); to see for yourself):

[image-title] => Array
    (
        [0] => First Title
        [1] => 2nd Title
    )

[image-tags] => Array
    (
        [0] => foo
        [1] => bar
    )

[image-filename] => Array
    (
        [0] => filename
        [1] => another-filename
    )

And so you can loop through them, processing them one at a time:

$sql = "UPDATE lj_imageposts SET
    image_title = :image_title,
    image_tags = :image_tags
    WHERE filename = :filename";
$stmt = $connection->prepare($sql);

foreach ($_POST['image-title'] as $key => $value) {
    $stmt->execute([
        ':image_title' => $_POST['image-title'][$key],
        ':image_tags'  => $_POST['image-tags'][$key],
        ':filename'    => $_POST['image-filename'][$key]
    ]);
}

Note that using the filename as the unique identifier is not ideal - that's what IDs are for. There is potentially the risk of non-unique filenames, but also your DB queries will be much more efficient for queries like (assuming your table has a primary key id) UPDATE ... WHERE id=:id. You can do that by replacing your hidden file input with a hidden ID input:

<input type="hidden" name="id[]" value="<?php echo $row->id; ?>">

And then of course on the back end update your query:

WHERE id = :id
// ...
':id'    => $_POST['id'][$key]

Even better, you can use the id in the input name, to explicitly identify the set of values:

<input type="text" name="image-title[<?php echo $row->id; ?>]">
<textarea type="text" name="image-tags[<?php echo $row->id; ?>]"></textarea>

In this case you don't even need to pass the id (or image-filename) as a separate, hidden input - you already have all the IDs in the input names. On the back end you could then do:

$sql = "UPDATE lj_imageposts SET
    image_title = :image_title,
    image_tags = :image_tags
    WHERE id = :id";
$stmt = $connection->prepare($sql);

foreach ($_POST['image-title'] as $id => $value) {
    $stmt->execute([
        ':image_title' => $_POST['image-title'][$id],
        ':image_tags'  => $_POST['image-tags'][$id],
        ':id'          => $id
    ]);
}

As you can imagine this is a common problem, and there are many other solutions here to reference:

PHP docs reference:

https://www.php.net/manual/en/faq.html.php#faq.html.arrays

Did you abbreviate `because`? Why? – mickmackusa Dec 27 '21 at 02:54 — mickmackusa, Dec 27 '21 at 02:54

score 0 · Answer 2 · answered Dec 23 '21 at 04:43

If I understand your question correctly, you want all of the multiple input entries to be saved to your db for each image.

To do this, you will first of all need to make each input name an array. For example, on your first input, "image-title" you would need to make it "image-title[]". You'll also need "image-tags" to be "image-tags[]".

Once you have that completed, you will need logic in the code it submits to, to loop through each of the inputs and update the database.

So now $image_title on the submit script will be an array of the values. You need to loop through those and update the database accordingly.

I can't work out how to loop through those and update the database though? — pjk_ok, Dec 27 '21 at 00:07

score 0 · Answer 3 · answered Dec 30 '21 at 08:06

First, in your question/code you are using filename field as unique identifier for your records in table lj_imageposts. I assume that you are sure values in this field are unique for each user you have. I strongly recommend you create a unique auto increment field id in your table (in case you don't have one) and use it. In my answer I will use id field to identify the images.

    while ($row = $stmt->fetch()) {

        $db_image_id = $row['id']; // the unique id for this image
        $db_image_filename = htmlspecialchars($row['filename']);
        ...
        ...

Second, when you create/build your form, use an array names for your inputs and make sure to have a unique input name for each image you have. to do this replace the name for this:

<input id="upload-details-title" type="text" name="image-title">

to this:

<input id="upload-details-title_<?=$db_image_id;?>" type="text" name="image-title[<?=$db_image_id;?>]">

important You need to do this for each input and textarea you have in the loop.

At this point, when you submit your form, your $_POST should look something like this

array(
    'image-title' => array(
        1 => 'First Image Title',
        2 => 'Second Image Title',
        3 => 'Third Image Title',
        ...
        ...
    ),
    'image-tags' => array(
        1 => 'First Image Tags',
        2 => 'Second Image Tags',
        3 => 'Third Image Tags',
        ...
        ...
    ),
    ...
    ...
)

where 1, 2, 3, ... are the IDs for your images

Third, in your update code, you need to loop for the images you have data for them, and update the images one by one:

if(isset($_POST['upload-submit'])) {

    foreach( $_POST['image-title'] as $image_id => $dummy) { // at this point I only need the ID


        $image_title = $_POST['image-title'][$image_id];
        $image_tags = $_POST['image-tags'][$image_id];

        // I don't need this value to identify the record. in this example I'm using `id` field
        //$form_filename = $_POST['image-filename'][$imageId]; // value attribute from hidden form element

        try {

            $sql = "UPDATE lj_imageposts SET
            image_title = :image_title,
            image_tags = :image_tags

            WHERE id = :id";
                
            $stmt = $connection->prepare($sql);
    
            $stmt->execute([
                ':image_title' => $image_title,
                ':image_tags' => $image_tags,
                ':id' => $image_id // using id instead of filename as identifier
            ]);

        } catch (PDOException $e) {
            echo "Error: " . $e->getMessage();
        }

    }

    // move the "redirect" header to outside my update loop
    // This is the URL to this actual page (basically refreshes the page)
    header("Location: upload-details.php?username={$username}");

    // also, exit to only send the redirect header not anything else
    exit;
}

Update MySQL Database With Data From Multiple Input Elements Inside A While Loop, And A Single Submit Button Outside of The While Loop

3 Answers3

Linked