Page 1 of 1

problem with yesterday query

Posted: Mon Oct 18, 2004 7:15 am
by irealms
I have a query to find pull orders from the previous day but can't seem to get it to work. I have previous month and year queries working fine.

Here it is:

Code: Select all

//day variables
$yesterday = date('d', mktime(0, 0, 0, date("m"),  date("d")-1,  date("Y")));
//query
$yorders = "SELECT * FROM orders WHERE DAY(dateadded)='$yesterday' AND status!='Quote'";
$yordersq = mysql_query($yorders, $db_conn) or die("Query $yordersq Failed".mysql_error());
I keep getting a syntax error in the query though i can't seem to see the problem. It's setup in the same format as month and year queries which are fine.

Posted: Mon Oct 18, 2004 8:34 am
by Weirdan
[mysql_man]DAY[/mysql_man] is only available starting from MySQL 4.1.1.

Posted: Mon Oct 18, 2004 10:53 am
by pickle
For something like this, I usually find the stamp for 0:00:00 and for 23:59:59 and find entries between those two values.

Also, you should generate a timestamp once, and use that when generating the $yesterday variable. Imagine if the the script started at 11:59:59.9 December 31st then changed to January 1st halfway through assigning the $yesterday variable - you'd have quite the messed up stamp. This could happen over any day-change period.

Posted: Mon Oct 18, 2004 2:16 pm
by irealms
i have the required version of MySQL for DAY, it even shows up the right number in the query error. I just need to get it to draw using the DAY, once that works i can make sure it's using the right month and year.

Posted: Tue Oct 19, 2004 4:55 am
by irealms
I have managed it with this code:

Code: Select all

$yesterday = date('Y-m-d', mktime(0, 0, 0, date("m"),  date("d")-1,  date("Y")));
//query 
$yorders = "SELECT * FROM orders WHERE dateadded='$yesterday' AND status!='Quote'";
$yordersq = mysql_query($yorders, $db_conn) or die("Query $yordersq Failed".mysql_error());
One question though, if the day is "1" will it deduct 1 from the month?

Posted: Tue Oct 19, 2004 9:48 am
by pickle
It sure won't. If you cross over months without decrimenting the month, you'll have an invalid stamp. Plus, you have to worry about going January->December. An easier way to do it would be to generate a timestamp and subtract 86400 (#of seconds in a day) from it:

Code: Select all

$yesterday_stamp = time() - 86400;
$yesterday = date('Y-m-d', $yesterday_stamp);
Much less obfuscated (for my brain) as well.

Posted: Wed Oct 20, 2004 6:30 am
by irealms
thanks, seems to work :)

For week i'm using YEARWEEK to find the last week is there something similar for months? as i can't seem to find it in the manual.

Posted: Wed Oct 20, 2004 7:10 am
by timvw
you "could" use YEAR() AND MONTH()



btw, i do the yesterday usually like this:

Code: Select all

$time = strtotime("-1 day");
$query = "SELECT * FROM foo WHERE datefield=FROM_UNIXTIME($time)";