date_format question

[bpgillett]

hi. i store dates in a table called concert_calendar. i'm trying to use the sql function, format_date and format_time to retrieve the fields formatted for display. the date and time are correctly formatted, but the date and time from the first row returned is displayed for all records. i'm having trouble solving this. do i need a foreach statement somehow? thanks for your help

--brian

//query table using sql date_format and time_format to get dates and times in correct fromat for display
$result_date = mysql_query("SELECT date_format(date,'%M %e %Y') AS date from concert_calendar");
$myrow_date = mysql_fetch_array($result_date);
$result_time = mysql_query("SELECT time_format(time,'%l:%i %p') AS time from concert_calendar");
$myrow_time = mysql_fetch_array($result_time);

//now get all the other fields from the table and order them by date
$result = mysql_query("SELECT * FROM concert_calendar ORDER BY date",$db);

//if there are records, then diplay them in an html table
if ($myrow = mysql_fetch_array($result)) {
  echo "<table border=1 bordercolor=\"333333\" cellspacing=\"2\" cellpadding=\"2\">\n";

  do {

printf("<tr bgcolor=\"cccccc\"><td><font color=\"#003399\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><strong>Date/Time</strong></font>\n</td>");
printf("<td><font color=\"#003399\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><strong>Location</strong></font>\n</td>");
printf("<td><font color=\"#003399\" size=\"3\" face=\"Arial, Helvetica, sans-serif\"><strong>Telephone</strong></td></tr></font>\n");


printf("<tr><td><font color=\"#003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s %s</td>\n", $myrow_date["date"], $myrow_time["time"]);
printf("<td><a href=\"#\" onclick=\"window.open('http://%s', '','width=500,height=500');\"><font size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></a>  ", $myrow["location_url"], $myrow["location"]);
printf("<a href=\"#\" onclick=\"window.open('http://www.mapquest.com/maps/map.adp?adress=%s&city=%s&state=%s&zipcode=%s&country=US&cid=lfmaplink', '','width=500,height=500');\"><font font color=\"#993300\"size=\"2\" face=\"Arial, Helvetica, sans-serif\">Map</font></a>", $myrow["street"], $myrow["city"], $myrow["state"], $myrow["zip"]);

printf("<font color=\"#003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><br>%s<br>%s, %s   %s</font>", $myrow["street"], $myrow["city"], $myrow["state"], $myrow["zip"]);
printf("<font color=\"#003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><br>%s</font></td></font>\n", $myrow["other"]);
printf("<td><font color=\"#003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td></tr></font>\n", $myrow["phone"]);
printf("<tr><td colspan=\"3\"><font color=\"#003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</td><td></tr></font>\n", $myrow["description"]);

  } while ($myrow = mysql_fetch_array($result));

echo "</table>\n";

} else {

//if no records, display error message
	echo "Sorry, no records were found!";	

}

[AstroTeg]

Then problem in your code is pertaining to the logic you've coded in it. Syntax-wise its doing exactly what you asked it.

Check out the first couple lines. You're querying for the dates and then a second query for the times. This doesn't make a lot of sense in the grand picture because they're not related to the data you later pull in.

What you need to do is:

Combine the first two queries into your third query. So the first two need to go in here: SELECT * FROM concert_calendar ORDER BY date

Using * is a little unhealthy. Its fast and quick for you to type, but for this to work, I think you'll need to enter in all the fields. You'll end up with a query like this:

SELECT date_format(date,'%M %e %Y') AS date, time_format(time,'%l:%i %p') AS time, [additional fields go here] FROM concert_calendar ORDER BY date

The trick will be the ORDER BY date. See, date will be formatted and MySQL will do the order by by the formatted string. In same cases this is good. In others this might not be desirable. Your mileage will vary.

[bpgillett]

thanks for the explanation. it now works. i just added a separate date field in the table that i query unformatted to get the ORDER BY to work.

--brian

[AstroTeg]

Originally posted by bpgillett
i just added a separate date field in the table that i query unformatted to get the ORDER BY to work.

Wait wait wait. You added an additional field to the table? So, you now have 2 fields representing one date? If this is so, you DO NOT want to do this. Think about it this way: everything that touches that table will have to take those two fields into account. What if there's a bug and one field gets updated and not the other? Also, you're storing reduntant data in a database and that isn't healthy for you, someone else, or the database.

Just have 1 date field (you could even use it to store the time as well). Then use aliasing to format the date field into something useable for your output and use the original field value to order on. Like this:

SELECT SELECT date_format(date,'%M %e %Y') AS dateformatted FROM mytable ORDER BY date

Notice how I use [date] to order on and to specify the field to get the date on, but notice I have "AS dateformatted." You can name this whatever you like. Then you can specify it in your code and it'll be the formatted date.

[bpgillett]

wow. thanks again. i have several books, but i'm pretty new to this. thanks for the explanation...everything works, and i learned conceptually.

--brian