Get data from table and Insert into another table from while loop

Question

I am having a little issue getting data from a table with while loop. What i want to do is simple I want to take all data from table cart with cookie value from table orders that matches a cookie value and query tables cart to extract data that matches the cookie value in the cart table and place them in table orders_final . Now this is . Now the final part after querying cart table with cookie value gotten from order table, i now want to place the data into orders_final table with all that matches that cookie value from order and cart

$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();  
            }   
        }       
    }
}

You can probably avoid having two queries and a loop, by doing a `INSERT INTO..SELECT` query instead. Some sensible formatting of the code (indenting properly) would make it a lot easier to read, too. — Qirel, Sep 10 '17 at 14:38
You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Sep 10 '17 at 14:40

score 1 · Answer 1 · answered Sep 10 '17 at 14:59

A better approach is to run fewer queries, that do more. Instead of selecting an entire table and looping over it to run up to 3 queries per iteration (which quickly becomes a lot of queries!), you can use a INSERT INTO...SELECT query instead. Using a transaction, it's also possible to ensure that everything goes through before committing the changes - so you don't end up deleting something that didn't transfer properly.

The code below has been altered to reduce the amount of queries down to three (and none is looped!), and usage of prepared statements has been implemented.

$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();
}

Get data from table and Insert into another table from while loop

1 Answers1