Undeclared variable While using PHP script to run cron

[Farcell]

Hi there, I am trying to run a Cron script in batches, so that I don't get a headache later, so I decided to load the data in batches of 1000 at a time. I've written this PHP script in a file to be triggered, which will hopefully update my my cargo table:

<?php
defined('_JEXEC') or die('Restricted index access');
$myDb = FabrikWorker::getDbo();
$myDb->setQuery("SELECT * FROM `row` WHERE `country` = 'uk'");
$rows = $myDb->loadObjectList();
foreach($rows as $row) {
$count = $row->cargo;
}

// Update row count for Next Batch of Cron Execution
// Get cargo row count
$counter = $myDb->setQuery("SELECT COUNT(*) AS id FROM `cargo` WHERE `cargo_archive` = '0'");
$num = mysql_fetch_array($counter);
$counted = $num["id"];

//Return row count to 0
if ($counted <= $count){
// Return row ro 0
$myDb->setQuery("UPDATE `row` SET `cargo` = '0' WHERE `country` = 'uk'");
$myDb->execute();
}else{
//Increase row count by 1000 for next batch
$myDb->setQuery("UPDATE `row` SET `cargo` = `cargo`+'1000' WHERE `country` = 'uk'");
$myDb->execute();
}
//Update next batch of 1000 rows
$myDb->setQuery("UPDATE `cargo` SET `cargo_archive` = '1' WHERE `cargo_archive` = (SELECT * FROM `cargo` WHERE `cargo_archive` = '0' LIMIT `$count`,`1000`) AND DATE(cargo.arrival_date) = CURDATE()");
$myDb->execute();

?>

The problem is it gives me an error which is an 'Undeclared variable' in the limit i set when I execute it manually in schedule. I created a separate table to load and updated the row count from the previous cron execution which I get the row count from the previous execution.

Can you tell me how I can get around this problem?

 

[troester]

SELECT * FROM `cargo` WHERE `cargo_archive` = '0' LIMIT `$count`,`1000`

What should this return? You'll need ONE single value of a single row in
WHERE `cargo_archive` = something

`` are column/table delimiters

 

[Farcell]

Hi troester, I've tried it without the `` and it didn't work, so I found a helpful solution online which worked. By using the 'IN' and the table id

$myDb->setQuery("UPDATE `cargo` SET `cargo_archive` = '1' WHERE `id` IN (SELECT * FROM (SELECT `id` FROM `cargo` WHERE `cargo_archive` = '0' LIMIT $count,1000) as t) AND DATE(cargo.arrival_date) = CURDATE()");

Thanks for the fast response.

 

[cheesegrits]

Erm ... unless I'm totally misunderstanding what you are trying to do, that approach doesn't really make much sense.

You'd have to have many millions of rows in your table before it would make sense to try and second guess MySQL's optimization by batching. By using a dependent subquery like that, you are probably putting more strain on MySQL than just doing a straight "UPDATE ... WHERE ..." on the whole table, unless you have tens of millions of rows, and millions of rows matching your WHERE.

As long as the fields in your WHERE clause are indexed, MySQL will easily handle doing a single update like ...

UPDATE  `cargo` SET `cargo_archive` = '1' WHERE `cargo_archive` = '0'  AND DATE(`arrival_date`) = CURDATE()

... on huuuuuuuge tables.

My advice is don't over-think things like this. Do it the absolutely simplest way, and only worry about performance / resource issues if and when you run into them. Assume that MySQL has been built and tuned to handle much larger datasets than you will probably ever use.

Just make sure you have indexes on those fields in your WHERE. If you use them in Fabrik as element filters, they will will already have indexes. If not, create indexes by hand in phpMyAdmin (or whatever).

-- hugh

 

[Farcell]

I read people complaining about performance issues and strains on memory as I have PHP files which use messagebird API to send out SMS notifications. At the moment messagebird is sending the SMS notification through the schedules I set up but my fear is when the customer database grows the execution times will take longer or fail completely which would be a nightmare.

 

[Farcell]

Also, I've been trying to run the crons I setup using easycron by following these instructions - https://www.easycron.com/cron-job-t...p-cron-job-for-fabrik-schedule-system-plug-in

I get a http 200 and Succeeded from easycron, Am I missing something? I read about using wget in your other threads but they do not support them. They said, "as long as you can access your cron job scripts in your browser, there should be no problem for EasyCron to work".

 

[Farcell]

Also, I've been trying to run the crons I setup using easycron by following these instructions - https://www.easycron.com/cron-job-t...p-cron-job-for-fabrik-schedule-system-plug-in

I get a http 200 and Succeeded from easycron, Am I missing something? I read about using wget in your other threads but they do not support them. They said, "as long as you can access your cron job scripts in your browser, there should be no problem for EasyCron to work".

 

[cheesegrits]

How is the sending of SMS related to setting the cargo_archive flag?

-- hugh

 

[cheesegrits]

You only need 'wget' to trigger a page load. Looks like EasyCron allows you to specify the page to load, and it handles that.

-- hugh

 

[Farcell]

Its not, this is another php script have setup to trigger SMS notifications. I will try the simple approach you suggested and deal with the failures as and when they come.

Thanks