SitePoint Sponsor

User Tag List

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

    Show Next Birthday - PHP & MySQL

    My site contains a database of a group of friends, all with profiles and their date of birth entered. I need a query to show when the next birthday & the date it is, also how old they will be.

    EG:
    Next Birthday: Ian Gunter will be 28 on the 11th July

    I have come up with this
    Code:
    SELECT id, name_f, name_l, DATE_FORMAT(d_o_b,'%m %D') AS d_o_b2
    FROM users
    WHERE d_o_b <=CURDATE()
    ORDER BY d_o_b2 ASC
    LIMIT 0,1
    This selects the first birthday that happens that year, I need it to somehow reference today date and sort from there, but when I use the d_o_b2 field name in the WHERE clause, I get error # 1054 (unknown column).

    I also can't figure out ho to use the year to work out how old they will be.


    Any ideas?
    Gunter
    Last edited by spoondevil; May 31, 2004 at 16:04.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you create a column as you have with d_o_b2 you must use it in a HAVING clause not a WHERE clause.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dave, that's not necessarily true

    aliases are tricky, though, so i'm not sure where you were going with that

    gunter, what does today's date have to do with people's birthdays this year? do you only want to list the birthdays in the rest of the year (i.e. june 1 - dec 31) or all year?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I only want to list the next birthday that is happening this year

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this (untested):
    Code:
    select id
         , name_f
         , name_l
         , d_o_b
         , date_format(d_o_b ,'%m %D') as d_o_b2
      from users
     where date_add(d_o_b, interval
             year(current_date) - year(d_o_b)
                    years)
                >= current_date
    order 
        by d_o_b 
     limit 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy

    am I not correct when creating an alias column like d_o_b2 that you need to search for it in a HAVING clause like this instead of a WHERE clause:

    Code:
     
     select foo as special, bar from tablename
     having special > 25
    as opposed to using
    Code:
     select foo as special, bar from tablename
     where special > 25
    since the latter generates an 'unknown column in where clause' error?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you may not use an alias in the WHERE clause

    you may use an alias in the HAVING clause but you don't have to

    if you do use HAVING, you'd better either (1) also use GROUP BY, or (2) use only aggregate expressions in the SELECT list
    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
  •