SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: date format

  1. #1
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    date format

    I am trying to output a list of dates from mysql database into a table. I have created a variable $date which outputs in the format yyyy-mm-dd as per the default mysql format. How can I change this to display the list of dates in the format day (eg monday), date, month, year.

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You can use the date_format() command to retrieve the date from the database in whatever format you want.

    See http://dev.mysql.com/doc/refman/5.0/...on_date-format
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, but I must be missing something somewhere because i still cannot get it to work. All I get are Mysql error messages.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    post the queries and the error messages. we're here to help, but only if you give us enough information to form an answer.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a mysql database called 'woodhouse' which contains a table called 'bhsbedsbooked'. This table has a column called 'date' which contains a list of dates. I want to display these dates as part of a table in a php page in the format 'day, date, month, year'.

    In mysql I have entered using command prompt

    mysql> select date_format('%W %D %M %Y'),
    -> from woodhouse.bhsbedsbooked.date;

    This gives error 1064 (42000)

    How should I be using the date_format command to get this to work?

    The php I have to create the table is as follows and this will output the date in the format YYYY-MM-DD no problem.

    <table width="405" border="1" cellpadding="1" align="center">
    <tr><th width="49%">Date booked </th><th width="50%">Number of beds available</th></tr>

    <?php
    //display the list
    $beds = @mysql_query('select date, fambedsbooked from bhsbedsbooked order by date');

    $date =

    while ($bed = mysql_fetch_array($beds)) {
    echo "<tr valign='top'>\n";
    $date = $bed['date'];
    $familybeds = $bed['fambedsbooked'];
    $bedsfree = 6 - $familybeds;

    echo "<td align='center'>$date</td>\n";
    echo "<td align='center'>$bedsfree</td>\n";
    echo "</tr>\n";

    }



    ?>
    </table>

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    select date_format(`date`,'&#37;W %D %M %Y') from woodhouse.bhsbedsbooked
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  7. #7
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still having problems with this.

    This works in mysql:

    mysql> select date_format(date, '%D %W %M %Y'),
    ->fambedsbooked from woodhouse.bhsbedsbooked
    ->order by date;


    This does not work on my php page:

    <?php
    //display the list
    $beds = @mysql_query('select date_format("date",'%W %D %M %Y'), fambedsbooked from woodhouse.bhsbedsbooked order by date');
    if (!$beds) {
    exit('<p>error performimg query ' . mysql_error() .
    '</p>');
    }


    while ($bed = mysql_fetch_array($beds)) {
    echo "<tr valign='top'>\n";
    $date = $bed['date'];
    $familybeds = $bed['fambedsbooked'];
    $bedsfree = 6 - $familybeds;

    echo "<td align='center'>$date</td>\n";
    echo "<td align='center'>$bedsfree</td>\n";
    echo "</tr>\n";

    }



    ?>

    This returns this error message

    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING on line 130

    What am I doing wrong?

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    change this:
    Code:
    $beds = @mysql_query('select date_format("date",'&#37;W %D %M %Y'), fambedsbooked from woodhouse.bhsbedsbooked order by date');
    to this:
    Code:
    $beds = @mysql_query("select date_format(`date`,'%W %D %M %Y'), fambedsbooked from woodhouse.bhsbedsbooked order by date");
    and also read this: http://www.sitepoint.com/forums/show...29&postcount=4
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  9. #9
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Regret this still doesn't work! I have renamed `date` column to `datebooked` to avoid any possible confusion and my code is now this


    //display the list
    $beds = @mysql_query("select date_format(`datebooked`,'&#37;W %D %M %Y'), fambedsbooked from woodhouse.bhsbedsbooked order by datebooked");
    if (!$beds) {
    exit('<p>error performimg query ' . mysql_error() .
    '</p>');
    }


    while ($bed = mysql_fetch_array($beds)) {
    echo "<tr valign='top'>\n";
    $date = $bed['datebooked'];
    $familybeds = $bed['fambedsbooked'];
    $bedsfree = 6 - $familybeds;

    echo "<td align='center'>$date</td>\n";
    echo "<td align='center'>$bedsfree</td>\n";
    echo "</tr>\n";

    }



    ?>


    This gives me a blank $date column in my php page; column $bedsfree completes as intended.

    When I change coding and leave out the date_format both columns of the php page work ok so the problem has to be something with this.

  10. #10
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There is no column name datebooked in your result, you need to give a correlation name for the expression and then use that name in php.

    Code:
    $beds = mysql_query("select date_format(`datebooked`,'&#37;W %D %M %Y') as theDate, fambedsbooked from woodhouse.bhsbedsbooked order by datebooked");
    
    $date = $bed['theDate'];

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    is that really called a correlation name, swampie?

    i've always called it a column alias
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Just checked some of my mySQL books and I can find plenty of references to aliasing but no references to correlation name. Guess that's why I have never heard of a column alias being called a correlation name before.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  13. #13
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that SwampBoogie. Dont mind what you want to call it, but it fixed the problem.

  14. #14
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Correlation name is the term used in the SQL standard specification.

  15. #15
    SitePoint Member cipals15's Avatar
    Join Date
    Nov 2008
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's something like this.. YEAR(date_variable), MONTH(date_variable), DAY(date_variable).

    I'm just a MySQL newbie. Sorry for wasting your time.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cipals15 View Post
    It's something like this..
    no, it isn't, sorry

    it's DATE_FORMAT ftw
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I much rather return the date as a unix timestamp and let my application layer format the date as needed. That way if I need to, I can display the date in a multitude of formats without ever touching my SQL

    Code:
    UNIX_TIMESTAMP(datebooked) AS datebooked
    MySQL v5.1.58
    PHP v5.3.6

  18. #18
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,784
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by BrandonK View Post
    I much rather return the date as a unix timestamp and let my application layer format the date as needed.
    Converting to the required format in the SQL is far more efficient if you only need it in the one format. It is only more efficient to retrieve it as a timestamp if you need to perform calculations on the date and then display multiple formats from the one date retrieved.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i would reserve the word timestamp when talking about mysql data formats to apply strictly to TIMESTAMP

    i would call the integer a unix epoch number

    and they're hard to do for all dates, like for example birthdates before 1970
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •