There is a database where all registered users are stored. There is a field of birth. How to get all the entries that have a birthday today? I did what I can get records if he was born on this day including the year

$today = date("dmY"); $users = $this->pages_model->get_users(); foreach ($users as $item){ $result=(strtotime($item['birthday'])==strtotime($today)); if ($result){ echo $item['fio']; } }

But I need to get records, that if he has a birthday on 05/19/1988, he also received this record.

All anything, it was possible to play with the request, but the $item['birthday'] field in mySQL is not a date , but varchar .

How to be?

  • The SQL language is designed to manipulate sets. It is sad to see when this feature of it is ignored, and the processing is shifted to a scripting language like php. This is very suboptimal. - artoodetoo

2 answers 2

Fix the base - let the birthday field become a date - the STR_TO_DATE(str,format) function STR_TO_DATE(str,format) will help.

And choose from the database only those users who have a birthday today:

 SELECT * FROM `users` WHERE MONTH(`birthday`) = MONTH(NOW) AND DAYOFMONTH(`birthday`) = DAYOFMONTH(`NOW`)
  • Is it possible to change the structure itself? so that all records are converted - duddeniska
  • In what format are there dates now? If something MySQL is understandable, for example. 1990-12-03 , you can try to make ALTER TABLE users CHANGE birthday birthday DATE NOT NULL; - Sergiks
 select date_format(str_to_date('12/31/2011', '%m/%d/%Y'), '%Y%m');

You can convert your varchar to date and get it using the query. Further processing and everything will be fine.