SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DATE_FORMAT() Working But Not Showing

    I am using the following script to get the info from the database:
    PHP Code:
    <?php
    mysql_select_db
    ($database_localhost$localhost);
    $query_GetBandMembers "SELECT name, dob, home, dayjob, instrument, likes, dislikes, photoname FROM profiles ORDER BY id ASC";
    $GetBandMembers mysql_query($query_GetBandMembers$localhost) or die(mysql_error());
    $row_GetBandMembers mysql_fetch_assoc($GetBandMembers);
    $totalRows_GetBandMembers mysql_num_rows($GetBandMembers);
    ?>
    And it it extracting it fine and showing the right date when I use:
    PHP Code:
    <?php echo $row_GetBandMembers['dob']; ?>
    I want the date to show as 1st January 2001 so I changed the script to:
    PHP Code:
    <?php
    mysql_select_db
    ($database_localhost$localhost);
    $query_GetBandMembers "SELECT name, DATE_FORMAT('dob','%D %M %Y') AS dob1, home, dayjob, instrument, likes, dislikes, photoname FROM profiles ORDER BY id ASC";
    $GetBandMembers mysql_query($query_GetBandMembers$localhost) or die(mysql_error());
    $row_GetBandMembers mysql_fetch_assoc($GetBandMembers);
    $totalRows_GetBandMembers mysql_num_rows($GetBandMembers);
    ?>
    The select for the dob (Date of Birth) has changed, and when I use:
    PHP Code:
    <?php echo $row_GetBandMembers['dob1']; ?>
    Now it just shows a blank cell in the table.

    I am using Dreamweaver MX, and unfortunately it doesn't have any built in date formatting (a big oversight on their behalf), so it all has to be done by hand

    Ian Gunter

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2001
    Location
    UK
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try removing the single-quotes from round the dob. I.e.

    DATE_FORMAT(dob, '%D, %M, %Y')

  3. #3
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers mate. It worked a treat

    Do you know of any tutorials to learn when and when no to use quotes and single-quotes?

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2001
    Location
    UK
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually it's quite straight forward.

    If you want to refer to a column then don't use quotes. If you are referring to a specific value, then use single quotes (you can use double quotes with MySQL but you shouldn't as it is not strictly ANSI SQL compliant).

    You actually don't need to quote numeric values if MySQL is expecting a numeric at that point (either as a column type or in a function), however, if you do quote them it has no adverse affect and so I always quote them.

    In short:

    Referring to a column name? Don't use quotes.
    Referring to a value? Quote.

    Hope that helps.

  5. #5
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool. sounds simple now it is explained.

    cheers


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
  •