View Full Version : PHP/mySQL question

Nov 17, 2003, 02:15 PM
i've got a database that inserts records with a time field in the format MM/DD/YYY, instead of a standard timestamp. (i know, i know... but unfortunately i didn't write the original code.)

i'm trying to pull the records back out via PHP with a statement like the following, based on a time i'm setting with an earlier form ($criteria_time):

SELECT a, b FROM table WHERE record_time > '$criteria_time'

and i've been playing with things like:

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

which puts my starting date into a format that matches the one in 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 record_time and criteria_time as dates and not just strings, but i don't know how to do this.

any and all help appreciated.

Nov 17, 2003, 02:51 PM
i can't answer your question, but i just wanted to mention that www.3dbuzz.com has video tutorials for PHP/mySQL. you can download them from that site after you've registered. i've got them on CD (haven't watched them yet) but all their VTM's are really good stuff.

Nov 18, 2003, 08:39 AM
thanks for the tip!

in case anyone is curious, the answer to my problem is to use a SUBSTRING(string, pos, len) in my query like so:

(SUBSTRING(record_date, 1, 2) >= '$startmonth')
AND (SUBSTRING(record_date, 4, 2) >= '$startday') AND(SUBSTRING(record_date, 7, 4) >= '$startyear')

which parses each bit of the date string separately.

Nov 18, 2003, 01:50 PM
Yup, I do something similar on my website (http://organicallydigital.com) to convert a timestamp to the "month, year" in the archives section in the left-hand column.