SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  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)

    Order Users By Who's Birthday Is Next

    I have a list of users and would like to order them by who's birthday is next from closest to farthest.

    I can't figure out how to do this in MySQL, if anyone has any ideas, I would be eternally thankful

    EDIT: I have the birth dates saved in the databse as DATE YYYY-MM-DD

    Regards
    Ian

  2. #2
    SitePoint Wizard Nikolas's Avatar
    Join Date
    Feb 2005
    Location
    Greece
    Posts
    1,222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT ....... FROM ...... WHERE birthdate > NOW() ORDER BY birthdate ASC

    Is that helping?

  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)
    The only problem with that is that all the birthdates are going to be less than todays date.

    I need to somehow strip the year off and then order by the month then day and when it get's to the end of the list, start from the 1st Jan and work up to todays date.

    Ian

  4. #4
    SitePoint Wizard Nikolas's Avatar
    Join Date
    Feb 2005
    Location
    Greece
    Posts
    1,222
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to have the dates from Jan 1st to todays then you need BETWEEN.

    About the strip you wont need it, if you put an ORDER statement the results will come in their order

  5. #5
    Wadge! F4nat1c's Avatar
    Join Date
    Oct 2005
    Location
    South Wales, UK
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you just want to arrange the dates just in the database? Or do you want them to be displayed too? This is better of placed in the mysql forums i feel.
    OMFG SitePoint ROXORZ TEH BIG ONE111!
    Wish you were invisible?

  6. #6
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to create a query to extract 5 rows from the database and order by the next birthday that is happening.

  7. #7
    Wadge! F4nat1c's Avatar
    Join Date
    Oct 2005
    Location
    South Wales, UK
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe this forum topic will help you:

    http://www.sitepoint.com/forums/showthread.php?t=336412
    OMFG SitePoint ROXORZ TEH BIG ONE111!
    Wish you were invisible?

  8. #8
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After much playing around, I have come up with the following
    PHP Code:
    SELECT namedate_birth, IF(DAYOFYEAR(date_birth) < DAYOFYEAR(CURDATE()), DAYOFYEAR(date_birth)+366,DAYOFYEAR(date_birth)) as birthdayofyearFROM birthdays WHERE buID=$userID ORDER BY birthdayofyear LIMIT 05
    This will extract the next 5 birthdays happening and wrap through to Jan 1st to currant date if there are not more than 5 this year.
    Code:
    Test 2	1977-01-20
    Test 3    1977-07-04
    Test 4    1977-07-15
    Test 5    1977-11-13
    Test 6    1977-01-05

  9. #9
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Or you could the approach that this guy did:

    http://thedailywtf.com/forums/56520/ShowPost.aspx



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
  •