problem with yesterday query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

problem with yesterday query

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

[mysql_man]DAY[/mysql_man] is only available starting from MySQL 4.1.1.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)";
Post Reply