Show Next Birthday - PHP & MySQL

[spoondevil]

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

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

[guelphdad]

When you create a column as you have with d_o_b2 you must use it in a HAVING clause not a WHERE clause.

[r937]

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?

[spoondevil]

I only want to list the next birthday that is happening this year

[r937]

try this (untested):

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

[guelphdad]

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:

 
 select foo as special, bar from tablename
 having special > 25

as opposed to using

 select foo as special, bar from tablename
 where special > 25

since the latter generates an 'unknown column in where clause' error?

[r937]

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