PHP Update in While Loop

Question

I've searched far and wide on how to accomplish something like this without result.

What I want is to update a single row to 1 when the button is pressed, instead it updates every row.

Php:

if(isset($_GET['updateValidation'])) {
$stmt5 = $DB_con->prepare("UPDATE comment_imgs SET validation=1 WHERE id=".$id."");
$stmt5->execute();
}

Form:

<form method="get">
<input type="submit" name="updateValidation" id="updateValidation">
<label for="updateValidation" class="btn btn-primary">Update Validation</label>
</form>

.. and how I have $id defined:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$id = $row['id'];

EDIT: Excuse my mess of a code.

    <body>
        <div class="container">         
            <div class="page-header">
                <h1>View Uploaded Projects <small>Admin backend</small> </h1>
            </div>
            <?php include("dbconfig.php");?>
            <div class="panel panel-default">
                <div class="panel-body">
                    <div class="formlayout">
                    <h1><small>Sort by:</small></h1>
                    <form method="get" class="form-inline">
                    <input id="sortDate" name="sortDate" type="submit">
                    <label for="sortDate" class="btn btn-outline-info" id="sortDateCSS">Date</label><br>
                    <input id="sortProject" name="sortProject" type="submit">
                    <label for="sortProject" class="btn btn-outline-info" id="sortProjectCSS">Project</label><br>
                    <input id="sortUser" name="sortUser" type="submit">
                    <label for="sortUser" class="btn btn-outline-info" id="sortUserCSS">User</label><br>
                    </form>
                    </div>
<?php 

/*include("class.user.php");*/
$user_id = $_SESSION['user_session'];
$user_name = $_SESSION['user_name'];

/* Laddar sidan med nyaste datum först */
$stmt = $DB_con->prepare("SELECT * FROM comment_imgs ORDER BY date DESC");
$stmt->execute();

/* Sortera projekt */
if(isset($_GET['sortDate'])){
    $stmt = $DB_con->prepare("SELECT * FROM comment_imgs ORDER BY date DESC");
    $stmt->execute();
}

if(isset($_GET['sortProject'])){
    $stmt = $DB_con->prepare("SELECT * FROM comment_imgs ORDER BY project_id DESC");
    $stmt->execute();
}

if(isset($_GET['sortUser'])){
    $stmt = $DB_con->prepare("SELECT * FROM comment_imgs ORDER BY user_id DESC");
    $stmt->execute();
}
/* Sortering slut */


/* Loop för Card Display / Modals */
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
$id = $row['id'];
$date = $row['date'];
$comment = $row['comment']; 
$project_id = $row['project_id'];
$user_name = $row['user_name'];
$validation = $row['validation'];
?>

<!-- Card Display -->                   
<div class="card" style="width: 18rem;" id="display">
<div class="card-body">
<h5 class="card-title"><?php echo $project_id;?></h5>
<p class="card-text"><?php echo $date;?></p>
<p class="card-text"><?php echo $user_name;?></p>
<p class="card-text"><?php echo $comment;?></p>


<!-- Button trigger modal -->
<button type="button" class="btn btn-primary btn-block" data-toggle="modal" data-target="#exampleModal<?php echo $id;?>" id="formButtons">
 Button
</button>


<!-- Modal -->
<div class="modal fade" id="exampleModal<?php echo $id;?>" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-lg" role="document">
        <div class="modal-content">
            <div class="modal-header">
            <h5 class="modal-title" id="exampleModal<?php echo $id;?>Label"><?php echo $project_id;?></h5>
            <button type="button" class="close" data-dismiss="modal" aria-label="Close">
            <span aria-hidden="true">&times;</span>
            </button>
            </div>
            <div class="modal-body">
            <?php echo $date;?>
            <?php echo $comment;?>
            <?php echo $user_name;?>
                <form method="get">
            <input type="submit" name="updateValidation" id="updateValidation">
            <label for="updateValidation" class="btn btn-primary">Update Validation</label>
            </form>
            <?php
            if(isset($_GET['updateValidation'])) {
                $stmt5 = $DB_con->prepare("UPDATE comment_imgs SET validation=1 WHERE id=".$id."");
                $stmt5->execute();
                }
            ?>
            <?php if ($validation == 1) {
                echo "Avklarad";
                } else {
                    echo "WIP";
                }?>
            <?php

            $stmt2 = $DB_con->prepare("SELECT image_path, image_name, display_id FROM uploads LEFT JOIN comment_imgs ON uploads.display_id = comment_imgs.project_id");
            $stmt2->execute();

            while ($row2 = $stmt2->fetch(PDO::FETCH_ASSOC))
            {
            $image_path = $row2["image_path"]."/".$row2["image_name"];
            $display_id = $row2['display_id'];

            ?><?php if ($project_id == $display_id){?>
            <a href="<?php echo $image_path; ?>"><img src="<?php echo $image_path; ?>" class="images" /></a><?php }} ?>
        </div>
    <div class="modal-footer">
    <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
    <button type="button" class="btn btn-primary">Save changes</button>
</div>
</div>
</div>
</div>
</div>
</div>
<?php
    }
?>
</div>

        <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
        <script src="js/jQuery.js"></script>
        <!-- Include all compiled plugins (below), or include individual files as needed -->
        <script src="js/bootstrap.min.js"></script> 
    </body>

You need to rework your logic. It seems like it is working as intended... — Ice76
– Ice76, Commented Mar 8, 2018 at 18:05
Even if I put the code outside of the loop it still updates every row instead of one. — Godavni pimp
– Godavni pimp, Commented Mar 8, 2018 at 18:14
You need to put the ID into the form, and then use $_GET['id'] in the UPDATE. — Barmar
– Barmar, Commented Mar 8, 2018 at 18:15
You should also use parametrized queries instead of concatenating variables, to protect against SQL injection. — Barmar
– Barmar, Commented Mar 8, 2018 at 18:16
WARNING: When using PDO you should be using prepared statements with placeholder values and supply any user data as separate arguments. In this code you have potentially severe SQL injection bugs. Never use string interpolation or concatenation and instead use prepared statements and never put $_POST, $_GET or any user data directly in your query. Refer to PHP The Right Way for general guidance and advice. — tadman
– tadman, Commented Mar 8, 2018 at 18:30

Ice76 · Accepted Answer · 2018-03-08 18:25:11Z

1

You do not need to run a while loop to update every record with a unique ID. in MySQL, the Update statement updates all rows that meet the where clause. Putting it in a while loop is redundant and inefficient. You only need to run update once to update all rows with your id field.

You should also understand that if the string is outside the while loop, it will set the string to what the value of $id is at that time, and use it inside the loop. Maybe update your question with code that isnt broken up

answered Mar 8, 2018 at 18:25

Ice76

1,1308 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP Update in While Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related