How to update data when the expiration time ends

Question

I have build an apps with Codeigniter and want to make cron job and use MySQL

i have a table 'order' and have field like this

order_id | order_expired_date
001      | 2018-11-12 10:03:33

and i have table 'order_payment' like this,

order_id | op_status
001      | pending

I have many fields in these two tables but only include those that have something to do with this question

i have code from php but not in model codeigniter

$result = mysql_query('UPDATE `'order_payment'`
    SET op_status='expired' 
    WHERE
    (UNIX_TIMESTAMP( now( ) ) - `order_expired_date`));

The question is how to change the status in the order_payment table to expire when the expiration time is up?

Are you using MySQL for your database? Also could you show the code you already have? — Max Voisard, Jul 10 '19 at 04:13
@MaxVoisard i have code but in php not in Model Codeigniter, wait i add to the question — Misdan, Jul 10 '19 at 04:25
Possible duplicate of [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Dharman, Jul 11 '19 at 17:51

score 0 · Answer 1 · answered Jul 10 '19 at 04:35

You may use following code, you need to modify code as per your requirements..

<?php

    $servername = "xyz";
    $username = "xyz";
    $password = "xyz";
    $dbname = "xyz";
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

    $sql = "SELECT *,o.`order_id` as `oid` FROM `order` as o,`order_payment` as op WHERE o.`order_id`=op.`order_id` AND op.`op_status`='paid'";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {

                  $today = date('Y-m-d');
                  $expDt = date('Y-m-d',strtotime($row['order_expired_date']));

                  if($today==$expDt){
                      $updCltSql = "UPDATE `order_payment` SET `op_status`='expired' WHERE `order_id`='".$row['oid']."'";
                      $conn->query($updCltSql);                        
                  }             
        }
    }

    $conn->close();
?>

We always prefer core-php file as cron-job..

Max Voisard · Answer 2 · 2019-07-10T05:52:59.730

After plenty of research, this is what I came up with - hope it helps. In your example, I don't think you need to put single quotes around the table name order_payment, unless that is something unique to CodeIgniter.

$orderID = mysql_query("SELECT order_id FROM order");  // Get order IDs of order table, assuming it has the same list of order IDs as the order_payment table
$order_ids = array();   // Put in array
while ($row = mysql_fetch_array($orderID)) {
   $order_ids[] = $row['order_id'];
}

foreach ($order_ids as $order_id) {   // Iterating through order_id's of array in order to read same index/row number from both tables
  $expirationDate = mysql_query("SELECT order_expired_date FROM order WHERE order_id='$order_id'");
  $expire = date('M j Y g:i A', $expirationDate);   // Converting MySQL timestamp to correct format for strtotime
  $today = strtotime("now");
  if($today >= $expire)   // If past the expiration date
      mysql_query("UPDATE order_payment SET op_status='expired' WHERE order_id='$order_id'");
}

@Dharman I understand that; the only reason I used it was because Misdan used it in his example. — Max Voisard, Jul 12 '19 at 00:32
That is still a poor excuse. Just because someone asked a question with broken code, does not mean you should answer with the same broken code. These functions no longer exist, and your answer is wrong. It would be a valid answer about 7 years, but now it is only broken code. — Dharman, Jul 12 '19 at 01:01

How to update data when the expiration time ends

2 Answers2