i can't get the value inside my database for a specific date
sample values:
$from= 12/15/2012 $to= 12/15/2012
$from= 12/16/2012 $to= 12/16/2012
$from= 12/17/2012 $to= 12/17/2012
php script:
$query=" select * from call where (date_time BETWEEN '$from' AND '$to') ORDER BY date_time DESC";
the value on my table which i'm getting my dates from has time in it
ex values on my data base:
12/15/2012 2:51
12/15/2012 2:21
12/15/2012 2:55
12/15/2012 3:12
12/16/2012 2:51
12/16/2012 2:21
12/17/2012 2:55
12/17/2012 3:12
when i search for from 12/15/2012 to 12/15/2012 it does not display the values for
12/15/2012
but if i search for
from 12/15/2012 to 12/16/2012
it displays not only the values for 12/15/2012 but also for 12/16/2012
what should i do so that i can enclose all the values only for that specific date?
First of all, you should store your time values in a DATETIME (or possibly TIMESTAMP) column. Then follow the answers given to this question.
However, if you really insist on doing this with strings, you could always use a LIKE comparison:
SELECT * FROM `call` WHERE date_time LIKE '12/15/2012 %'
Of course, this only works for finding all records for a single day (or, with appropriate modification, for a single hour / minute / month / year / etc.). If you need more complicated selection criteria, I'd strongly suggest switching to a real DATETIME column.
Assuming you are storing dates as datetime field, you should convert data to Date type.
When you write:
$from= 12/15/2012
PHP do a division: 12 / 15 and then ( 12 / 15 ) / 2012
An approach will be declare $from as string and cast to date in query:
$from= '12/15/2012'
...
"select * from call where (date_time BETWEEN STR_TO_DATE('$from', '%m/%d/%Y') ..."
The right query for a index friendly plan is:
select *
from `call`
where date_time >= STR_TO_DATE('$from', '%m/%d/%Y') and
date_time < date_add( STR_TO_DATE('$to', '%m/%d/%Y'), interval 1 day)