php table update multiple records at one time

Question

I have a table that retrieves the username and availability status from the users table. Right now in my testing database I have 3 records. If I try to change the availability field for any of them, noting in updated.

This is my availability.php file (the first line keeps getting cut off):

<form method="POST" action="av_update.php">
    <table cellspacing="0" width="50%" id="users" class="table table-striped table-hover table-responsive" style="margin-left:0;">
        <thead class="alert-info">
            <tr>
                <th>ID</th>
                <th>Username</th>
                <th>Available?</th>
                <th>Updated By</th>
            </tr>
        </thead>
        <tbody>
            <?php
                require 'config/config.php';
                $sql = $db_con->prepare("SELECT * FROM `users` ORDER BY `id` DESC");
                $sql->execute();
                while($row = $sql->fetch()){
            ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td><?php echo $row['username']; ?></td>
                <td><select name="availability" id="availability" style="width:150px;font-size:1.1em;">
                            <option style="background-color:rgb(0,0,150);color:rgb(200,200,200);font-size:1.2em;" value='<?php echo $row['availability']; ?>'><?php echo $row['availability']; ?></option>
                            <option value="Available">Available</option>
                            <option value="Not Available">Not Available</option>
                            </select></td>
                <td><input class="form-control" id="av_updatedby" name="av_updatedby" value="<?php echo htmlspecialchars($_SESSION["username"]); ?>">
            </tr>
            <?php
                }
            ?>
        </tbody>
    </table>

Here is my av_update.php file:

require_once 'config/config.php';

if(ISSET($_POST['update'])){
    try{
        $id = $_GET['id'];
        $availability = $_POST['availability'];
        $av_updatedby = $_POST['av_updatedby'];
        
        
        $db_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "UPDATE `crm_users` SET `availability` = '$availability', `av_updatedby` = '$av_updatedby' WHERE `id` = '$id'";
        $db_con->exec($sql);
    }catch(PDOException $e){
        echo $e->getMessage();
    }
   
    header("Location: av_updated.php");
    die;
}

Updating any other field from other forms and inserting and deleting data from all other forms is working properly, so I know it's not the connection file.

Any help on getting the code to update the table would be greatly appreciated.

Your code is open to [SQL injection](https://stackoverflow.com/questions/601300/what-is-sql-injection) attacks. Also, what does `echo $sql;` show? Is it a valid SQL statement? — kmoser, Mar 07 '22 at 04:11
This code is only in development so it's very basic and just trying to see if it will update the table. I will give echo $sql a shot. — wilcan, Mar 07 '22 at 04:23
What have you tried to resolve the problem? Where are you stuck? — Nico Haase, Mar 07 '22 at 13:37

score 1 · Accepted Answer · answered Mar 07 '22 at 04:15

1

Your form action must go to av_update.php?id=, since you're getting that value from the $_GET array.

Also, you should sanitize and validate the input data, or otherwise you're exposing your system to SQL Injection and other kinds of attacks.

answered Mar 07 '22 at 04:15

danielperaza

412
3
12

Thanks @danielperaza! That worked with also removing the " WHERE `id` = '$id'" from the update query. This code is only being used on localhost for now, so I will clean it up before bringing it to production. – wilcan Mar 07 '22 at 04:33

php table update multiple records at one time

1 Answers1