php table update multiple records at one time

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.

Answer

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.

Advertisement

Answer