How to select rows which are today events?

I have a set of tables (phonebooks, numbers, extrafields, extradatas)

• phonebooks - List of users phonebooks (username, book_id, title)
• numbers - List of each phonebook numbers (number_id, book_id, number, fullname)
• extrafields - List of extra fields (birthday, email, ...) and its table fields are : field_id, title
• extradatas - List of extra data of each number's extra fileds (book_id, field_id, number_id, value)
• relations - Which extra-field belongs to which phonebook (book_id, field_id)

Imagine a user who have a phonebook and an extra field of birthday.

How can i select numbers which their birthday is today?

EDIT: I have specified uname, book_id, field_id in my hands (from PHP script) and results must be limited to these values.

Note that i save date fields in unix timestamp and extradatas.value type is varchar.

This is the hardest math-join-select in my life (i think).

Why math?开发者_Python百科 : because i want to calculate day number from unix timestamp, without need to use functions (like DATE_FORMAT)

SELECT DISTINCT n.number
FROM phonebooks p
INNER JOIN  numbers n ON n.book_id = p.book_id
INNER JOIN extradatas ed ON ed.book_id = n.book_id
INNER JOIN extrafields ef ON ef.field_id = ed.field_id AND ef.title = 'birthday'
WHERE p.book_id = :book_id
    AND p.username = :username
    AND ed.value BETWEEN unix_timestamp(DATE(now()))
        AND unix_timestamp(DATE(now()) + INTERVAL 1 DAY);

Note that the only significant difference (that I see) between my answer and @Andrej L's is how I compare dates. On larger datasets, @Andrej L's solution would require converting every birthday in the system using DATEFORMAT. My solution should allow MySQL to evaluate the comparisons once, up front, and possibly take advantage of an index for extrafields.value.

Try torun:

Select number from numbers as n inner join extradatas e on (n.number_id=e.number_id and  n.book_id=e.book_id) inner join extrafields ex on e.field_id=ex.field_id
where ex.title='birthday' and DATE_FORMAT(ex.value, '%Y-%m-%d')=DATE_FORMAT(NOW(), '%Y-%m-%d')