[SOLVED] Adding TIME parameter to SELECT statement

[RyanSF07]

I'm inserting content into database with:

 

$sql = "INSERT INTO video VALUES ('0', '$_SESSION[id]', '$_POST[title]', '$_POST[video]', '$_POST[description_text]', '$_POST[category_text]', '$_POST[level_text]', NOW())";

 

and selecting it with:

$sql = "SELECT video.id, title, description_text, category_text, level_text, user_name, 
DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users WHERE video.user_id = registered_users.id ORDER BY id DESC";

 

This works!

 

My question is this, "What can I add to the select statement to select * in the database EXCEPT for content added within the past 24 hours?"

 

Thank you very much for your help 

Ryan

 

 

[sw0o0sh]

Here's what I do.

 

$guests = mysql_num_rows(mysql_query("SELECT * FROM c_guests WHERE date > ' " . date("Y-m-d H:i:s", time()-60) . " ' "));

 

Thats what I do to return unique guests that have appeared within the past 60 seconds from the database. Modify as needed.

 

Not 100% clear of what you are asking, but this is how I call time related things..

[RyanSF07]

Thanks!

 

I played around with that but couldn't get it to work.

 

What I'm going for is, basically:

 

Select everything in the database that has been added from the beginning of time to one day ago.

 

(I don't want content that has been added to the database withing the last 24 hours to be included in the SELECT request.)

 

Thanks very much for your help 

 

Ryan

[obsidian]

Try this in your query:

SELECT video.id, title, description_text, category_text, level_text, user_name, 
DATE_FORMAT(date, '%M %D, %Y') as fDate 
FROM video, registered_users 
WHERE video.user_id = registered_users.id 
AND date <= CURDATE() - INTERVAL 1 DAY
ORDER BY id DESC

 

Notice that I've renamed your formatted date to "fDate" to keep the column from being confused in the CURDATE() comparison. Hope this helps!

 

Actually, using NOW() and HOUR intervals may be more exact for your needs:

SELECT video.id, title, description_text, category_text, level_text, user_name, 
DATE_FORMAT(date, '%M %D, %Y') as fDate 
FROM video, registered_users 
WHERE video.user_id = registered_users.id 
AND date <= NOW() - INTERVAL 24 HOUR
ORDER BY id DESC

[RyanSF07]

Obsidian, looks like that worked.

 

Thank you very much!

 

Ryan

[sw0o0sh]

Oh, Ryan. Just to as a question if you bother to check this topic again, you didnt leave the mysql_num_rows() around the query i gave you did you? Lol