Selecting different time periods from SQL Query

[defroster]

Hello,

 

I have the following query:

$sql = "Select photos.*, categories.cat FROM photos, categories
WHERE photos.cat_id = categories.id AND photos.status ='1'	ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";
$result = mysql_query($sql);

 

The photos table contains one column called "dateposted' which is in the following format (2010-08-03 00:15:00).

 

How do I from this query select different timeframes by using "hours" something like this I am guessing??:

 

Day
WHERE dateposted=time(now)-24(hours)
Week
WHERE dateposted=time(now)-168(hours)
Month
WHERE dateposted=time(now)-744(hours)
Year
WHERE dateposted=time(now)-8760(hours)

 

Thanks so much. /df

[bh]

Hi,

 

DATE_SUB(dateposted, INTERVAL 168 HOUR);

[defroster]

Thanks so much, but now I am stuck again. How do I do two 'AND' in a SQL Query.. Sorry I am not too good at this. Still learning.

 

$sql = "Select photos.*, categories.cat FROM photos, categories
WHERE photos.cat_id = categories.id AND photos.status ='1' AND DATE_SUB(videos.dateposted, INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";
$result = mysql_query($sql);

 

I have tried this but it still selects all videos instead of the past 24 hours.. Thanks!

[bh]

$sql = "Select photos.*, categories.cat FROM photos, categories
WHERE photos.cat_id = categories.id AND photos.status ='1' AND videos.dateposted >= DATE_SUB(videos.dateposted, INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";
$result = mysql_query($sql);

 

Anyway, i cant see "videos" in your query. And date sub only return a date -> date = videos.dateposted - 24 hour

[defroster]

Thanks so much. But now instead the return is NULL even though I have a post that has a  dateposted within 24 hours Any ideas what could be wrong?

 

This is exactly what the query looks like now.

$sql = "Select videos.*, categories.cat FROM videos, categories
WHERE videos.cat_id = categories.id AND videos.status ='1' AND videos.dateposted >= DATE_SUB(videos.dateposted, INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";
$result = mysql_query($sql);

[defroster]

Sorry, wrong the return is not NULL, it displays all videos instead. But not only the ones the past 24 hours which I want to display?? Hmm.. Thanks so much for help. Any ideas what I am doing wrong?

 

Current code:

$sql = "Select videos.*, categories.cat FROM videos, categories
WHERE videos.cat_id = categories.id AND videos.status ='1' AND videos.dateposted >= DATE_SUB(videos.dateposted, INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit";
$result = mysql_query($sql);

[fenway]

You're comparing a field value to itself?!?!

[bh]

You're comparing a field value to itself?!?!

 

Yeah, its my fault Of course it should be "NOW()"

 

Select videos.*, categories.cat FROM videos, categories
WHERE videos.cat_id = categories.id AND videos.status ='1' AND videos.dateposted >= DATE_SUB(NOW(), INTERVAL 24 HOUR) ORDER BY (videos.up-videos.down) DESC
LIMIT $start, $limit;

[fenway]

So solved?  If so, please mark as such.

[defroster]

Yes, solved. Thanks!