SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    11
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Display birthday with dd-mm only from mysql date (dd-mm-yyyy)

    I have setup a church membership application where I am capturing member information. On the date of birth, even though I am capturing dd-mm-yyyy I want the display to only retrieve dd-mm from mysql database. How do I accomplish this? I am using ....echo $row[date].

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    MySQL stores the date as yyyy-mm-dd

    Use php to explode() the string at the - points and then select the two parts you want to use, and echo them.

    http://php.net/manual/en/function.explode.php

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Or you can do it in MySQL with DATE_FORMAT

    Code MySQL:
    SELECT DATE FORMAT (dat_date_field, '%d %M') AS birthday
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    11
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tonyck View Post
    I have setup a church membership application where I am capturing member information. On the date of birth, even though I am capturing dd-mm-yyyy I want the display to only retrieve dd-mm from mysql database. How do I accomplish this? I am using ....echo $row[date].
    Here is the code I am using to execute this.

    PHP Code:
    $sql "select * from membership WHERE dob != 0 order by dob ASC";
    $query mysql_query$sql );
     
    echo 
    "<table width='100%' border='1' cellpadding='3' cellspacing='0'>
    <tr bgcolor='#DBEAF9' border:'1px solid #ccc'>

    <th>First Name</th>
    <th>Middle Name</th>
    <th>Last Name</th>
    <th>Date of Birth</th>

    </tr>"
    ;
     
    while( 
    $row mysql_fetch_assoc($query) )
    {
    echo 
    "<tr><td>$row[fname]</td><td>$row[mname]</td><td>$row[lname]</td><td>$row[dob]</td></tr>";
    }
    echo 
    "</table>";
     
    ?> 
    Last edited by SpacePhoenix; Oct 6, 2013 at 18:14. Reason: placed php tags around php code

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    @tonyck ;

    A change to your query:

    Code:
    SELECT
          fname
        , mname
        , lname
        , DATE_FORMAT(dob, '%d %m) AS birthday
    FROM
        membership
    WHERE
        dob <> 0
    ORDER BY
        dob ASC
    When dealing with the result set from that the dob field of the result set will be "birthday" and not "dob"

    Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn't tie you down so much to a particular database server software.

    Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    11
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks Spacephoenix this works. Now trying to figure out how to sort by date month. Since the mysql database has the year in it. The display is still using the year to sort, hence when it displays dd mm it will have the same month apart cause its considering the year as well on the database. e.g 12 Nov, 13 Jan, Dec 10 Nov 5...

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Change the ORDER BY clause to:

    Code:
    ORDER BY     birthday ASC
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    11
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks it worked.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tonyck View Post
    Thanks it worked.
    yeah, but how useful is that?

    you're going to get them in day order, regardless of month

    i would've thought month-day order would be more useful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    11
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Actually I had to switch to month day cause when I did day month. It was sorting by day and hence months were scattered. Thanks again


Tags for this Thread

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
  •