如何使用 mysqli 根据 WHERE 条件对单行进行 SELECT 和 DELETE 和 RETURN-解网

问：

如何进行mysqli php查询，以便SELECT请求既返回该行又删除MySQL数据库中的同一行？

这个 php 脚本可以很好地查询和返回基于最大数字邮政编码的单行（示例），但我的修改没有完成删除行：

<?php

echo phpversion();
// Create connection
$con = mysqli_connect("localhost", "mysqluser", "mysqlpass", "mysqldb");

// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Select all from designated assigned bot
$sql = "SELECT * FROM mysqltable WHERE zipsize=( SELECT max(zipsize) FROM mysqltable ) LIMIT 1;";

// Confirm there are results
if ($result = mysqli_query($con, $sql)) {
    // We have results, create an array to hold the results
    // and an array to hold the data
    $resultArray = array();
    $tempArray = array();
    // Loop through each result
    while ($row = $result->fetch_object()) {

        // Add each result into the results array
        $tempArray = $row;

        array_push($resultArray, $tempArray);
    }

    // Encode the array to JSON and output the results
    echo json_encode($resultArray);
}

// Close connections
mysqli_close($con);
?>

我尝试在各个地方添加此行以使用 Id 删除该行但没有成功，Id 是表中每一行的唯一代码（也尝试使用 $result 和 $resultArray 而不是 $row）：

$delsql= mysqli_query($conn,"DELETE FROM mysqltable WHERE Id= $row['Id']");

php mysql sql 选择 mysqli

<?php

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$con = new mysqli("localhost", "mysqluser", "mysqlpass", "mysqldb");
$mysqli->set_charset('utf8mb4'); // always set the charset

// Select all from designated assigned bot
$sql = 'SELECT * FROM mysqltable WHERE zipsize=( SELECT max(zipsize) FROM mysqltable ) LIMIT 1';
$resultArray = $con->query($sql)->fetch_all(MYSQLI_ASSOC);
echo json_encode($resultArray);

// now delete
$sql = 'DELETE FROM mysqltable WHERE zipsize=( SELECT max(zipsize) FROM mysqltable ) LIMIT 1';
$resultArray = $con->query($sql);

如果你不需要嵌套数组，并且你只获取一行，那么你可以改用。fetch_all(MYSQLI_ASSOC)fetch_assoc()

小心！这些查询可能会产生意外结果，因为除非您指定。ORDER BY

如果表中有一个主键/唯一键来唯一标识要选择/删除的行，则可以存储 ID 列表，然后执行查询。WHERE IN()

例如：

// Select all from designated assigned bot
$sql = 'SELECT * FROM mysqltable WHERE zipsize=( SELECT max(zipsize) FROM mysqltable )';
$resultArray = $con->query($sql)->fetch_all(MYSQLI_ASSOC);
echo json_encode($resultArray);

// get all ids from our array
$ids = array_column($resultArray, 'Id');

// now delete
$sql = 'DELETE FROM mysqltable WHERE Id IN('.str_repeat('?,', count($ids) - 1) . '?)';
$stmt = $con->prepare($sql);
$stmt->bind_param(str_repeat('s', count($ids)), ...$ids);
$stmt->execute();

如何使用 mysqli 根据 WHERE 条件对单行进行 SELECT 和 DELETE 和 RETURN

How can I use mysqli to SELECT and both DELETE and RETURN a single row based on WHERE condition

评论

评论