PHP Cron update based on condition

[joshblevins]

I am trying to run a php script to update a field based on if a date is x amount of days away or past the current date.

PHP SETUP SCRIPT

$id= {additional_certifications___id}
$status = {additional_certifications___status};
$expdate = {additional_certifications___expiration_date};

PHP SCRIPT

if $expdate < strtotime( '120 days' ) ) {
$sql = "UPDATE additional_certifications SET status='2' WHERE id=$id";
$result = query($sql);

}
elseif $expdate > NOW{
$sql = "UPDATE additional_certifications SET status='3' WHERE id=$id";
$result = query($sql);
}

 

[cheesegrits]

I think every single line of that is wrong. Except the }.

If you'd like to take out a support sub, I'd be happy to help you write your code.

-- hugh

 

[joshblevins]

Done signed up for subscription

 

[cheesegrits]

Looks like we may have an issue with our subs, as I don't see "Standard" by your name. I'll take a look.

-- hugh

 

[joshblevins]

Looks like it is still saying pending on my side. When I try to pay for it it tell me invoice already paid.

 

[cheesegrits]

Can you check your paypal? We show the payment as "pending".

-- hugh

 

[joshblevins]

Looks like it went though now

 

[cheesegrits]

Yes indeed, I now see "Standard" by your name.

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

// loop through the data array, which is split into groups
foreach ($data as $group) {
   // loop through each group, which is split into rows
   foreach ($group as $row) {
      // for each row, grab the expiration date and calculate the difference in days between it and  "now"
      $status = '';
      $expDate = new DateTime($row->additional_certifications___expiration_date_raw);
      $now = new DateTime();
      $interval = $expDate->diff($now);
      $days = $interval->format('%R%a');

      // set the status accordingly
      if ($days > 0) {
         $status = '3';
      }
      else if ($days > -120) {
         $status = '2';
      }

      // if a status has been set, update it
      if (!empty($status)) {
         $myQuery->clear()
            ->update('additional_certifications')
            ->set('status = ' . $myDb->quote($status))
            ->where('id = ' . $myDb->quote($row->__pk_val));
         $myDb->setQuery($myQuery);
         $myDb->execute();
      }
   }
}

Note that you should probably do this on a copy of your list, which has a prefilter which only selects rows with an expiration date 120 days or less in the future, to minimize the amount of data being selected and passed to you cron job. So say you had 1000 rows in your table, if you don't pre-filter them, the cron job is going to try and select all 1000 rows.

So a prefilter of something like

Field: expiration_date
Condition: less than
Value: NOW() + INTERVAL 120 DAY
Type: no quotes

... should restrict the selection to expiration dates less than 120 days in the future.

-- hugh

 

[cheesegrits]

Better yet, add a second pre-filter for ...

AND
Field: status
Condition: not equal
Value: 3
Type: text

If a row is already status 3 (expiration date has passed), that's never going to change (even after the heat death of the universe), so no point selecting and processing those rows.

-- hugh

 

[cheesegrits]

(Well, unless expiration dates change, which I'm assuming they don't)

 

[joshblevins]

When I run this script I get

Fatal error: Call to a member function clear() on a non-object in /home/miscott21/public_html/portal/plugins/fabrik_cron/php/php.php(75) : eval()'d code on line 22

 

[cheesegrits]

Are you positive you copied and pasted the whole thing, including the first two lines before the foreach(), where $myQuery gets set?

-- hugh