CODEWITHSUNDEEP
phpmysqlcron
Blaise M.
Blaise M.

Reputation: 575

change status after 1 hour and after 24 hours

I have installed a cron job to run automatically. It checks for the jobs in the database and after one hour it should change from private to public status and after 24 hours it should change from public to expired. The cron job is running smoothly but the following code is not bringing the desired outcome. The database data doesn't change and when I run it myself, nothing is being shown as a message.

How can I twist it so that it achieves that?

My code: 

      // Check and do something: private to public
      $now=date("Y-m-d H:i:s");
      $timeBefore=strtotime($now-3600);
      $check=mysqli_query($con,"SELECT * FROM ibirakas WHERE status='private' AND added<'$timeBefore'") or die(mysql_error());
      while($exe_check=mysqli_fetch_assoc($check)){
        $id=$exe_check['kiraka_id'];
        $query=mysql_query("UPDATE ibirakas SET status='public' WHERE id='$id'") or die(mysql_error());
        if($query==true){
          echo "CRON_1_DONE";
        } else {
          echo "CRON_1_FAIL";
        }
      }
      //check and do something: public to expired
      $now=date("Y-m-d H:i:s");
      $timeBefore=strtotime($now-86400);
      $check=mysqli_query($con,"SELECT * FROM ibirakas WHERE status='public' AND added<'$timeBefore'") or die(mysql_error());
      while($exe_check=mysqli_fetch_assoc($check)){
        $id=$exe_check['kiraka_id'];
        $query=mysql_query("UPDATE ibirakas SET status='expired' WHERE id='$id'") or die(mysql_error());
        if($query==true){
          echo "CRON_2_DONE";
        } else {
          echo "CRON_2_FAIL";
        }
      }

I have included connect.php file and also i have opened the php open and close tags(which are not included here) Your help will be appreciated. Thank you

Upvotes: 1

Views: 1309

Answers (2)

Blaise M.
Blaise M.

Reputation: 575

I have been able to find a relevant answer to this question. Since I wanted to use MySQL in the first place, this simple solution did it. 

      <?php
      include("connect.php");
      date_default_timezone_set("Africa/Kigali");
      // Change from private to public
      $query=mysqli_query($con,"SELECT * FROM ibirakas WHERE TIME_TO_SEC(TIMEDIFF(now(),`added`))>3600 AND status='private'");
      while($row=mysqli_fetch_assoc($query)){
        $change=mysqli_query($con,"UPDATE ibirakas SET status='public'");
      }
      // Change from public to expired
      $query=mysqli_query($con,"SELECT * FROM ibirakas WHERE TIME_TO_SEC(TIMEDIFF(now(),`added`))>86400 AND status='public'");
      while($row=mysqli_fetch_assoc($query)){
        $change=mysqli_query($con,"UPDATE ibirakas SET status='expired'");
      }
      ?>

Thanks

Upvotes: 0

Ren&#233; H&#246;hle
Ren&#233; H&#246;hle

Reputation: 27295

I think you can make it much easier without so much PHP and work more with MySQL functions. There are a lot functions like TIMEDIFF so you could check if the difference from NOW() to the saved date is bigger then 90 minutes. Here is a good question how to get minutes from your diff.

Difference in minutes from two time fields in MySQL

Then you don't need so select and update all your rows. You can directly update all that rows that match your conditions.

Upvotes: 1

Related Questions