How to write cron job to run an sql file in shared hosting

I recently wanted to execute a cron job to run an sql file in a shared hosting website. In my case, I wanted to reset by database every hour and everyday for a demo website. There are two methods to achieving this;

• Using the cron job from the cpanel
• Using an external service to run the cron job for you

METHOD 1

I learnt from this task that running the sql file directly from the cron job is not so efficient. In fact, in my own project, it didn’t work. Therefore, I changed my method. I wrote the script in a php file calling the sql file, while the cron job calls the php file, because it is more convenient that way. Though, if you just want to run a php script, it can be passed to the cron job in the cpanel and hopefully, it works for you.

In the cron job on the cpanel, there is a guide in order to use it properly. For example

/usr/local/bin/php -f /home/(username)/public_html/nameofthefile.php

<?php

$con = mysqli_connect('localhost', 'username', 'password');
if (!$con) {
    die('Could not connect: ' . mysqli_connect_error());
}

$sql = 'DROP DATABASE db_name';
if (mysqli_query($con, $sql)) {
    //echo "Database db_name was successfully dropped\n";
} else {
    echo 'Error dropping database: ' . mysqli_connect_error() . "\n";
}
$createsql = 'CREATE DATABASE db_name';
if (mysqli_query($con, $createsql)) {
    //echo "Database db_name created successfully\n";
} else {
    echo 'Error creating database: ' . mysqli_connect_error() . "\n";
}

$mysql_host = "localhost";
$mysql_database = "db_name";
$mysql_user = "username";
$mysql_password = "password";
# MySQL with PDO_MYSQL
$db = new PDO("mysql:host=$mysql_host;dbname=$mysql_database", $mysql_user, $mysql_password);

$query = file_get_contents("file.sql"); // calls the sql file here

$stmt = $db->prepare($query);

if ($stmt->execute()) {
    echo "Success";
} else {
    echo "Fail\n";
}

then you select the timing you need for this job to run for.

METHOD 2

In my case, this file above didn’t work on the server, but worked on the localhost which was really surprising. The PDO part of the code, didn’t work to be precise, so initially I thought my shared hosting service didn’t enable PDO for my php version which was 7.3 at that time, after confirmation that it was enabled, I then knew I had to find an alternative to the cron job issue. After much researching, I found google cloud platform (serverless computing — not free) so I couldn’t use it, I then found another online service- webcron that could do this. I registered and configured for what I needed. The only major thing you need to supply is the url.

Therefore, i just supplied https://domain-name.com/nameoffile.php. and BOOM, it worked as I expected.