Quick Question about optimization / easier way of approaching a problem.
I have a table of invoices with the fields [companyID, invoiceid,date,amount,status]
I'm looking to find all the differences between two invoices (for the same company) of different dates to see if the amount changed at all.
What I'm currently doing I think could be better optimized or brought down to one or two queries but I'm not certain how to do so or if I'm just searching the wrong things.
Current Code:
$getPreviousInvoices = mysql_query("SELECT `accountid`,`amount` FROM `invoices` WHERE `date` = '$dateFrom'",$db);
while($previousInvoice = mysql_fetch_array($getPreviousInvoices))
{
$accountID = $previousInvoice['accountid'];
$getNewInvoice = mysql_query("SELECT `amount` FROM `invoices` WHERE `date` = '$dateTo' AND `accountid` = '$accountID'",$db);
if($newInvoice = mysql_fetch_array($getNewInvoice))
{
$newAmount = $newInvoice['amount'];
$oldAmount = $previousInvoice['amount'];
if($newAmount != $oldAmount)
{
echo"{$accountID} : {$oldAmount} changed to {$newAmount} \n";
}
}
}
i think you can do it with one self join. you may try following query
SELECT a.`accountid`, a.`amount`, b.`amount` ,a.`date`,b.`date`
FROM `invoices` as a, `invoices` as b
WHERE a.accountid = b.accountid
AND a.`date` = '$dateFrom' AND b.date = '$dateTo'
AND a.`amount` <> b.`amount`