Question 211792 in PHP Question posted in
Official manual can be found here.

Php – it is about auto execute the query

I have conditional statement if $hours,$minutes,$seconds equals to 0 then I have UPDATE query, it worked, the problem is I will refresh my webpage on exact time so that the query will execute, if I don’t refresh the webpage and the set time from database column even the time is pass the current time, it is not updating, does my code is wrong or should I really need a cron for this?

this code is working but need to refresh the webpage on exact time which days,hours,min,sec, is 0, when I input future time in database, when I see after time passes the current time it will not be updated, should I need a auto execute?

        while($row = mysqli_fetch_assoc($result)){
             $clientid = $row['client_id'];
             $date = $row['date']."".$row['time'];
                          
                           
                          
                            
             date_default_timezone_set('Asia/Manila');
             $start = new DateTime($date);
             $current = new DateTime(date('Y-m-d h:i:s A'));
             $totaldays = $current->diff($start);
             $days = $totaldays->days;
             $minutes = $totaldays->i;
             $seconds = $totaldays->s;
             hours = $totaldays->h;
                          
             if($days === 0 && $hours === 23 && $minutes === 59){
             $sql = "update clientappointment set status = 'Pending' where client_id = '$clientid'";
                                $resultt = mysqli_query($con,$sql);
             }

             if($days === 0 && $hours === 0 && $minutes === 0 && $seconds === 1){
                                    
             $sql = "update clientappointment set status = 'Ongoing' where client_id = '$clientid'";
                                $results = mysqli_query($con,$sql);         
              } 
                              
        }
Tags:

2

Answers


  1. 0

    Use can use sleep function, it can delay the execution by given seconds.

    syntax : sleep(100); // it delay execution by 100 seconds

    reference : https://www.php.net/manual/en/function.sleep.php

    @all you need to do is get time difference by given dates in seconds and place in sleep function.
    like
    sleep($time_diferrence_by_seconds);

    Login or Signup to reply.
  2. 0

    Your code tells me a lot more than your question. It seems you want to change the status column in the database in 2 ways:

    • One day before the appointment the status goes to "Pending".
    • At the start of the appointment the status goes to "Ongoing".

    To achieve this you seem to be looping through all your client appointments every time this code is executed.

    What if I told you you didn’t need to do this? In general it is a bad idea to have to update the database to keep it valid. Normal updating with new data is fine, of course.

    First of all a slight correction to your database. You now have two separate columns to store the date and time of the appointment. That is a bit cumbersome. It is easier when you store both in one column, for instance of the type DATETIME. Let’s call this column "StartDateTime", it being the starting date and time of the appointment. This will make querying the database simpler.

    Now you can construct a status, in your query, from this column:

    SELECT
  client_id,
  CASE
    WHEN NOW() > StartDateTime THEN "Ongoing"
    WHEN DATE_SUB(NOW(), INTERVAL 1 DAY) > StartDateTime THEN "Pending"
    ELSE "????"
  END AS status
FROM
  clientappointment

    See: CASE Statement

    Since I don’t know the status before "Pending" I used question marks.

    If you work this way you don’t need to maintain a "status" column at all, because you can get it from the "StartDateTime" column.

    Obviously you probably need to extend this CASE statement with other statusses. For instance, when an appointment ends.

    When you want to select appointments with a certain status you can simply move the CASE statement to the WHERE part of the query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search