Can anyone tell me how to format a drop down list that allows the user to
select a date/time from a list? The game_time_date is a date_time field in
MySQL. The PHP created by Dreamweaver for the select list is to complex for
this novice (attached). I tried using DATE_FORMAT in the recordset to format
the date/time, and also date() in the PhP to format the field; both seem to
break the select list. I want the date/time in the drop down list to display
as "Saturday, Aug 11 1:30 pm". It's stored in MySQL as "2008-08-11 1:30:00".
The form works fine, but the formats aren't user friendly.

Help will be greatly appreciated.

<form id="game_day_form" name="game_day_form" method="post" action="">
<select name="game_date_time_select" id="game_date_time_select">
<option value="1">Please select a day</option>
<?php
do {
?>
<option value="<?php echo
$row_rsGameDateTime['game_date_time']?>"><?php echo
$row_rsGameDateTime['game_date_time']?></option>
<?php
} while ($row_rsGameDateTime = mysql_fetch_assoc($rsGameDateTime));
$rows = mysql_num_rows($rsGameDateTime);
if($rows > 0) {
mysql_data_seek($rsGameDateTime, 0);
$row_rsGameDateTime = mysql_fetch_assoc($rsGameDateTime);
}
?>
</select>
<input type="submit" name="game_day_select2" id="game_day_select2"
value="Select" />
<input name="event_id" type="hidden" id="event_id" value="<?php echo
$row_rsGameDateTime['event_id']; ?>" />
</form>

..oO(BROnstott)

>Can anyone tell me how to format a drop down list that allows the user to
>select a date/time from a list? The game_time_date is a date_time field in
>MySQL. The PHP created by Dreamweaver for the select list is to complex for
>this novice (attached). I tried using DATE_FORMAT in the recordset to format
>the date/time, and also date() in the PhP to format the field; both seem to
>break the select list.

DATE_FORMAT() would be the preferred method. Let's see what you've tried
so far and please give a more detailed error description than just "seem
to break the select list". What happened? What was broken? In what way?

> I want the date/time in the drop down list to display
>as "Saturday, Aug 11 1:30 pm". It's stored in MySQL as "2008-08-11 1:30:00".
>The form works fine, but the formats aren't user friendly.

Sure, and with DATE_FORMAT() you can easily fix it without touching the
PHP code. But you would at least have to post your SQL query to see
where the problem might be.

Micha

Thanks.

Here's the query:

SELECT game_date_time
FROM events
WHERE game_venue_id = game_venue_id_variable AND game_type_id =
game_type_id_variable AND game_date_time > NOW()
ORDER BY game_date_time

game_venue_id_variable and game_type_id_variable are session variables.

If I add DATE_FORMAT to the query like this:

SELECT DATE_FORMAT(game_date_time,'%W %b %e %r')
FROM events
WHERE game_venue_id = game_venue_id_variable AND game_type_id =
game_type_id_variable AND game_date_time > NOW()
ORDER BY game_date_time

I get nothing in the drop down list in the form. No errors that I can see,
just nothing to pick from. I have no clue why. It would seem that it should
work. If I don't put the DATE_FORMAT in there, it works fine.

Ideas?

Thanks in advance.

..oO(BROnstott)

> Here's the query:
>
> SELECT game_date_time
> FROM events
> WHERE game_venue_id = game_venue_id_variable AND game_type_id =
>game_type_id_variable AND game_date_time > NOW()
> ORDER BY game_date_time
>
> game_venue_id_variable and game_type_id_variable are session variables.
>
> If I add DATE_FORMAT to the query like this:
>
> SELECT DATE_FORMAT(game_date_time,'%W %b %e %r')
> FROM events
> WHERE game_venue_id = game_venue_id_variable AND game_type_id =
>game_type_id_variable AND game_date_time > NOW()
> ORDER BY game_date_time
>
> I get nothing in the drop down list in the form. No errors that I can see,
>just nothing to pick from. I have no clue why. It would seem that it should
>work. If I don't put the DATE_FORMAT in there, it works fine.
>
> Ideas?

Yep. If you use any function or another kind of expression in the SELECT
part, you have to give the result a meaningful alias name, e.g.

SELECT DATE_FORMAT(game_date_time, '%W %b %e %r') AS formattedDateTime
FROM events
....

You can replace "formattedDateTime" with whatever name you want, except
for the original column name "game_date_time" - this would cause some
problems with the rest of the query.

Then simply use the chosen name in your PHP script:

<?php echo $row_rsGameDateTime['formattedDateTime']?>

Another note: You should check your local php.ini and make sure that the
error_reporting directive is set to E_ALL (or E_ALL|E_STRICT), and that
display_errors is enabled. Given your second query from above PHP should
have thrown an E_NOTICE error, because your script tried to access an
element in the result set which didn't exist.

HTH
Micha

Thanks a million! Works like a champ!