SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Date Function

  1. #1
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date Function

    I'm attempting to change mySQL's interpretion of the date from yyyy/mm/dd to dd/mm/yyyy. I found the DATE_FORMAT function for PHP, but I can't figure out the SQL syntax, the one I'll post below makes the date vanish, but the rest of the content is there.

    Code:
    $query="SELECT DATE_FORMAT(`Date`, '%d-%m-%Y'),`Title`, `Content` 
    			FROM `CurrentNews` 
    			ORDER BY `Date`";
    Any suggestions?
    Ryan Butler

    Midwest Web Design

  2. #2
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rbutler
    Any suggestions?
    Try using an alias.
    PHP Code:
    $query="SELECT DATE_FORMAT(`Date`, '%d-%m-%Y') as `formatteddate`
                 , `Title`
                 , `Content`
              FROM `CurrentNews`
          ORDER BY `Date`"

    -Helge

  3. #3
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to call your shiny new formatted date something

    Code:
     $query="SELECT DATE_FORMAT(`DateField`, '%d-%m-%Y') AS 'NewDate'
     			FROM `CurrentNews` 
     			ORDER BY `Date`";

    By the way, the DATE_Format command is part of MySQL not PHP. The PHP date functions are different again

    >>edit>> HElge you beat me to it

  4. #4
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, creating an alias didn't work. I have this:
    Code:
    $query="SELECT DATE_FORMAT(Date, '%d-%m-%Y') AS 'NewDate', `Title`, `Content` 
    			FROM `CurrentNews` 
    			ORDER BY `Date`";
    Everything but the date shows.
    Ryan Butler

    Midwest Web Design

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you try my code?
    Also you need to access the alias (formatteddate) and not the column name (date) when echo'ing out the rows.

    -Helge

  6. #6
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I tried you code, just substituted the alias for something I liked better. My code looks like this:
    Code:
    <?php 
    //--  Declare connection to the database 
    $db=mysql_connect ("localhost", "butler21_rbutler", "password") or die ('I cannot connect to the database because: ' . mysql_error()); 
    //-- Select Database 
    mysql_select_db ("butler21_news"); 
    //--  Declare variable to pull data from table 
    $query="SELECT DATE_FORMAT(Date, '%d-%m-%Y') AS 'NewDate', `Title`, `Content` 
    			FROM `CurrentNews` 
    			ORDER BY `Date`"; 
    //--  Run the query on the DB 
    $result = mysql_query($query); 
    //--  Loop through the result array and display results 
    while($row = mysql_fetch_array($result)) 
    { 
    $date=$row['Date'];
    $title=$row['Title'];
    $content=$row['Content'];
    //--  Display the results of the array
    echo "<p>", $NewDate, "</p>", "\n" ,"<p>", $title, "</p>", "\n", "<p>", $content, "</p>";
    } 
    ?>
    I'm now accessing the NewDate in the echo'ing of rows.
    Ryan Butler

    Midwest Web Design

  7. #7
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your while loop your are getting the Date row (This line: $date=$row['Date']; ).
    That should be: $NewDate=$row['NewDate'];

    Also you have single quotes around your alias (in the query). You should remove them or change to backticks. I used backticks in my code.

    -Helge

  8. #8
    SitePoint Wizard rbutler's Avatar
    Join Date
    Jul 2003
    Location
    Springfield, MO
    Posts
    1,867
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Helge, you rock! Can I hire you as my teacher for PHP, lol. Thanks again!

    By the way, what is the deal with backticks in php queries?
    Ryan Butler

    Midwest Web Design

  9. #9
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe...
    If you ask the questions here you'll have a lot of teachers.

    -Helge


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •