Good day to you! I am looking for a way to implement the following: It is necessary to take data from a database on a remote server, mark the fields that were in the selection and insert them into the database on another server. My thoughts led me to the following: I added a column to the database from which I take the data IsUpdated = 0, in a cycle I take 300 IsUpdated in an array, UPDATE tables from which I took the data IsUpdated = 1, and INSERT into the final table. It is not possible to run the cycle until all the required rows have the number 1 in the IsUpdated field. The total of the code is 743 records in the database instead of 62 with a few thousand, and the second table is completely updated with IsUpdated instead of 0 becoming 1. I don’t understand what’s wrong . No timeout error messages.

 do{ $mysqli = mysqli_connect('host', 'login', 'pass', 'db_1'); mysqli_query($mysqli, 'SET NAMES \'utf8\''); if ($mysqli->connect_error) { die('Ошибка подключения (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } $query = "SELECT row1, row2, row3 FROM `db_1`.`tabe_1` WHERE Result <='3' AND State = 'disabled' AND IsUpdated = 0 ORDER BY abc DESC LIMIT 300"; if(!$stmt = $mysqli->prepare($query)) { die('Ошибка выполнения запроса (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } $stmt->execute(); $stmt->bind_result($row1, $row2, $row3); $sql = array(); while ($stmt->fetch()) { $row1 = $mysqli->real_escape_string($row1); $row2 = $mysqli->real_escape_string($row2); $row3 = $mysqli->real_escape_string($row3); $sql[] = "('$row1', '$row2', '$row3')"; } $mysqliUpdate = "UPDATE `db_1`.`tabe_1` SET `IsUpdated` = 1 WHERE Result <='3' AND State = 'disabled' AND `IsUpdated` = 0 ORDER BY abc DESC LIMIT 300"; $mysqli->query($mysqliUpdate); $mysqli->close(); $mysqli1 = mysqli_connect('localhost', 'root', '', 'example'); mysqli_query($mysqli1, 'SET NAMES \'utf8\''); if ($mysqli1->connect_error) { die('Ошибка подключения (' . $mysqli1->connect_errno . ') ' . $mysqli1->connect_error); } if(count($sql) > 0) { $query1 = "INSERT INTO `db_2`.`table_2` VALUES " . implode(',', $sql) . " ON DUPLICATE KEY UPDATE row1 = VALUES(row1), row1 = VALUES(row2), row1 = VALUES(row3)"; $mysqli1->query($query1); $mysqli1->close(); } }while (count($sql) > 0);
  • I think your script timeout falls off or memory. - Naumov
  • @Naumov on a remote server, everything is updated, localhost also does not speak about errors and does not hang. - Eugene
  • Is the server logs empty too? - Naumov
  • 2
    And you update not those 300 records which selected. You choose with ORDER BY, and you give UPDATE without sorting ... In general, the approach is crooked, some change in the database and update can change between select and update and not the records that were selected even with the correct sorting - Mike
  • one
    In general, I would suggest first changing IsUpdated to some unique number (which, for example, is stored in a different table and increased each time), so to say "unload number". after that, select and transfer to another database entries with this particular number. - Mike

0