0

Hey Guys,

I need some help... I have the following code:

$dueinQ = mysql_query("SELECT * FROM `invoices` WHERE `status` = '10' OR `status` = '11' OR `status` = '9'");
while($dueinR = mysql_fetch_assoc($dueinQ)){

$inv_due = $dueinR['duedate'];
$eofy = $dueinR['eofy'];
$datediff = $inv_due - $eofy;
$days = floor($datediff/(60*60*24));

	if($days > 365){
		//Do nothing
	}else{
		$due = $due + ($dueinR['total'] - $dueinR['total_paid']);			
	}
}
echo $due;

but it doesn't work...

I am trying to work out if the due date of the invoice is in THIS financial year... if it is add it to the total else don't....
but its not working, I can seem to get it to work out if its this financial year (e.g. end of this financial year is 1/4/2012)

Dan

4
Contributors
8
Replies
9
Views
5 Years
Discussion Span
Last Post by niranga
0

Try to get the date difference directly from the query like this

SELECT *, DATEDIFF(duedate,eofy) as date_diff FROM `invoices` WHERE `status` = '10' OR `status` = '11' OR `status` = '9'

Now in the while loop you can get the date difference in days from $dueinR. So you can perform the if else checking at the beginning of the while loop

0

The date diff will give the difference, so I don't know if that will work. Bepending on the format of your dates (should be yyyy-mm-dd), you could just do a simple comparison:

SELECT ... FROM `invoices` WHERE `status` > 8 AND `status` < 12 AND `dateinv` >= '2011-04-01'

Edited by diafol: n/a

0

@ardav : DATEDIFF gives the difference between two dates by number of days. It works when two dates are in the format of 'timestamp' data type(Ex: 2011-11-18 10:09:08). Actually I am not sure about other date formats as you pointed :)

EDIT: I think you are referring to 'duedate' by 'dateinv' in your reply

Edited by niranga: n/a

0

Hello,


Why not have MySQL do all the calculation with the following query?
SELECT sum( total - total_paid) as due FROM `invoices` WHERE `status` in( '9', '10', '11') and DATEDIFF(duedate,eofy) <= 365

The datediff(date1, date2) function returns date1 – date2 expressed as a value in days. BUT date1 and date2 have to be date or date-and-time expressions.

HTH

0

I'm not so keen on this datediff thingy - what about leap years? Changing to 366 won't help. Also you're making a calculation DATEDIFF (slow) where it is not required.

EDIT: I think you are referring to 'duedate' by 'dateinv' in your reply

Is that aimed at me? 'dateinv' in my example is just the invoice date. If the invoice date is since 2011-04-01, include it. Otherwise don't. Simple.

0

@ardav : In @Danny159's first code snippet he was trying to get the date difference between 'duedate' and 'eofy' where both were taken from the database. I am sorry if I understood that incorrectly :)

0

I don't think you did,I just don't understand why the eofy needs to be in every record. Doesn't make sense to me.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.