I am trying to create a demo website for users to test. I want to restore the databse every x hours. When I run the php script in browser it works. It drops the tables and restores the databse without any issues. When I add the same file in cron, the tables are dropped but it does not restore the databse. I am not sure why it’s not restoring the database.
<?php
$mysqli = new mysqli("localhost", "", "", "");
$mysqli->query('SET foreign_key_checks = 0');
if ($result = $mysqli->query("SHOW TABLES"))
{
while($row = $result->fetch_array(MYSQLI_NUM))
{
$mysqli->query('DROP TABLE IF EXISTS '.$row[0]);
}
}
$mysqli->query('SET foreign_key_checks = 1');
echo "Deleted databse";
sleep(5);
$sqlScript = file('demo.sql');
foreach ($sqlScript as $line) {
$startWith = substr(trim($line), 0 ,2);
$endWith = substr(trim($line), -1 ,1);
if (empty($line) || $startWith == '--' || $startWith == '/*' || $startWith == '//') {
continue;
}
$query = $query . $line;
if ($endWith == ';') {
mysqli_query($mysqli,$query) or die('<div class="error-response sql-import-response">Problem in executing the SQL query <b>' . $query. '</b></div>');
$query= '';
}
}
$mysqli->close();
echo '<div class="success-response sql-import-response">SQL file imported successfully</div>';
?>
Cron job
*/10 * * * * /usr/local/bin/php /home/demo/public_html/db.php
2
Answers
aynber provided the answer in a comment
I updated the sql file path and it worked.
As mentioned above, in your script there is a relative path. Therefore you need to run it from the directory where script is. You can simply replace the cron command to this:
&& means after applying the first command, apply the following command.