I am using PHP PDO, this the code :
for ($i = 0; $i < 1000; $i++) {
//select room and insert to chart
$thisDay=date ("Y-m-d");
$query = $db->prepare("SELECT room FROM d_room WHERE activ=1 ORDER BY room");
$query->execute();
for($a = 1; $result = $query->fetch(); $a++) {
$query2 = $db->prepare("INSERT INTO d_chart (date,room,status) VALUES (? + INTERVAL ? DAY,?,0)");
$query2->execute(array($thisDay,$i,$result['room']));
}
}
this code run too slow, How to make better code and fast, < 2 second.
You should be able to make this significantly simpler by using an INSERT...SELECT query (assuming MySQL)
// Make sure you see any errors that might occur
ini_set('display_errors', 'On');
error_reporting(E_ALL);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$thisDay = date('Y-m-d');
$stmt = $db->prepare('INSERT INTO d_chart (date, room, status)
SELECT (:thisDay + INTERVAL :days DAY), room, 0
FROM d_room WHERE activ = 1'); // there's no need to order these results
$stmt->bindParam(':thisDay', $thisDay);
$stmt->bindParam(':days', $i, PDO::PARAM_INT);
for ($i = 0; $i < 1000; $i++) {
$stmt->execute();
}
If d_room.activ has an index, this would be even faster.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments