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
aynber provided the answer in a comment
Cron jobs either run relative to the home of the user running it, or the server root if it's using
/etc/cron
.file('demo.sql');
is looking for a relative file path to where the script is being run from, so it will fail when being run by cron
I updated the sql file path and it worked.