p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Change Date On Fly


Message #1 by "Ken Goff" <macessen@a...> on Fri, 14 Feb 2003 06:57:23
My field, EventDate displays in UNIX style as yyyy-m-d in the following
$result segment:

mysql_result($result, $i, "EventDate")

Can this date be reformatted on the fly to m-d-yyyy?
Message #2 by "Nikolai Devereaux" <yomama@u...> on Thu, 13 Feb 2003 23:46:41 -0800
> My field, EventDate displays in UNIX style as yyyy-m-d in the following
> $result segment:
>
> mysql_result($result, $i, "EventDate")
>
> Can this date be reformatted on the fly to m-d-yyyy?


Yes, you can do this a couple ways.

The first way is to modify your query and specify the date format you'd like
the result returned.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_
and_time_functions

(same thing as: http://tinyurl.com/5tiy)


$query = "SELECT DATE_FORMAT(EventDate, '%c-%e-%Y') FROM table";



Another way also involves modifying your query to return a unix timestamp
instead, and using php's date() function to format it.  I don't see why you'd
want to do this over the first option.

$query = "SELECT UNIX_TIMESTAMP(EventDate) FROM table";

$result = mysql_query($query);

$date_str = date("n-j-Y", mysql_result($result, 0));

http://www.php.net/date


The last (and least efficient, so I won't show you the code) is to perform a
regular expression search and replace on the original query return value.

Look at http://www.php.net/preg_replace for examples.


Take care,

nik

Message #3 by "Ken Goff" <macessen@a...> on Fri, 14 Feb 2003 17:31:19
> 
> My field, EventDate displays in UNIX style as yyyy-m-d in the following
> $result segment:
>
> mysql_result($result, $i, "EventDate")
>
> Can this date be reformatted on the fly to m-d-yyyy?


Yes, you can do this a couple ways.

The first way is to modify your query and specify the date format you'd like
the result returned.

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Date_
and_time_functions

(same thing as: http://tinyurl.com/5tiy)


$query = "SELECT DATE_FORMAT(EventDate, '%c-%e-%Y') FROM table";



Another way also involves modifying your query to return a unix timestamp
instead, and using php's date() function to format it.  I don't see why you'd
want to do this over the first option.

$query = "SELECT UNIX_TIMESTAMP(EventDate) FROM table";

$result = mysql_query($query);

$date_str = date("n-j-Y", mysql_result($result, 0));

http://www.php.net/date


The last (and least efficient, so I won't show you the code) is to perform a
regular expression search and replace on the original query return value.

Look at http://www.php.net/preg_replace for examples.

-------------

Thanks.

It looked as if DATE_FORMAT() would work for me but when implemented, it
changed the ORDER BY from what I wanted in my original query. Perhaps I
should have shown the complete original query, rather than just a segment.

$result = mysql_query("SELECT * FROM EventList WHERE EventDate >
'$neweventdate' ORDER BY 'EventDate','EventOrder' LIMIT 50",$link_id);

Then for display, I have the following:

if($result !="") {
	print "ID -- Event Date -- Event -- Order<BR><BR>";  
	for($i =0; $i < mysql_num_rows($result); $i++) {
	echo mysql_result($result, $i, "EventId")," - - ", mysql_result($result,
$i, "EventDate"), " - - ",
	mysql_result($result, $i, "Event"), " - - ",mysql_result($result, $i,
"EventOrder"),"<BR>";
	}
	exit();
	}

Can I still use DATE_FORMAT() and then somehow modify the ORDER BY section
to produce the desirable result? I think also that WHERE EventDate >
'$neweventdate' got scrambled as well. $neweventdate is "yyyy-m-d".
Message #4 by "Nikolai Devereaux" <yomama@u...> on Fri, 14 Feb 2003 09:42:00 -0800
> Can I still use DATE_FORMAT() and then somehow modify the ORDER BY section
> to produce the desirable result? I think also that WHERE EventDate >
> '$neweventdate' got scrambled as well. $neweventdate is "yyyy-m-d".

Yes.  Keep your sort by EventDate, but alias the reformatted date column:


SELECT DATE_FORMAT(...) as FormattedEventDate, *
  FROM ...
 WHERE ...
 ORDER BY EventDate


Take care,

nik

Message #5 by "Ken Goff" <macessen@a...> on Fri, 14 Feb 2003 21:02:38
> 
> Can I still use DATE_FORMAT() and then somehow modify the ORDER BY section
> to produce the desirable result? I think also that WHERE EventDate >
> '$neweventdate' got scrambled as well. $neweventdate is "yyyy-m-d".

Yes.  Keep your sort by EventDate, but alias the reformatted date column:


SELECT DATE_FORMAT(...) as FormattedEventDate, *
  FROM ...
 WHERE ...
 ORDER BY EventDate

- - - - - - -

That's what I tried but there seems to be a php bug. The original query
produced the following partial result:

7 - - 2003-02-14 - - Staff development (no students) - - 1
48 - - 2003-02-14 - - Valentines Day - - 2
8 - - 2003-02-17 - - Presidents' Day (no school) - - 1
9 - - 2003-03-07 - - Staff development (no students) - - 1
10 - - 2003-03-10 - - Trimester III begins - - 1
11 - - 2003-03-13 - - Evening conferences (no students) - - 1
12 - - 2003-03-14 - - Day conferences (no students) - - 1
13 - - 2003-03-24 - - Spring break (no school) - - 1
14 - - 2003-03-25 - - Spring break (no school) - - 1
15 - - 2003-03-26 - - Spring break (no school) - - 1
16 - - 2003-03-27 - - Spring break (no school) - - 1
17 - - 2003-03-28 - - Spring break (no school) - - 1
47 - - 2003-03-31 - - Classes resume - - 1
18 - - 2003-04-18 - - Staff development (no students) - - 1

After using DATE_FORMAT() and leaving ORDER BY set to EventDate, I get
records sorted by ID (auto incrementing field) instead:

7 - - 2-14-2003 - - Staff development (no students) - - 1
8 - - 2-17-2003 - - Presidents' Day (no school) - - 1
9 - - 3-7-2003 - - Staff development (no students) - - 1
10 - - 3-10-2003 - - Trimester III begins - - 1
11 - - 3-13-2003 - - Evening conferences (no students) - - 1
12 - - 3-14-2003 - - Day conferences (no students) - - 1
13 - - 3-24-2003 - - Spring break (no school) - - 1
14 - - 3-25-2003 - - Spring break (no school) - - 1
15 - - 3-26-2003 - - Spring break (no school) - - 1
16 - - 3-27-2003 - - Spring break (no school) - - 1
17 - - 3-28-2003 - - Spring break (no school) - - 1
18 - - 4-18-2003 - - Staff development (no students) - - 1
-To bottom of list-
45 - - 12-25-2003 - - Christmas - - 1
46 - - 12-26-2003 - - Winter break (no school) - - 1
47 - - 3-31-2003 - - Classes resume - - 1
48 - - 2-14-2003 - - Valentines Day - - 2
Message #6 by "Ken Goff" <macessen@a...> on Sat, 15 Feb 2003 07:15:34
Have I discovered a bug in PHP? The following sorts by EventId, rather than
by EventDate and then by EventOrder:

$result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y')
as FormattedEventDate, Event, EventOrder FROM EventList WHERE EventDate >
'$neweventdate' ORDER BY 'EventDate','EventOrder' LIMIT 50",$link_id);

If I remove the ORDER BY portion, there is absolutely no change in the result.

Can I accomplish my goal any using any other means?
Message #7 by "Ken Goff" <macessen@a...> on Sat, 15 Feb 2003 19:56:57
It does indeed appear that I'm encountering a bug in PHP. I've tried Nik's
helpful second option (UNIX_TIMESTAMP) and end up with the same results as
Date_Format(). Records display sorted on EventId, rather than first by
EventDate and then by EventOrder.

Is there any solution for this problem?

- - - - - - - -
$result = mysql_query("SELECT EventId, UNIX_TIMESTAMP(EventDate), Event,
EventOrder FROM EventList WHERE EventDate >= '$neweventdate' ORDER BY
'EventDate','EventOrder' LIMIT 50",$link_id);
	if($result !="") {
	print "ID -- Event Date -- Event -- Order<BR><BR>";  
	for($i =0; $i < mysql_num_rows($result); $i++) {
	echo mysql_result($result, $i, "EventId")," - - ",
date("n-j-Y",mysql_result($result, $i, 1)), " - - ",
	mysql_result($result, $i, "Event"), " - - ",mysql_result($result,
$i,"EventOrder"),"<BR>";
	}
	exit();
	}
}
- - - - - - - -
ID -- Event Date -- Event -- Order

7 - - 2-14-2003 - - Staff development (no students) - - 1
8 - - 2-17-2003 - - Presidents' Day (no school) - - 1
9 - - 3-7-2003 - - Staff development (no students) - - 1
10 - - 3-10-2003 - - Trimester III begins - - 1
11 - - 3-13-2003 - - Evening conferences (no students) - - 1
12 - - 3-14-2003 - - Day conferences (no students) - - 1
13 - - 3-24-2003 - - Spring break (no school) - - 1
14 - - 3-25-2003 - - Spring break (no school) - - 1
15 - - 3-26-2003 - - Spring break (no school) - - 1
16 - - 3-27-2003 - - Spring break (no school) - - 1
17 - - 3-28-2003 - - Spring break (no school) - - 1
18 - - 4-18-2003 - - Staff development (no students) - - 1
     -To bottom of list-
45 - - 12-25-2003 - - Christmas - - 1
46 - - 12-26-2003 - - Winter break (no school) - - 1
47 - - 3-31-2003 - - Classes resume - - 1
48 - - 2-14-2003 - - Valentines Day - - 2
- - - - - - - -
Message #8 by "Nikolai Devereaux" <yomama@u...> on Mon, 17 Feb 2003 13:01:40 -0800
> It does indeed appear that I'm encountering a bug in PHP. I've tried Nik's
> helpful second option (UNIX_TIMESTAMP) and end up with the same results as
> Date_Format(). Records display sorted on EventId, rather than first by
> EventDate and then by EventOrder.
>
> Is there any solution for this problem?

I seriously doubt that it's a bug in PHP.  If there is a bug, it's probably in
MySQL, since it's what's handling your query, not PHP.

I've written LOTS of queries like the one you're trying and I've never
encountered a problem, on several recent versions of PHP.


> $result = mysql_query("SELECT EventId, UNIX_TIMESTAMP(EventDate), Event,
> EventOrder FROM EventList WHERE EventDate >= '$neweventdate' ORDER BY
> 'EventDate','EventOrder' LIMIT 50",$link_id);

I am surprised that you're not seeing a MySQL error in your query, since
you're not SELECTing the column (EventDate) you're ordering by.

If you read my sample query more closely, you'll see that I select the value
of EventDate twice -- once to DATE_FORMAT() it, and once by itself.

If you want me to try your code, dump your MySQL table and post the contents.
If I get time, I'll create a test table and run your query on it and see what
it is you're missing.


Take care,

Nik

Message #9 by "Ken Goff" <macessen@a...> on Mon, 17 Feb 2003 21:29:23
> Have I discovered a bug in PHP? The following sorts by EventId, rather than
b> y EventDate and then by EventOrder:

> $result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y')
a> s FormattedEventDate, Event, EventOrder FROM EventList WHERE EventDate >
'> $neweventdate' ORDER BY 'EventDate','EventOrder' LIMIT 50",$link_id);

> If I remove the ORDER BY portion, there is absolutely no change in the
result.


The bug or idiosyncracy is with MySQL, not PHP. I discovered this by
performing the query directly within Terminal. Someone at MySQL.com came up
with the solution as follows:

$result = mysql_query("SELECT EventId, DATE_FORMAT(EventDate, '%c-%e-%Y'),
 as FormattedEventDate, EventDate, Event, EventOrder FROM EventList WHERE
EventDate >= $neweventdate' ORDER BY 'EventDate','EventOrder' LIMIT
50",$link_id);

The same overall approach, adding the date field name after the date
modification section, worked for UNIX_TIMESTAMP() as well.

$result = mysql_query("SELECT EventId, UNIX_TIMESTAMP(EventDate),
EventDate, Event, EventOrder FROM EventList WHERE EventDate >
'$neweventdate' ORDER BY 'EventDate','EventOrder' LIMIT 50",$link_id);
Message #10 by "Nikolai Devereaux" <yomama@u...> on Mon, 17 Feb 2003 14:27:30 -0800
Ah ****, I'm sorry Ken --  I knew I was right about your problem, but I wasn't
as "clear" as I thought I was.


nik> If you read my sample query more closely, you'll see that
nik> I select the value of EventDate twice -- once to
nik> DATE_FORMAT() it, and once by itself.


This is true, but not as obvious as it sounds.  My query to you was:

SELECT DATE_FORMAT(...) as FormattedEventDate, *
  FROM ...
 WHERE ...
 ORDER BY EventDate


See, the EventDate column is selected again as part of the * list.


Glad you got it working,

Nik


  Return to Index