Jump to content


Photo

date format


  • Please log in to reply
3 replies to this topic

#1 helgatheviking

helgatheviking
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 30 September 2006 - 01:23 PM

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]);
}




#2 ShibSta

ShibSta
  • Members
  • PipPipPip
  • Advanced Member
  • 112 posts

Posted 30 September 2006 - 01:32 PM

You said you tried "DATE_FORMAT(sqldate,'%d/%m/%Y')"
Your missing a dollar sign.
DATE_FORMAT($sqldate,'%d/%m/%Y')

#3 helgatheviking

helgatheviking
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 30 September 2006 - 06:30 PM

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]);
}


#4 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 30 September 2006 - 06:36 PM

You said you tried "DATE_FORMAT(sqldate,'%d/%m/%Y')"
Your missing a dollar sign.
DATE_FORMAT($sqldate,'%d/%m/%Y')


you do not 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:
SELECT `first`, `last`, DATE_FORMAT($sqldate,'%d/%m/%Y') AS myDate FROM `0607roster` WHERE `team` = 'CHA2';

then, when you assign your column names, use the 'myDate' as your third column:
<?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";
?>

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users