helgatheviking Posted September 30, 2006 Share Posted September 30, 2006 hey all, i got some fantastic help here when i was building the following code and am hoping that now that I need to update it you all can help again. basically i have a sports league, where players are entered into the database after they pay their registration fee via paypal. then i have the following page that lists the roster of each team and the date when each player was registered. everything seems to be working and the date lists just fine, but i would like to format the date into a normal m/day/year instead of the mysql format, and if i could convert it to eastern time that would be great too. i have tried using DATE_FORMAT(sqldate,'%d/%m/%Y') in the SELECT query but then the date stops displaying. so i am sort of stuck. hope this is something simple and i hope you guys can help.-helga// rosters function startTable2($title) { $out = "<h3>$title</h3>" . "<table class=sample width=400>\n" ; return $out;}function getRecords2($sql) { $x = mysql_query($sql) or die(mysql_error()); $records = array(); if (mysql_num_rows($x) > 0) while ($z = mysql_fetch_array($x)) $records[] = $z; return $records;}function drawTable2($sql, $header) { // query $records = getRecords2($sql); $out = startTable2($header); // grab all the content and draw table foreach ($records as $record) { $cols = array('first', 'last', 'sqldate'); $out .= "<tr>\n" . "\t<td class=\"left\">$record[NAME]</td>\n"; foreach ($cols as $col) $out .= "<td>$record[$col]</td>\n"; $out .= "</tr>\n"; } $out .= "</table>\n"; return $out;}// change table and title for each team or add new teams here$tables = array( array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'CHA2'", "<strong>Chaudière 2</strong>"), array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'COR-M'", "<strong>Cortland</strong>"), array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'NE'", "<strong>New England</strong>"), array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'NJ'", "<strong>New Jersey</strong>"), array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'NY'", "<strong>New York</strong>"), array("SELECT `first`, `last`, `sqldate` FROM `0607roster` WHERE `team` = 'WP-M'", "<strong>West Point</strong>")); foreach ($tables as $x) { echo drawTable2($x[0], $x[1]);} Quote Link to comment Share on other sites More sharing options...
ShibSta Posted September 30, 2006 Share Posted September 30, 2006 You said you tried "DATE_FORMAT(sqldate,'%d/%m/%Y')"Your missing a dollar sign.DATE_FORMAT($sqldate,'%d/%m/%Y') Quote Link to comment Share on other sites More sharing options...
helgatheviking Posted September 30, 2006 Author Share Posted September 30, 2006 so i tried adding the $ sign, but the date is still not displaying. function startTable2($title) { $out = "<h3>$title</h3>" . "<table class=sample width=400>\n" ; return $out;}function getRecords2($sql) { $x = mysql_query($sql) or die(mysql_error()); $records = array(); if (mysql_num_rows($x) > 0) while ($z = mysql_fetch_array($x)) $records[] = $z; return $records;}function drawTable2($sql, $header) { // query $records = getRecords2($sql); $out = startTable2($header); // grab all the content and draw table foreach ($records as $record) { $cols = array('first', 'last', $sqldate); $out .= "<tr>\n" . "\t<td class=\"left\">$record[NAME]</td>\n"; foreach ($cols as $col) $out .= "<td>$record[$col]</td>\n"; $out .= "</tr>\n"; } $out .= "</table>\n"; return $out;}// change table and <h3> title for each team or add new teams here$tables = array( array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'CHA2'", "<strong>Chaudière 2</strong>"), array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'COR-M'", "<strong>Cortland</strong>"), array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'NE'", "<strong>New England</strong>"), array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'NJ'", "<strong>New Jersey</strong>"), array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'NY'", "<strong>New York</strong>"), array("SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') FROM `0607roster` WHERE `team` = 'WP-M'", "<strong>West Point</strong>")); foreach ($tables as $x) { echo drawTable2($x[0], $x[1]);} Quote Link to comment Share on other sites More sharing options...
obsidian Posted September 30, 2006 Share Posted September 30, 2006 [quote author=ShibSta link=topic=110071.msg444212#msg444212 date=1159623143]You said you tried "DATE_FORMAT(sqldate,'%d/%m/%Y')"Your missing a dollar sign.DATE_FORMAT($sqldate,'%d/%m/%Y')[/quote]you [b]do not[/b] want a dollar sign since it is a reference to a column in the database, not a variable. you do, however want to name your column:[code]SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') AS myDate FROM `0607roster` WHERE `team` = 'CHA2';[/code]then, when you assign your column names, use the 'myDate' as your third column:[code]<?php$cols = array('first', 'last', 'myDate'); $out .= "<tr>\n<td class=\"left\">$record[NAME]</td>\n";foreach ($cols as $col) $out .= "<td>$record[$col]</td>\n";$out .= "</tr>\n";?>[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.