PHP cron script produces blank page after run

[Farcell]

Hi there, I've setup a custom php cron script which should add records from one table into another, provided the conditions are true. But when I run the cron from schedule, I get a blank page and nothing is recorded. I've added 'error_reporting(E_ALL);' in my script to see what the problem

<?php
// Check to ensure this file is included in Joomla!
defined('_JEXEC') or die();

error_reporting(E_ALL);

$data = $listModel->getData();

$db = FabrikWorker::getDbo();

$TrackingNo = (".$data->customer___id_raw.",".$data->customer___Cargo.");
$Status1 = "We are preparing to ship your cargo and we will update you soon";

$count = (int) $db->loadResult();

if ($count === 0 & $data->customer___Status == "1") {
  $db->setQuery("INSERT INTO Tracking (`tracking___tracking_no`, `tracking___status`, 'tracking___date') VALUES ($TrackingNo, $Status1,(DATE_FORMAT(NOW(),'%d-%M-%Y')");
 
  $db->execute();
}

exit;
?>

I've also referenced the customer table in schedule and pointed to this PHP script file. Any help or guidance here will be much appreciated.

Also, how can I update the "customer___Status" after the record is copied over to "2".

 

[rackem]

Remove "exit;" and try it. I believe the Fabrik code just runs your code and then carries on with whatever it is doing. So the exit command would break that process and give the blank page.

 

[rackem]

For troubleshooting this type of code, I've found it is best to start simple and build up the functionality, rather than write it all first and figure out why it isn't working. Here is some other helpful info: http://fabrikar.com/forums/index.php?wiki/debugging/

For your other question, perhaps you could run UPDATE query at the end, e.g. something like

$db->setQuery("UPDATE customer SET status = 2");
$db->query();

 

[cheesegrits]

As well as the exit at the end, there's all kinds of stuff wrong with that.

First issue is $listModel->getData() returns an array of arrays, not a single row. The result is grouped (so whatever your list is grouped by), and even if it isn't grouped, you still get a single group. So the first returned row's data is going to be in $data[0][0]. To process getData() results, you need to iterate ...

foreach ($data as $group) {
   foreach ($group as $row) {
      // now you can access $row->tablename___elementname
   }
}

Then you are doing a

$count=(int)$db->loadResult();

... on nothing, as you haven't set a query for $db yet.

And there's a syntax error in your main setQuery, missing closing paren. And incorrect quotes (' instead of `) on at least one of the field names (use backticks for `table` and `field` names, single or double quotes for 'data').

And this ...

$TrackingNo=(".$data->customer___id_raw.",".$data->customer___Cargo.");

... is a syntax error. What is format of the string you are trying to build?

If you are trying to do what I think you are trying to do, which is insert new rows into the tracking table for customer rows with status of 1, you don't need to do any of that, you can do the select and insert in a single query, without needing to call getData() ...

// get a db object
$myDb = FabrikWorker::getDbo();

// use the INSERT INTO ... SELECT FROM syntax
// http://dev.mysql.com/doc/refman/5.7/en/insert-select.html
$myDb->setQuery("INSERT INTO `tracking` (`tracking___tracking_no`, `tracking___status`, `tracking___date`) SELECT CONCAT(`id`, `Cargo`), 'We are preparing to ship your cargo and we will update you soon',  DATE_FORMAT(NOW(),'%d-%M-%Y') FROM `customer` WHERE `customer`.`Status` = '1'");
$myDb->execute();

// update status 1 to 2
$myDb->setQuery("UPDATE `customer` SET `Status` = '2' WHERE `Status` = '1'");
$myDb->execute();

You may need to tweak that CONCAT() to build the string how you want it, so for example if it's supposed to be X.Y (separated with a dot) ... CONCAT(`id`, '.', `Cargo`).

-- hugh

 

[Farcell]

Thank you rackem and a special thank you to hugh, it works like a charm. Initial, I got an error message when I used the script above, this script below worked for me when I tweaked it:

<?php
// get a db object
$myDb = FabrikWorker::getDbo();

// use the INSERT INTO ... SELECT FROM syntax
// http://dev.mysql.com/doc/refman/5.7/en/insert-select.html

$myDb->setQuery("INSERT INTO `tracking` (`tracking_no`, `status`, `date`) SELECT CONCAT(`id`,`Cargo`), 'We are preparing to ship your cargo and we will update you soon',  DATE_FORMAT(NOW(),'%d-%M-%Y') FROM `customer` WHERE `customer`.`Status` = '1'");
$myDb->execute();

// update status 1 to 2
$myDb->setQuery("UPDATE `customer` SET `Status` = '2' WHERE `Status` = '1'");
$myDb->execute();

?>

I changed the reference INSERT INTO as the table reference was not needed in tracking no, status or date.

Cheers Mate.

 

[Farcell]

One more thing, how can I set the date format so that it compare today's date if there is match in the shipping date on record?

$myDb->setQuery("INSERT INTO `tracking` (`tracking_no`, `status`, `date`) SELECT CONCAT(`id`,`Cargo`), 'Your cargo has been shipped and we will notify you when it has arrived at the port', (`ship_date`) FROM `customer` WHERE `customer`.`Status` = '2' AND `customer`.`ship_date` = DATE_FORMAT(NOW(),'%d-%M-%Y')");

Thank you in advance.

 

[cheesegrits]

DATE(customer.ship_date) = CURDATE()

Sent from my HTC 10 using Tapatalk

 

[Farcell]

Great, it works

Thanks Hugh