FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

time related problem

   Frihost Forum Index -> Scripting -> Php and MySQL
 


inphurno
i have this script that inserts data with a time field with the following format MM/DD/YYY into a mySQL database , instead of a standard timestamp format.

then i try to pull the data back out via PHP with a query like the this, based on a date i get from another form:

SELECT data1, data2 FROM table WHERE record_time > '$date'

i have tried soemthing like this:

$date = date('n/j/Y', (mktime(0, 0, 0, $month, $day, $year)));

which should put my date in a format same as the database. but i'm having no luck in getting it to work, it just returns every record in the table! does anyone know how i should be doing this? i figure i need to tell mySQL to evaluate $date as dates and not just strings, but i don't know how to do this.

Thanks in advance Very Happy
kv
If you want database to do date comparison, you should use database date-time field. And also use the database date/time format (which can be customized I guess). Once you do that, rest of the things should work without any issue.

The other alternative is to use timestamp and an integer field, but while displaying, format it to whatever format you want.

Also just check once if the date you have provided is greater than all dates in the db Smile
rvec
if you can't change the data type I think you'll have to do it in php.

It might be possible with some LIKE and IN statements, but I can't think of a way to make one query work for all dates.
sonam
The second problem is maybe wrong query.
Quote:
SELECT data1, data2 FROM table WHERE record_time > '$date'


This query will select all datas after typed date. If you want select only exact date then you must use = in your query.

Sonam
phucngo
inphurno wrote:
i have this script that inserts data with a time field with the following format MM/DD/YYY into a mySQL database , instead of a standard timestamp format.

then i try to pull the data back out via PHP with a query like the this, based on a date i get from another form:

SELECT data1, data2 FROM table WHERE record_time > '$date'

i have tried soemthing like this:

$date = date('n/j/Y', (mktime(0, 0, 0, $month, $day, $year)));

which should put my date in a format same as the database. but i'm having no luck in getting it to work, it just returns every record in the table! does anyone know how i should be doing this? i figure i need to tell mySQL to evaluate $date as dates and not just strings, but i don't know how to do this.

Thanks in advance Very Happy

To avoid wrong date comparison, you should standardize both $date (PHP variable) and compare condition in SQL statement. The YYYYMMDD format is the best choice.

In PHP:
Code:
$date = sprintf("%04d%02d%02d", $year, $month, $day);


SQL statement:
Code:
SELECT data1, data2 FROM table WHERE DATE_FORMAT(record_time,'%Y%m%d') >= '$date'


Hope it can help you. Smile
inphurno
thanks for all your responses everyone, this helped a lot! i will give it a try and let you know...
Related topics

[New Problem] Opera and absolute Div's
Locking the posts?
RAW Format, a pain...
Merge the 2 computer forums
SIM CITY series
phpbb upload avatar problem
net2ftp
Any help with strange redirection when accessing my site?
Strange problem with my audio output
Wordpress htaccess problem
Can't Delete Files ot Folder
Hacking
Mail clients not working on anything but Vista
Math question - Deal or no deal
Need to add links at the end of a flash video
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.