skip to Main Content

I’m having difficulty to make a cron job on cpanel to update the state of an user if the account has been created for 5 days. It just doesn’t work at all. When I program the cron job on the cpanel it does nothing. I even tested it with minutes, but doesn’t work.

Here’s my table on database:

CREATE TABLE `users_tmp` (
  `idUsers` int(11) NOT NULL,
  `ipUser` varbinary(16) NOT NULL,
  `uidUsers` longtext COLLATE utf8_unicode_ci NOT NULL,
  `emailUsers` longtext COLLATE utf8_unicode_ci NOT NULL,
  `pwdUsers` longtext COLLATE utf8_unicode_ci NOT NULL,
  `dataUser` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stateUser` longtext COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And here’s my code to update (update_row.php):

<?php
require 'dbh.inc.php';


$sql = "UPDATE users_tmp SET stateUser = 'Inactive' WHERE timestamp < NOW() - INTERVAL 5 DAY;


2

Answers


  1. Create a shell script and use this for the cron job.

    No need for php at all

    #!/bin/bash
    
    mysql --user=[username] --password=[password] --database=[db name] --execute="UPDATE users_tmp SET stateUser = 'Inactive' WHERE timestamp < NOW() - INTERVAL 5 DAY;"
    

    ALso there are events in mysql

    Enable it with

    SET GLOBAL event_scheduler = ON;
    

    and create an event like this:

    CREATE EVENT name_of_event
    ON SCHEDULE EVERY 1 DAY
    STARTS '2020-04-19 00:00:00'
    DO
    UPDATE users_tmp SET stateUser = 'Inactive' WHERE timestamp < NOW() - INTERVAL 5 DAY;
    
    Login or Signup to reply.
  2. If I understand correctly your sql query is already working on your environment and you wish to create a cron job on linux to call your page which is made in PHP!

    If above is the correct scenario, then you first need to make sure the sql script is executed within your cron_script.php file as follows:

    <?php
    require 'dbh.inc.php';    
    // Let's assume that you have an active connection to your database by now
    $sql = "UPDATE users_tmp SET stateUser = 'Inactive' WHERE timestamp < NOW() - INTERVAL 5 DAY";
    if (mysqli_query($conn, $sql)) {
        // createCronLog is an optional function that you can create and use to make a log of what has happened on your cron activity!
        createCronLog("Record updated successfully");
    } else {
        createCronLog("Error updating record: " . mysqli_error($conn));
    }
    mysqli_close($conn);
    ?>  
    

    Now if you wish to place the above file into let’s say your home directory:
    "/home/yourusername/path/to/cron/cron_script.php"
    somewhere outside the public_html (so that it can be only called from your cron job and not from www).

    Here is your cron job command:

    /usr/local/bin/php /home/yourusername/path/to/cron/cron_script.php
    

    Here is the cron job command and activity call once/per/hour

    0   *   *   *   *  /usr/local/bin/php /home/yourusername/path/to/cron/cron_script.php
    

    If your file has dependencies such as $_GET or $_POST variables, you may put the cron_script.php file somewhere inside your public_html folder and call your cron file using a full path (with domain) for the crob to call your script:

    0   *   *   *   *  /usr/local/bin/php https://yourdomain.tld/path/to/cron/cron_script.php
    

    I have tested the above and it works with in linux environment on centos 7 running cPanel. But it must also work on whatever environment your setup is, as long as your cron job is executing correctly and calling your cron_script.php file.

    PS:
    Note that there is a space between "/usr/local/bin/php" AND "/home/yourusername/path/to/cron/cron_script.php"

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search