PHP使用while循环准备语句[关闭]

Question

I am doing some work in php with prepared statements. My task is to first select some data from one table and then insert the data into another table. How can I use while loop in prepared statement to insert the records?

Here is the code:

stmt=$db->prepare("SELECT userid, order_number, balance from plus_wallet  limit 0,3");
$stmt->execute();
$stmt->bind_result($userid, $orderid, $balance);
while($stmt->fetch()) {
    $stmt1=$db->prepare("INSERT INTO `plus_user_wallet`(`userid`, `balance`, `update_time`) VALUES (?,?, NOW() + INTERVAL 45000 second)");
    $stmt1->bind_param('ss' , $userid, $balance);
    $stmt1->execute();
    $stmt1->close();
    $stmt->close();
}

Answer 1

If the order_number field is not required anywhere else and you're selecting it because it's in the table, you could use:

insert into
plus_user_wallet
    (userid,balance,update_time) 
select 
    userid,balance,now()+interval 45000 second
from
    plus_wallet
limit
    0,3;

You could make that into a prepared statement if you require a single userid value by adding where userid=? into the select statement after from plus_wallet

Obviously, if you do require the order_number for elsewhere then you will need a loop system.

A few notes regarding your code:

• Prepare $stmt1 outside of the while loop - that's the point of a prepared statement
• Close all prepared statements after the while loop is complete