Page 1 of 1

Query doesn't display correctly

Posted: Fri Apr 24, 2009 8:59 am
by tamtam
Hi all,

I've a problem that i can't figure out where it went wrong.

This is the sql statement in my PHP code to filter a report

Code: Select all

$sql = "SELECT * FROM tblname WHERE  (inv_date <= '" . $request->getParam("inv_date2"). "' AND inv_date >= '01-01-2007' )";
and this is the echo from it if i select 24th April 2009 for the date:
SELECT * FROM tblname WHERE (inv_date <= '24/04/2009' AND inv_date >= '01/01/2007' )

I will get all the data between 01/01/2007 and 24/04/2009. Which is perfect.
BUT, if i select the date between 1st to 9th of any month (example 01/01/2007 and 01/01/2008). IT won't show any data. But for all the day from 10th to 31st, all data can be display correctly.
I query in the database and it does have data between the date from 01/01/2007 to 01/01/2008. Anyone have any idea whats wrong with my sql statement?
Thank u very much.

Re: Query doesn't display correctly

Posted: Fri Apr 24, 2009 9:02 am
by jazz090
STOP using actual dates and use timestamps instead and then convert the timestamps to date with the date() function. so u set a coloumn int(10) and insert to it using mysqls UNIX_TIMESTAMP() or PHPs time() function: mysql_query("insert into table (date) values (UNIX_TIMESTAMP())"); OR mysql_query("insert into table (date) values (".time().")"); i prefer the former and when u want to get a results from a specefic time just convert the time u want using mktime() and get timestamp and send it to mysql