Pulling records in mysql for exact date

Anon

My problem is this: I'm trying to set up a simple news publishing script. I'd like it to organize news by day, so have 3 days worth posted on the main page at a time:

Day 1
Post 1
Post 2

Day 2
Post 1
Post 2
etc

In order to do this, I need to do a mysql query that grabs records based on the timestamp field I have in the record. I know there are mysql functions that would work for this, but I'm not sure which to use.

The other problem is that when I added news entries I was using PHP's timestamp and from what I've read that's different than mysql's so I need to convert it over.

SELECT * FROM table WHERE entryDate = ?

I've looked at mysql's help on date and time and also php.net's but I'm stil confused as to what stuff does. Does anyone know of a good tutorial or have a way for me to do this? Thanks.

Anon

what i would do is create the table using datetime for the formate of the date row. this way, sql will want the date in this format 0000-00-00 00:00:00. this way you can just use the now() function in a sql query.

it would look like this:

UPDATE table SET entryDate = now() WHERE x = 'x';

then you can easily make a date from by using the function strtotime(). this will return the timestamp and you can then sort the timestamps using a simple rsort or sort function.

now adding and retrieving them are easy. to retrieve them.

$Query = "SELECT * FROM table WHERE entryDate = '$dateincorrectformat'";

then you would read it anyway else. to set it you would do this:

$Query = "UPDATE table SET entryDate = now() WHERE x = 'x'";

Anon

Use the mysql timestamp to date your articles and then if im right you should be able to do a query like this

select * from table where entryDate <= curdate and entryDate >= (curdate less 3)

curdate being the current date and curdate less 3 would juss be a simple process of working out three days previous to curdate... if you know what i mean??? Not usre that I know what I mean.. >🙂

Anon

or even better...

select * from table where entryDate >= curdate less 3..

Anon

The problem is that I already have several records with timestamps I got from php. Is there an easy way to convert them over to the mysql timestamp?

Anon

not that i know of

Anon

same ere...