I've used a MySQL query to get an array of results into PHP. However, the query uses a subquery and can result in the subquery value being shown twice in the array which is incorrect. I spent ages looking at the SQL to see if I could stop this but wasn't successful at all. I now wonder if it is easier to just modify the array to rectify this error.
The array:
array(436) {
[0]=>
array(8) {
["id"]=>
string(2) "75"
["subtotal"]=>
string(8) "168.0000"
["invoice_type"]=>
string(1) "1"
["adjustment"]=>
string(4) "0.00"
["totalcost"]=>
string(8) "168.0000"
}
[1]=>
array(8) {
["id"]=>
string(2) "82"
["subtotal"]=>
string(7) "84.0000"
["invoice_type"]=>
string(1) "1"
["adjustment"]=>
string(4) "0.00"
["totalcost"]=>
string(7) "84.0000"
}
[2]=>
array(8) {
["id"]=>
string(2) "86"
["subtotal"]=>
string(8) "224.0000"
["invoice_type"]=>
string(1) "1"
["adjustment"]=>
string(4) "0.00"
["totalcost"]=>
string(8) "224.0000"
}
[3]=>
array(8) {
["id"]=>
string(2) "95"
["subtotal"]=>
string(7) "70.0000"
["invoice_type"]=>
string(1) "1"
["adjustment"]=>
string(4) "9.00"
["totalcost"]=>
string(7) "70.0000"
}
[4]=>
array(8) {
["id"]=>
string(2) "95"
["subtotal"]=>
string(7) "84.0000"
["invoice_type"]=>
string(1) "2"
["adjustment"]=>
string(4) "9.00"
["totalcost"]=>
string(7) "84.0000"
}
The problem:
In the SQL, the result is grouped by the "invoice_type" then "id". The SQL uses a subquery to get the value for "adjustment". If the result has "invoice_type = 1" AND "invoice_type = 2", both items are added to the array but the adjustment value is also added to both which I don't want as it gets doubled.
Is there a simple way to see if "id" is duplicated and if it is, set one of the "adjustment" values to 0.00?
If you think I should really cover this in the SQL, I'd be happy to add more information regarding that part too.
I've been staring at this for too long now and can't see any other options ;-)
EDIT - The SQL:
SELECT tbl_client.id, tbl_client.first_name, tbl_client.surname, tbl_schedule.invoice_type, sum( duration ) AS totalhours, sum( duration * rate ) AS subtotal, ifnull( adjustment.totaladjustment, 0 ) AS adjustment, (
sum( duration * rate ) + ifnull( adjustment.totaladjustment, 0 )
) AS totalcost
FROM `tbl_schedule`
INNER JOIN tbl_client ON tbl_client.id = tbl_schedule.client
LEFT JOIN (
SELECT client, sum( amount ) AS totaladjustment
FROM tbl_invoice_adjustment
WHERE ( tbl_invoice_adjustment.`date` BETWEEN 1357002061 AND 1359676740 )
GROUP BY client
) adjustment ON adjustment.client = tbl_schedule.client
WHERE tbl_schedule.franchise =1
AND ( STATUS =1 OR `status` =2 OR `status` =4 )
AND `date` BETWEEN 1357002061 AND 1359676740
GROUP BY tbl_schedule.invoice_type, tbl_schedule.client
ORDER BY tbl_client.surname ASC
EDIT - Table structure
tbl_client
id
franchise
title
surname
first_name
tbl_schedule
id
franchise
date
client
invoice_type
duration
rate
status
tbl_invoice_adjustment
id
franchise
client
date
description
amount
The idea is that the query should pull in adjustments for a given time period and combine them with the schedule values. It was all good till I started implementing this adjustment thing. It is 99.7% OK except for the rare case where there are multiple "invoice_type" entries for a given client in a single period.
EDIT: - SQL Fiddle
Here is an SQL Fiddle of the problem.
If you run the statement, you will see that client ID 5 is given the adjustment twice though there is only one adjustment for the client in the database. The main query shows two rows because the grouping is by the invoice_type. Each result row runs the subquery and gets the adjustment.
The fiddle shows the required output but the statement is not fixed in any way.
