Page 1 of 1
get results within the last week
Posted: Tue Jan 09, 2007 10:04 am
by illmapu
Hi,
I am trying to show only results for the last week of entries, done by date.
Code: Select all
$today = date("Y-m-d");
$ltwek = strtotime('-1 week');
$mem = "SELECT * FROM tablename WHERE '_dat' BETWEEN $ltwek AND $today";
with this, I get no results at all when there are results in the database during the week. Can anyone help? Thanks in advance.
Posted: Tue Jan 09, 2007 10:12 am
by timvw
Most SQL Dbms have a DATEDIFF function...
Eg: if you start reading the mysql manual, the section on
date and time functions the first example you get is already extremely similar to what you need...
Posted: Tue Jan 09, 2007 10:12 am
by Burrito
you need single quotes around your field values.
also look at using the built in MySQL functions.
dateadd() I believe....but can't remember for sure.
Posted: Tue Jan 09, 2007 10:24 am
by volka
if _dat is a field (and the query doesn't make much sense if it isn't) use `_dat` instead of '_dat'
' marks a string literal
`marks a field,table,database name
date() returns a string, e.g. 2007-01-09
strtotime() returns a unix timestamp which is number, e.g. 1167754895
So your query looks like
Code: Select all
SELECT * FROM tablename WHERE `_dat` BETWEEN 1167754895 AND '2007-01-09'
This might confuse your sql server as well as the common human reader

Of what type is _dat?
What kind of database do you use? If it is Mysql and you do not need to worry about incompatibilities with other systems you can use
Code: Select all
SELECT * FROM tablename WHERE `_dat` BETWEEN Curdate()-Interval 1 week AND Curdate()
Posted: Tue Jan 09, 2007 10:32 am
by illmapu
Hi everyone, thanks for the help.
Volka, I think you are the closest to what I'm trying to do, but the sql you gave me returns an error still.
_dat is a field in the database, datetime if that helps. It is Mysql.
Check the manual that corresponds to your MySQL server version for the right syntax to use near 'week AND Curdate()'
Posted: Tue Jan 09, 2007 10:41 am
by feyd
you may need some parentheses around the math bit.

Posted: Tue Jan 09, 2007 10:57 am
by volka
hm, I just tested the query on mysql 5.0.27-community-nt and it works fine.
Posted: Tue Jan 09, 2007 11:10 am
by illmapu
hi there, running on mysql 4.0.27-standard, going to see if my host will update it, maybe that is the problem too....
also added single quotes, removed the error, but still getting no results.
Code: Select all
$mem = "SELECT * FROM tablename WHERE `_dat` BETWEEN 'Curdate()-INTERVAL 1 MONTH' AND 'Curdate()'";
Thanks for all your help, I fell dumb that I cannot get this part, never needed it until now so it's new to me.
Posted: Tue Jan 09, 2007 11:42 am
by volka
week seems to be new. Try
Code: Select all
SELECT * FROM tablename WHERE `_dat` BETWEEN Curdate()-Interval 7 day AND Curdate()
instead.
Use single quotes only if you want to mark something as a string literal, like WHERE field='peek-a-boo', not in this query.
Posted: Tue Jan 09, 2007 12:56 pm
by illmapu
Hi volka, that solved the problem! Thanks a lot!
Mucho Appreciated!
