I am writing a report on data based on new customers for each given month, starting from the earliest month an order was present all the way to the last month that any orders are present (I realize the current code will stop on any month that doesn't have new customers, and will fix later...)
Some background-- I'm using the Flourish Framework (www.flourishlib.com).. The first month/year is set correctly because I have error log'd it out. The first month with orders is 4/2013.
The problem is that for some reason, MySQL randomly returns an empty result at some point that is completely random. I have run the query for that month/year that it returns an empty result for in a MySQL client and it is not an empty result. The script itself proves this to be the case as where it returns the empty result is random and it will go further than it did before sometimes showing the correct information.
I have tried sleeping in between queries, as I originally thought maybe it was throttling or something, no go. Still the same exact behavior. I have tried using retries (when it encounters a count of 0 it will retry up to X times) and EVERY TIME it's empty, which means it cannot be one of those "sometimes it craps out, try again" type of scenarios.
Here is the code as it is now:
function newClients($month, $year) {
$db = fORMDatabase::retrieve();
$noobs = $db->query("
SELECT
id,
email,
(
SELECT completed
FROM orders
WHERE client_id = clients.id
ORDER BY completed ASC
LIMIT 1
) as first_order
FROM clients
HAVING first_order IS NOT NULL
AND MONTH(first_order) = '$month'
AND YEAR(first_order) = '$year'
AND email NOT LIKE '*@********.com'
AND email NOT LIKE '%@********.com'
AND email NOT LIKE '%@********.com'
AND email NOT LIKE '%@********.com'
AND email NOT LIKE '%@********.com'
AND email NOT LIKE '%@********.org'
AND email != '********@gmail.com'
AND email != '********@********.net'
")->fetchAllRows();
return $noobs;
}
$currentMonth = $theFirst['month'];
$currentYear = $theFirst['year'];
$retries = 0;
$noobs = newClients($currentMonth, $currentYear);
while (count($noobs) > 0 || $retries < 3) {
if (count($noobs) == 0) {
error_log('retry #' . ($retries + 1) . '...');
$retries++;
$noobs = newClients($currentMonth, $currentYear);
error_log('count: ' . count($noobs));
sleep(5);
continue;
}
error_log("loop $currentMonth / $currentYear: " . count($noobs));
if ($currentMonth >= 12) {
$currentYear++;
$currentMonth = 1;
} else {
$currentMonth++;
}
sleep(1);
$noobs = newClients($currentMonth, $currentYear);
error_log('count: ' . count($noobs));
}
Couple additional things.. I censored the email addresses for obvious reasons, and I did look at the actual returned data in the MySQL client, it is correct, and I also did vardump the actual array returned and it is indeed empty. (in case you're wondering that maybe count is counting incorrectly or who knows.. I thought maybe it was a countable object/non-array issue or quirk or something)
There may be some confusion as to the retries etc. as that has nothing to do with my desired outcome and were only attempts to solve the issue, here is the original code:
$noobs = newClients($currentMonth, $currentYear);
while (count($noobs) > 0) {
error_log("loop $currentMonth / $currentYear: " . count($noobs));
if ($currentMonth >= 12) {
$currentYear++;
$currentMonth = 1;
} else {
$currentMonth++;
}
$noobs = newClients($currentMonth, $currentYear);
error_log('count: ' . count($noobs));
}
