php - 成功插入后记录未更新

Question

我有一行，如下图所示

Image

我正在从数据库中获取 id 和名称，其他选择选项将在特定行中更新。

我的 ID 和名称获取如下所示

$sql = "SELECT id, name,day,week,month,pickup,drop_location FROM crm LIMIT 10";
        $resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
        while( $emp = mysqli_fetch_assoc($resultset) ) {        
        ?>  
            <tr style="text-align: center;">  
                <td><?php echo $emp["id"]; ?></td> 

我正在更新行，但它不起作用。

form.php

<form action="drop.php" name = "myForm" method="post">
                <td id="s1">    
                    <select style="width: 150px;" class="blue" id="pick" data-style="btn-info" name="pickup">
                        <option value="ward" id="ward1">Floor 1</option>
                        <option value="ward1" id="ward2">Floor 2</option>
                        <option value="ward2" id="ward3">Floor 3</option>
                    </select>

                </td>
                <td id="s2">
                    <select style="width: 150px;" class="blue" id="drop" data-style="btn-success" name="drop_location">
                        <option value="ward"  id="ward1">Floor 1</option>
                        <option value="ward1" id="ward2">Floor 2</option>
                        <option value="ward2" id="ward3">Floor 3</option>
                    </select>

                </td>
                <td id="s3">
                    <button class="btn btn-primary" type="submit" name="submit">submit</button>
                    <select style="width: 150px;" class="blue" id="stat" data-style="btn-warning" name="stat">

                        <option value="allocate">Allocated</option>
                        <option value="cancel">Cancelled</option>   
                    </select>

                </td>  

                <td id="s4">
                    <select style="width: 150px;" class="blue" id="status" data-style="btn-primary" name="status">
                        <option value="pending">pending</option>
                        <option value="Allocated">Allocated</option>
                        <option value="Engaged">Engaged</option>
                    </select>

                </td>  

            </form>
            </tr> 

drop.php

 <?php
    /* Attempt MySQL server connection. Assuming you are running MySQL
    server with default setting (user 'root' with no password) */
    $mysqli = new mysqli("localhost", "root", "", "redeem");

    // Check connection
    if($mysqli === false){
        die("ERROR: Could not connect. " . $mysqli->connect_error);
    }


    // Escape user inputs for security
    $pickup = $mysqli->real_escape_string($_POST['pickup']);
    $drop_location = $mysqli->real_escape_string($_POST['drop_location']);
    $stat = $mysqli->real_escape_string($_POST['stat']);
    $status = $mysqli->real_escape_string($_POST['status']);

    // attempt insert query execution
    $sql = "UPDATE crm SET pickup='$pickup',drop_location='$drop_location',stat='$stat', status='$status' WHERE id = 'id' ";

    if($mysqli->query($sql) === true){
        echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
    }

    // Close connection
    $mysqli->close();
    ?>

我已经为这样的选择创建了 id

 $(document).ready(function() {
$("#pick, #drop, #stat, #status").on('change', function () {
    alert($(this).val());
});

我不知道错误在哪里。请帮帮我。

提前谢谢您!!!

Answer 1

您已经从数据库中获取记录，因此您的表单中需要有一个隐藏字段，其中包含每个记录的 ID 值。

将其添加到您的表单中:

<input type="hidden" name="id" value="<?=$emp['id']?>">

然后你就可以使用该id来更新记录，我还建议你使用准备好的语句来防止sql注入(inject)。

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "redeem");

// Check connection
if ($mysqli === false) {
    die("ERROR: Could not connect. " . $mysqli->connect_error);
}



$pickup        = isset($_POST['pickup']) ? $_POST['pickup'] : null;
$drop_location = isset($_POST['drop_location']) ? $_POST['drop_location'] : null;
$stat          = isset($_POST['stat']) ? $_POST['stat'] : null;
$status        = isset($_POST['status']) ? $_POST['status'] : null;
$id            = isset($_POST['id']) ? intval($_POST['id']) : null;


// attempt insert query execution
$sql = "UPDATE crm SET pickup = ? ,drop_location= ? ,stat= ? , status= ?  WHERE id = ? ";

$stmt = $mysqli->prepare($sql); //prepare statement
$stmt->bind_param("ssssi", $pickup, $drop_location, $stat, $status, $id);


if ($mysqli->execute()) { //execute the query
    echo "Records inserted successfully.";
} else {
    echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>