时间戳

首页 > 解决方案 > PHP 多重查询

php - PHP 多重查询

问题描述

不能在不同的表上同时执行插入和删除吗？还是我错过了什么

if(isset($_POST['update']))
{
    $id = $_POST['delete_id'];
    $name= $_POST['edit_name'];
    $name2= $_POST['edit_name2'];
    $pic = $_POST['edit_pic'];
    $contactnumber = $_POST['edit_contact'];
    $address = $_POST['edit_address'];
    $details = $_POST['edit_details'];

    $query = "INSERT INTO tabletest (name,name2,pic,contact,address,details,) VALUES ('$name' , '$name2' , '$pic' , '$contactnumber' , '$address' , '$details')";
    $query .= "DELETE FROM tabletest2 WHERE id='$id'";
    if (mysqli_multi_query($connection, $query))
    {
        $_SESSION['success'] = "Data Updated";
        header('Location: blankpage.php');
    } 
    else 
    {
        $_SESSION['status'] = "Error, Please try again";
        header('Location: blankpage.php');
    }
    
}

标签： phpmysql

解决方案

可以同时执行 DELETE 和 INSERT，但它们需要是单独的查询。如果您想让这两个操作都成为原子操作（两者都必须成功或都不应该执行），那么您需要使用DB transactions。

要将 2 个查询作为单个原子语句执行，您可以执行以下操作：

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli();
$connection->set_charset('utf8mb4'); // always set the charset

if (isset($_POST['update'])) {
    $id = $_POST['delete_id'];
    $name = $_POST['edit_name'];
    $name2 = $_POST['edit_name2'];
    $pic = $_POST['edit_pic'];
    $contactnumber = $_POST['edit_contact'];
    $address = $_POST['edit_address'];
    $details = $_POST['edit_details'];

    // start transaction
    $connection->begin_transaction();

    try {
        $stmt = $connection->prepare('INSERT INTO tabletest (name,name2,pic,contact,address,details,) VALUES (?,?,?,?,?,?)');
        $stmt->bind_param('ssssss', $name, $name2, $pic, $contactnumber, $address, $details);
        $stmt->execute();

        $stmt = $connection->prepare('DELETE FROM tabletest2 WHERE id=?');
        $stmt->bind_param('s', $id);
        $stmt->execute();
    
        // save data and end transaction
        $connection->commit();

        $_SESSION['success'] = "Data Updated";
        header('Location: blankpage.php');
    } catch (Exception $e) {
        $connection->rollback();
        $_SESSION['status'] = "Error, Please try again";
        header('Location: blankpage.php');
    }
}

推荐阅读