Jump to content

Archived

This topic is now archived and is closed to further replies.

helgatheviking

date format

Recommended Posts

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


Share this post


Link to post
Share on other sites
You said you tried "DATE_FORMAT(sqldate,'%d/%m/%Y')"
Your missing a dollar sign.
DATE_FORMAT($sqldate,'%d/%m/%Y')

Share this post


Link to post
Share on other sites
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]);
}

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.