Preface - I've looked around a lot for an answer for this (and spoken to a few developer colleagues) but am still scratching my head. I'm sorry if I'm being vague or unhelpful / asking a duplicated question, but I cannot comprehend this
I have a complex SQL query that I wrote in MySQL workbench. There are multiple derived tables, and whenever I run the query inside of MySQL, I get exactly the results I need.
Unfortunately, when I run the same query in PHP, I will randomly get a NULL for a value returned from inside the derived table (Note that the rest of the data is retrieved successfully, but only the derived tables aren't there).
If I'm running the query flawlessly in MySQL Workbench, but PHP is having issues, I think I've isolated the side where the problem is originating, but I don't understand why it's happening or how to resolve the issue.
Here's the Query:
SELECT
Debits.id,
Debits.memo_number,
Supplier1.name as manufacturer,
Supplier2.name as vendor,
CONCAT(Buyer.first_name,' ', Buyer.last_name) as buyer,
CONCAT(Author.first_name, ' ', Author.last_name)as author,
Debits.entry_date,
DebitStatus.name as status,
c.irTotal,
y.mdfTotal,
a.fsTotal,
b.ppTotal,
x.virTotal
FROM Debits
INNER JOIN Supplier as Supplier1
on Supplier1.id = Debits.manufacturer_id
INNER JOIN Supplier as Supplier2
on Supplier2.id = Debits.distributor_id
INNER JOIN Users as Buyer
ON Debits.buyer_id = Buyer.id
INNER JOIN Users as Author
ON Debits.entered_by_id = Author.id
INNER JOIN DebitStatus
ON DebitStatus.id = Debits.status_id
LEFT JOIN
(
SELECT VIR.debit_id, (VIR_Tiers.incentive * VIR_Tiers.claim_qty) as virTotal FROM VIR
INNER JOIN VIR_Tiers
ON VIR_Tiers.vir_id = VIR.id
GROUP BY VIR_Tiers.vir_id
) as x
on Debits.id = x.debit_id
LEFT JOIN
(
SELECT MDF.debit_id, MDF.amt_received as mdfTotal FROM MDF
GROUP BY MDF.debit_id
) as y
on Debits.id = y.debit_id
LEFT JOIN
(
SELECT * FROM Debit_PartNumber
) as z
ON Debits.id = z.debit_id
LEFT JOIN (
SELECT SUM(InstantRebate.discount_amt * Debit_PartNumber.quantity) as irTotal,
InstantRebate.debit_partnumber_id
FROM InstantRebate
INNER JOIN Debit_PartNumber
ON InstantRebate.debit_partnumber_id = Debit_PartNumber.id
GROUP BY Debit_PartNumber.debit_id
) as c
ON z.id = c.debit_partnumber_id
LEFT JOIN
(
SELECT SUM(FreeShipping.amt_per_item * Debit_PartNumber.quantity) as fsTotal,
FreeShipping.debit_partnumber_id
FROM FreeShipping
INNER JOIN Debit_PartNumber
ON FreeShipping.debit_partnumber_id = Debit_PartNumber.id
GROUP BY Debit_PartNumber.debit_id
) as a
ON z.id = a.debit_partnumber_id
LEFT JOIN
(
SELECT SUM((PriceProtection.old_cost - PriceProtection.new_cost) * Debit_PartNumber.quantity) as ppTotal,
PriceProtection.debit_partnumber_id
FROM PriceProtection
INNER JOIN Debit_PartNumber
ON PriceProtection.debit_partnumber_id = Debit_PartNumber.id
) as b
ON z.id = b.debit_partnumber_id
GROUP BY Debits.id
The irTotal is the one result in question. In the database, for the third row, I'm expecting a value of 601, which is returned via MySQL Workbench. The same query when run through PHP is able to generate this same value for 99/100 times, but on the 100th time, I get a NULL.
Again, this is only from the PHP as every time I run it from MySQL work bench, there are no errors. The most peculiar part of this is that I also print out the value of the $row (iterating through with a foreach loop) as that is the result set from the database, but the value remains NULL.
Any ideas?
EDIT: PHP Code
if(mysqli_num_rows($debits)){
$returnArray = [];
foreach($debits as $row){
$insertArray = array(
'id' => $row['id'],
'internal_id' => $row['memo_number'],
'manufacturer' => $row['manufacturer'],
'vendor' => $row['vendor'],
'buyer' => $row['buyer'],
'author' => $row['author'],
'entryDate' => $row['entry_date'],
'status' => $row['status'],
'grandTotal' => $row['irTotal'] + $row['ppTotal'] + $row['fsTotal'] + $row['mdfTotal'] + $row['virTotal']
);
$returnArray[] = $insertArray;
}
return $returnArray;
}