get results within the last week

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
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

get results within the last week

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

Post 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...
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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()
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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()'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you may need some parentheses around the math bit. :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hm, I just tested the query on mysql 5.0.27-community-nt and it works fine.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post by illmapu »

Hi volka, that solved the problem! Thanks a lot!

Mucho Appreciated! :D
Post Reply