• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Problem with planification script / Cron php

kouros91

Member
Hi;
I have a issue with a php cron script which must run everyday to change days between now and a limit date
If date as changed, script update all records of the table "table_adhcont" with the new nbjoursavtecheance value.
My code of script is :
PHP:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('id,DateMod');
$query->from('table_datemod as c');
$db->setQuery($query);
$rows = $db->loadObjectList();
foreach( $rows as $row )
{
    $this_user_datemod = $row->DateMod;
}

$flagmod = 0;
$nowDate = $this_user_datemod;
$thenDate = date("Y-m-d");


if ($nowDate!=$thenDate) {

        $flagmod = 1;
        $IDinput = 1;
        $query = $db->getQuery(true);
        $query
            -> update('table_datemod AS c')
            -> set('datemod ="'.$thenDate.'"')
            -> where('c.id = '. (int) $IDinput);
        $db->setQuery($query);
        $result = $db->execute();     
}
 
switch ($flagmod) {

case 1:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select ('id,idadhsymp,adhesionactive,alertefinadhesion,nbjoursavtecheance,imagealertefinadhesion,datefindernadhesion');
$query->from('table_adhcont2');
$db->setQuery($query);
$rows = $db->loadObjectList();
$i=0;
$db = JFactory::getDbo();
foreach( $rows as $row )
{
    ++$i;
    $this_user_id = $row->id;
    $this_user_idadhsymp = $row->idadhsymp;
    $this_user_adhesionactive = $row->adhesionactive;
    $this_user_alertefinadhesion = $row->alertefinadhesion;
    $this_user_nbjoursavtecheance = $row->nbjoursavtecheance;
    $this_user_imagealertefinadhesion = $row->imagealertefinadhesion;
    $this_user_datefindernadhesion = $row->datefindernadhesion;

  switch($this_user_idadhsymp) {
 
    case 1:
 
      switch($this_user_adhesionactive) {
   
        case 1:
          $this_user_nbjoursavtecheancenew = 'NA';
          $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-blue.png';
          $this_user_alertefinadhesionnew = 1;
          $this_user_adhesionactivenew = 1;   
          break;
        case 2:
          $nowDate = strtotime($this_user_datefindernadhesion);
          $thenDate = strtotime(date("Y-m-d"));
          $dateDiff = - ($thenDate - $nowDate);
          $this_user_nbjoursavtecheancenew =  round($dateDiff / 86400);
          if ($this_user_nbjoursavtecheancenew < 0) {
            if ($this_user_nbjoursavtecheancenew > -45) {
              $this_user_adhesionactivenew = 4;
              $this_user_alertefinadhesionnew = 4;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-red.png';
           
            }
         
            else {
              $this_user_adhesionactivenew = 1;
              $this_user_alertefinadhesionnew = 1;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-blue.png';
            }
          }
          else {
            $this_user_adhesionactivenew = 2;
            $this_user_alertefinadhesionnew = 2;
            $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-orange.png';
          }
          break;
        case 3:
          $nowDate = strtotime($this_user_datefindernadhesion);
          $thenDate = strtotime(date("Y-m-d"));
          $dateDiff = - ($thenDate - $nowDate);
          $this_user_nbjoursavtecheancenew =  round($dateDiff / 86400);

          if ($this_user_nbjoursavtecheancenew < 0) {
            if ($this_user_nbjoursavtecheancenew > -45) {
              $this_user_adhesionactivenew = 4;
              $this_user_alertefinadhesionnew = 4;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-red.png';
           
            }
         
            else {
              $this_user_adhesionactivenew = 1;
              $this_user_alertefinadhesionnew = 1;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-blue.png';
            }
          }
          else {
            $this_user_adhesionactivenew = 3;
            $this_user_alertefinadhesionew = 3;
            $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-green.png';
          }      
          break;
        case 4:
          $nowDate = strtotime($this_user_datefindernadhesion);
          $thenDate = strtotime(date("Y-m-d"));
          $dateDiff = - ($thenDate - $nowDate);
          $this_user_nbjoursavtecheancenew =  round($dateDiff / 86400);
          if ($this_user_nbjoursavtecheancenew < 0) {
            if ($this_user_nbjoursavtecheancenew > -45) {
              $this_user_adhesionactivenew = 4;
              $this_user_alertefinadhesionnew = 4;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-red.png';
           
            }
         
            else {
              $this_user_adhesionactivenew = 1;
              $this_user_alertefinadhesionnew = 1;
              $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-blue.png';
            }
          }
          else {
            $this_user_adhesionactivenew = 4;
            $this_user_alertefinadhesionnew = 4;
            $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-red.png';
          }
          break;
      }
      break;
    default:
      $this_user_nbjoursavtecheancenew = '---';
      $this_user_imagealertefinadhesionnew = '/images/Icones/AdhesionActive/button-violet.png';
      $this_user_adhesionactivenew = 0;
      $this_user_alertefinadhesionnew = 0;
      break;
  }



$IDinput =  '{rowid}';

$query = $db->getQuery(true);
// Fields to update.
$fields = array(
    $db->quoteName('nbjoursavtecheance') . ' = ' . $db->quote($this_user_nbjoursavtecheancenew),
    $db->quoteName('imagealertefinadhesion') . ' = ' . $db->quote($this_user_imagealertefinadhesionnew),
    $db->quoteName('adhesionactive') . ' = ' . $db->quote($this_user_adhesionactivenew),
    $db->quoteName('alertefinadhesion') . ' = ' . $db->quote($this_user_alertefinadhesionnew),
);
$this_user_id = $i;

$conditions = array(
    $db->quoteName('id') . ' = '. $db->quote($this_user_id)
);

$query->update($db->quoteName('table_adhcont2'))->set($fields)->where($conditions);
$db->setQuery($query);
$result = $db->execute();


}

break;
default:
break;
}
The code runs in a php_events of a list or inside an article with sourcerer but i suppose it stops before end of records (12800 records). I don't know how make a compact query which updates all rows with a different value of the field "nbjoursavtecheance" for each row.
I can create an array but i don't know write the query correctly and run this query in cron php script. But i would like it runs automatically every day at 01h00 am.
Perhaps with a temporary table with new values of col of my table and after make an update from ......
It doesn't work with planification cron. Error 500 http

Can you help me ?
Thank you again
Nicolas
 
Last edited:
It's probably just exceeding maximum PHP script execution time.

It looks like it would be possible to do that all in queries, rather than looping through every row in the table and individually setting values. For instance, the first (simplest) case of $this_user_idadhsymp =1 and $this_user_adhesionactive = 1, you could do all those in one query with ...

Code:
// first set the $fields up, then ...
$query->update($db->quoteName('table_adhcont2'))->set($fields)->where('idadhsymp = "1" AND adhesionactive = "1");

... which would then update all of those rows in one query.

And all the other cases could be handled the same way, by doing your date conditions testing in MySQL where clauses, rather than iterating through each row.

The next case, testing datefindernadhesion more than 45 days ...

Code:
'idadhsymp = "1" AND adhesionactive = "2" AND DATE_DIFF(CURDATE(), datefindernadhesion) > 45

Although I'm not clear from your code if you are looking for dates which are more than 45 days in the past, or in the future, so you may need < instead of >.

And so on. For each specific case, just build a where clause that tests for the same conditions, and update all those rows in one query. So you'd end up with 11 queries, where you set up the new values, then update with a where clause that meets those conditions, instead of 28,000 (or whatever) individual queries.

However, I can't really write that code for you as part of subscription support. It'd have to be billable custom work.

-- hugh
 
Is there a possibiliy to increase max execution time php ?
I try to minimise my code but process time for all records is more than php server execution time i think.
 
Last edited:
Yes. You can either ...

Up the limit globally for your server in your php.ini, as per:

http://php.net/manual/en/info.configuration.php#ini.max-execution-time

... or ...

You can try setting it in your code for just that script:

http://php.net/manual/en/function.set-time-limit.php

... but that may error out if your host is preventing runtime overrides on PHP configuration settings.

However ... I would REALLY (really really) recommend you work on doing this as a collection of queries that do the conditions in where clauses, rather than iterating through all rows and executing tens of thousands of queries. With the hints I've given you for two of the case, it really wouldn't be too hard to work out the queries you need to select the various conditions. Play around running some SELECT queries in phpMyAdmin, using the same WHERE clauses. So test it just selecting rows, like ...

Code:
SELECT * FROM table_adhcont2 WHERE idadhsymp = "1" AND adhesionactive = "2" AND DATE_DIFF(CURDATE(), datefindernadhesion) > 45

... and tweak that till you get exactly the rows you expect. Rinse and repeat for each of your set of conditions. Then it's just a simple case of copying and pasting to build the 11 sets of updates, and you'll have a solution which runs in a few seconds, instead of taking many minutes of hammering your database to death, and can easily be run as a cron job.

-- hugh
 
Ok. thank you for these precious advices.I've managed to make my script. It runs perfectly manually (when i push "run button") but it doesn't start automatically.
I have been careful to put last date limit far away from now. All plugin are activated. Is there something i have forgotten ?
 
No you can't cause cron planification is not in the actual version online, just on local for now. When i upload the new version, i tell you, so you can log in .
 
Did you enable the Fabrik Scheduled Task system plugin?

It's triggered by frontend activity and running with the actual frontend access settings. So did you select a list (with access restrictions) in the plugin settings (you don't need to if you don't reference anything in your code).
 
Yes FST is activated. If i undestand what you mean, a user must be connected at the site in front end to trigger the cron plugin ? Just connected, no matter the page or the list ?
I have not selected the list cause the script is for all records of my principal list and doesn 't refer to a fabrik list activity. The usual joomla connector is replaced by One login connector plugin extension (to have one connection only by login).
 
Last edited:
a user must be connected at the site in front end to trigger the cron plugin
Your site must be loaded (yes, no matter which page).
There must be no logged-in user, just some activity on your site. This can also be forced via a server cron job calling your site with wget.

What are your settings (especially the "Log" ones.)
 
Login One is an extension Joomla to have only one connexion per login : https://extensions.joomla.org/extension/login-one.
It overrides connexion native plugin Joomla. Theres plugin authentification and plugin user.
There's no guest connexions in the site. All users must be logged.
Else what are these settings you're talking about ?
Settings are like usual connexion :
upload_2017-1-18_13-39-50.pngupload_2017-1-18_13-40-19.pngupload_2017-1-18_13-40-46.png
 
I 'm going to make a new test with a user connexion in front end. What is the simple way with phpmyadmin to create something which wake up the site at 01h00 am to update data with the cron fabrik script ?
 
Last edited:
Nothing - I've saved my site before installed plugin one login. I 'm going to try without this plugin. It's the same without this plugin.
But when i run cron man,nually i have this in log fabrik :
8,Undefined variable: this_user_alertefinadhesionnew,F:\MAMP\htdocs\Glyco363\plugins\fabrik_cron\php\scripts\script-planification.php,197
and in the back end i have :
Annonce
Message body empty
Message
0 records updated
 
Last edited:
I'm going to read this in an article :
Cron jobs, created using the command line program called crontab, require that your website be hosted on a Unix-type web server, such as Linux or one of the BSDs. Generally, if the web server that your site is on is running Windows, you cannot use this tutorial. Note that I am referring to the computer which your web host uses to place your site, not your own computer.
As my local site for developpment running on windows, i suppose cron doesn't work ..... I must wait to put my site online ?
 
I'm developping locally on Windows, cron is working.

But as you see in your log there seems an issue with your code
It seems $this_user_alertefinadhesionnew used in line 197 of your code is never set.
 
I don't know why. I 'm going to see my code again..... perhaps $this_user_alertefinadhesionnew has no value for one row.
 
I have forgotten a 'n' in my variable .
Now i have in log :
2048,Only variables should be assigned by reference,F:\MAMP\htdocs\Glyco363\plugins\fabrik_cron\php\scripts\script-planification.php,41
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top