Can this be improved?

[newbtophp]

Hi,

 

Im currently using the following code, to list 'Todays 5', however can the following be improved im uneasy with using php for the date comparison - perhaps have it within the sql?

 

DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time()

 

$yesterday = strtotime('-1 day');

$time = time();

$result = mysql_query("SELECT * FROM site_flash WHERE flash_date > '{$yesterday}' AND flash_date < '{$time}' DESC LIMIT 5");

[JonnoTheDev]

DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time()

Why 32 characters? A timestamp is an integer with a length of 10 (INT 10). Convert this.

 

SQL

SELECT * FROM site_flash WHERE flash_date > UNIX_TIMESTAMP(CURDATE() - 1) AND flash_date < UNIX_TIMESTAMP() DESC LIMIT 5

[newbtophp]

DB Structure: I have a column named 'flash_date' (and the type is: varchar(32)) which contains a timestamp generated by time()

Why 32 characters? A timestamp is an integer with a length of 10 (INT 10). Convert this.

 

SQL

SELECT * FROM site_flash WHERE flash_date > UNIX_TIMESTAMP(CURDATE() - 1) AND flash_date < UNIX_TIMESTAMP() DESC LIMIT 5

 

I don't know why I did varchar(32), at that time I was a total newby, and every tutorial I read always seemed to mention varchar(32), (so I just used that, never really knowing what it is) - now I know it means 32 chars 

 

I changed it to INT(10), cheers for that query I'll use it, but just a thought would this work also?

 

SELECT * FROM site_flash WHERE flash_date > (now() - INTERVAL 1 day) DESC LIMIT 5

[JonnoTheDev]

No because NOW() returns the current date & time, not a unix timestamp

[newbtophp]

Ok cheers neil

 

/Solved