php - 从表中获取数据并从 while 循环插入到另一个表中

Question

我在使用 while 循环从表中获取数据时遇到了一个小问题。我想做的很简单，我想从 table cart 中获取所有数据，并从 table orders 中获取与 cookie 值匹配的 cookie 值，并查询 table cart 以提取匹配的数据购物车表中的 cookie 值并将它们放入 table orders_final .现在这是 .现在是使用从订单表中获取的 cookie 值查询购物车表后的最后一部分，我现在想将数据放入 orders_final 表中，其中包含与订单和购物车中的 cookie 值匹配的所有数据

$zomo = $_COOKIE['shopa']; // this is the cookie that is stored in the cart table and updated when the transaction is successful
$get_products = "SELECT * FROM `cart` WHERE cookie_value = '$zomo'";
$limo = mysqli_query($con, $get_products);

while($colo = mysqli_fetch_array($limo)){
    $product_id = $colo['product_id'];
    $order_quantity = $colo['order_quantity'];
    $cookie_value = $colo['cookie_value'];
    //var $dance is when i update the table with data after payment and data gotten from my payment processing company
    $dance = "UPDATE `orders` SET `status`='$r_status',`time`='$r_time',`date`='$r_date',`reference`='$r_reference',`transaction_status`='$r_transaction_status',`transaction_method`='$r_transaction_method',`final_price`='$r_final_price',`order_id`='$r_order_id',`currency`='$r_currency',`referrer`='$r_referrer' WHERE cookie_bought = '$zomo'";
    $uii = mysqli_query($con, $dance);

    if ($uii){
        //this variable insert is where i want to insert all data gotten from cart table above and insert into orders_final, where order table holds the cookie value which was created during shopping which is cookie name shopa held in the variable zomo
        $insert = "INSERT INTO `orders_final`(`product_id`, `cookie_value`, `trx_id`, `order_quantities`) VALUES ('$product_id','$zomo','$r_reference','$order_quantity')"; 
        $bena = mysqli_query($con, $insert);    

        if ($bena){
            $delc = "DELETE FROM `cart` WHERE cookie_value = '$zomo'";
            $tipee = mysqli_query($con, $delc);

            if ($tipee){
                perform_success();  
            }   
        }       
    }
}

Answer 1

一个更好的方法是运行更少的查询，做更多的事情。您可以使用 INSERT INTO...SELECT 而不是选择整个表并对其进行循环以每次迭代 最多运行 3 个查询(这很快就会变成很多查询!)而是查询。使用事务，还可以确保在提交更改之前一切都通过 - 所以您最终不会删除未正确传输的内容。

下面的代码已被更改以将查询数量减少到三个(并且没有一个是循环的!)，并且已经实现了准备好的语句的使用。

$stmt = $con->prepare("INSERT INTO orders_final (`product_id`, `cookie_value`, `trx_id`, `order_quantities`) 
                                            SELECT product_id, ?, order_quantity, ? 
                                            FROM cart
                                            WHERE cookie_value=?");
$stmt->bind_param("sss", $zomo, $r_reference, $zomo);
if ($stmt->execute()) {
    $stmt->close();

    $stmt = $con->prepare("UPDATE orders 
                            SET status=?, time=?, date=?, reference=?, transaction_status=?, 
                                 transaction_method=?, final_price=?, order_id=?, 
                                 currency=?, referrer=?
                            WHERE cookie_bought=?");
    $stmt->bind_param("sssssssssss", $r_status, $r_time, $r_date, $r_reference, $r_transaction_status, $r_transaction_method, $r_final_price, $r_order_id, $r_currency, $r_referrer, $zomo);

    $dance = "UPDATE `orders` SET `status`='$r_status',`time`='$r_time',`date`='$r_date',
    `reference`='$r_reference',`transaction_status`='$r_transaction_status',`transaction_method`='$r_transaction_method',`final_price`='$r_final_price',`order_id`='$r_order_id',`currency`='$r_currency',`referrer`='$r_referrer' WHERE cookie_bought = '$zomo'";

    $stmt = $con->prepare("DELETE FROM cart WHERE cookie_value=?");
    $stmt->bind_param("s", $zomo);
    $stmt->execute();
    $stmt->close();
}

• mysqli::prepare()
• mysqli_stmt::bind_param()
• MySQL INSERT INTO..SELECT