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();
}
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:
$stmt1 outside of the while loop - that's the point of a prepared statement