php foreach loop editing wrong record in DB

Question

Good day,

I've ran into a bit of a problem with my website.

I have a form which pulls a full table from my DB. Beside each record I have an 'Edit' and 'Remove' button (see image 1).

image 1

If I click on the edit button beside record 'Delete me', a modal appears and populates the value of each editable field with the current data available, and I can then edit each value and update the database. This works fine.

The problem I have; when I select one of the records where the 'Name' field is duplicated with a different 'Filling/Size' (for example, if I select 'Filling/Size' 5) it always updates and pre-populates the modal with the data from the first record in the table that shares the same 'Name'. Image 2 shows me clicking the 'Edit' button for record 1-5.

image 2

I've tried creating a foreach loop inside my foreach loop but that just duplicated every record many many times. My code is below. Any input or suggestions would be greatly appreciated.

<div class="card mb-3">
        <div class="card-header">
            <i class="fa fa-table"></i>Items</div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered" id="dataTable" name="dataTable" width="100%" cellspacing="0">
                    <thead>
                    <tr>
                        <th>Name</th>
                        <th>Filling/Size</th>
                        <th>Category</th>
                        <th>Description</th>
                        <th>Price</th>
                        <th>Edit/Remove</th>
                    </tr>
                    </thead>
                    <tfoot>
                    <tr>
                        <th>Name</th>
                        <th>Filling/Size</th>
                        <th>Category</th>
                        <th>Description</th>
                        <th>Price</th>
                        <th>Edit/Remove</th>
                    </tr>
                    </tfoot>
                    <tbody>
                    <?php foreach ($result as $i => $row) { ?>
                            <tr>
                                <td><?php echo $row->name; ?></td>
                                <td><?php echo $row->filling; ?></td>
                                <td><?php echo $row->category; ?></td>
                                <td><?php echo $row->description; ?></td>
                                <td><?php echo $row->price; ?></td>
                                <td id="editRemoveButtonsCell">
                                    <button id="editButton" type="button" class="btn btn-outline-success" data-toggle="modal" data-target="#editItemModal<?php echo $row->name;?>">Edit</button>
                                    <button id="removeButton" type="button" class="btn btn-outline-danger" data-toggle="modal" data-target="#removeItemModal<?php echo $row->name;?>">Remove</button></td>
                            </tr>

                            <!-- Delete Modal -->
                            <div class="modal" id="removeItemModal<?php echo $row->name;?>">
                                <div class="modal-dialog">
                                    <form>
                                        <div class="modal-content">

                                            <!-- Modal Header -->
                                            <div class="modal-header">
                                                <h4 class="modal-title">Delete Item <?php echo $row->name?></h4>
                                                <button type="button" class="close" data-dismiss="modal">&times;</button>
                                            </div>

                                            <!-- Modal body -->
                                            <div class="modal-body">
                                                Are you sure you want to delete item <?php echo $row->name?> <?php echo $row->filling;?>? <b>This cannot be undone!</b>
                                            </div>

                                            <!-- Modal footer -->
                                            <div class="modal-footer">
                                                <input type="hidden" value="<?php echo $row->name;?>" name="deleteItemName" id="deleteItemName">
                                                <input type="hidden" value="<?php echo $row->filling;?>" name="deleteItemFilling" id="deleteItemFilling">
                                                <button type="button" class="btn btn-cancel" data-dismiss="modal">Close</button>
                                                <input type="submit" name="deleteItem" id="deleteItem" value="Delete" class="btn btn-danger" />
                                            </div>
                                        </div>
                                    </form>
                                </div>
                            </div>

                            <!-- Edit Modal -->
                            <div class="modal" id="editItemModal<?php echo $row->name;?>" name="editItemModal<?php echo $row->name;?>">
                                <div class="modal-dialog">
                                    <form>
                                        <div class="modal-content">

                                            <!-- Modal Header -->
                                            <div class="modal-header">
                                                <h4 class="modal-title">Edit Item <?php echo $row->name;?></h4>
                                                <button type="button" class="close" data-dismiss="modal">&times;</button>
                                            </div>

                                            <!-- Modal body -->
                                            <div class="modal-body">
                                                    <div class="form-group">
                                                        <label for="itemNameUpdate">Name</label>
                                                        <input type="text" class="form-control" id="itemNameUpdate" name="itemNameUpdate" placeholder="Enter Item Name" value="<?php echo $row->name;?>">
                                                        <input type="hidden" value="<?php echo $row->name;?>" name="itemNameOriginal" id="itemNameOriginal">
                                                        <input type="hidden" value="<?php echo $row->filling;?>" name="itemFillingOriginal" id="itemFillingOriginal">
                                                    </div>
                                                    <div class="form-group">
                                                        <label for="itemFillingUpdate">Filling/Size</label>
                                                        <input type="text" class="form-control" id="itemFillingUpdate" name="itemFillingUpdate" placeholder="Enter Filling/Size" value="<?php echo $row->filling;?>">
                                                    </div>
                                                    <div class="form-group">
                                                        <label for="itemCategoryUpdate">Category</label>
                                                        <select class="form-control" id="itemCategoryUpdate" name="itemCategoryUpdate">
                                                            <?php echo '<option>'.$row->category.'</option>';?>
                                                            <?php foreach($categories as $category) {echo '<option>'.$category->name.'</option>';}?>
                                                        </select>
                                                    </div>
                                                    <div class="form-group">
                                                        <label for="itemDescriptionUpdate">Description</label>
                                                        <textarea class="form-control" id="itemDescriptionUpdate" name="itemDescriptionUpdate" rows="3" placeholder="Item description (optional)"><?php echo $row->description;?></textarea>
                                                    </div>
                                                    <div class="form-group">
                                                        <label for="itemPriceUpdate">Price</label>
                                                        <input type="text" class="form-control" id="itemPriceUpdate" name="itemPriceUpdate" placeholder="Enter Price" value="<?php echo $row->price;?>">
                                                    </div>
                                            </div>

                                            <!-- Modal footer -->
                                            <div class="modal-footer">
                                                <button type="button" class="btn btn-cancel" data-dismiss="modal">Close</button>
                                                <input type="submit" name="editItem" id="editItem" value="Edit" class="btn btn-success" />
                                            </div>
                                        </div>
                                    </form>
                                </div>
                            </div>
                    <?php } ?>
                    </tbody>
                </table>
            </div>
        </div>
        <div class="card-footer small text-muted">Use the navigation bar to the left to edit the menu.</div>
    </div>
</div>

It sounds lika you need a column in the database to make each row unique. It could be an int that is set to primary key with the AUTO_INCREMENT feature. Then you use this column in the WHERE clauses of your update and delete queries. — Jeppsen, Jul 03 '18 at 08:51
Thanks for the response guys, that makes sense. In my table, the primary key is a composite of the Name and Filling. Is their a way within PHP I can uniquely identify the same way or would you both recommend just adding an AI Int column? Again, thanks so much for the response! — JamMan9, Jul 03 '18 at 08:53
Personaly I prefer AI Int columns when working with user input. It's easy to maintain and to add other tables to the database that relates to these columns. — Jeppsen, Jul 03 '18 at 09:08

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

All your code references are to $row->name and I would judge it to be bad practise to reference anything in the code by name. You want to be idenitifying by a unique identifier (typically a number) on each case. So you don't get this sort of ambiguity.

Image 2 shows me clicking the 'Edit' button for record 1-5.

You can't event clearly identify your own rows in this question, without needing to pull in outside data that is only co-incidentally different.

What you want is a unique identifier for each row of your database table.

This is something that is an absolute basic standard and can be read in the MySQL Dev Tutorial Here.

This SO question may also be helpful to you.

So you can update your MySQL (assuming that's your database):

ALTER TABLE users ADD uid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (uid);

(also please see here as to which int type is best for your situation)

Once you have a unique identifier for each row, you need to then use that identifier on your code so, for example :

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

And

<div class="modal" id="editItemModal<?php echo $row->uid;?>" 
                   name="editItemModal<?php echo $row->uid;?>">

And similarly apply this to your codebase throughout.

This stops any repetition of HTML ids and stops the code logic having issues with any ambiguity on the part of the identifying data given.

Thanks very much for the detailed response. Much appreciated. — JamMan9, Jul 03 '18 at 09:11

php foreach loop editing wrong record in DB

1 Answers1

What you want is a unique identifier for each row of your database table.