On Tuesday, April 16, 2002, at 01:35 PM, Alex wrote:
> I just started with php and am trying to display a query result for a
> date
> field as mm/dd/yyyy instead of mysql's yyyy-mm-dd. I looked through
> some
> books and found the use of DATE_FORMAT to covert the dates. Is there any
> other way to format the date?
>
> Thanks
>
> My script looks like this:
>
> $month_1 = ($date_month_1);
> $day_1 = ($date_day_1);
> $year_1 = ($date_year_1);
> $month_2 = ($date_month_2);
> $day_2 = ($date_day_2);
> $year_2 = ($date_year_2);
>
> $query = "SELECT * FROM tablename where date >=
> '$year_1-month_1-$day_1-'
> AND
> date <= '$year_2-$month_2-$day_2'";
As a recent convert from my own custom date-formatting techniques (can
you believe I was storing my dates in VARCHAR(14) columns?) to the
standards of MySQL's DATE_FORMAT function, I can't really recommend this
approach. Why not just take your variables and format them into a
single string? It'll be much, much simpler once you get used to it
// your date data in variables:
$month_1 = ($date_month_1);
$day_1 = ($date_day_1);
$year_1 = ($date_year_1);
$month_2 = ($date_month_2);
$day_2 = ($date_day_2);
$year_2 = ($date_year_2);
// create a timestamp representing the date data
$timestamp_1 = mktime(0, 0, 0, $month_1, $day_1, $year_1);
$timestamp_2 = mktime(0, 0, 0, $month_2, $day_2, $year_2);
// format the timestamps into the format preferred by MySQL's
// DATE column type
$timestamp_1 = date('Ymd', $timestamp_1);
$timestamp_2 = date('Ymd', $timestamp_2);
// your new and improved, and much cleaner query
$query = "SELECT *
FROM tablename
WHERE date >= '$timestamp_1'
AND date <= '$timestamp_2'
";
It may seem like more work, but it's much neater, and you have these
portable timestamps that can always be reformatted to whatever format
you like for display. (Also, be sure to use either DATE_FORMAT or
UNIX_TIMESTAMP in MySQL queries to pull the data out in a desired
format! Much better than running string functions on the date result
after the fact.)
Erik
----
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
