Looking for birthday with PHP and MySQL

birth date - mysql birthday - php mysql

getting all birthday inside database with PHP

Looking for birthday with PHP and MySQL

We often experience difficulty to find the birthday between two dates inside MySQL database. It is caused by the storage we use in MySQL, namely brith date format like Y-m-d, for example 2000-12-21.

The problem occurs when we try to find the birthday between years, such as between Dec 15, 2020 to Jan 14, 2021.

Read: Getting current total and previous one from MySQL

To explain this, let say we use colum namely: tgl_lahir, with values as follows:

  • Jaka: 1997-02-01
  • Mega: 2001-01-14
  • Satria: 2000-12-21

Now we want to find who has birthday between January 10, 2021 and February 02, 2021. It is easier because it has same year, so we can find it with range of date and month in tgl_lahir.

The SQL query is like below

$result=doQuery("select * from [table] where (DATE_FORMAT(tgl_lahir, '%m-%d') 
DATE_FORMAT(?, '%m-%d'))
order by RIGHT(tgl_lahir,5) ASC", Array('2021-01-10','2021-02-02'));

doQuery is sql query according to mysql class we us.
BETWEEN is MySQLfunction to find data between X and Y.
order by RIGHT(tgl_lahir,5) ASC means that we sort  the result of  RIGHT(tgl_lahir,5), where RIGHT(tgl_lahir,5) is MySQL function to get 5 characters from the right. If the value is: 2001-01-14, so  RIGHT(tgl_lahir,5) is 01-14.

The result of query above is:

  • Mega: 2001-01-14
  • Jaka: 1997-02-01

The query will be different if we try to find birthday between December 20, 2020 and January 15, 2021. The query above cannot be used because it has different year.
Here it is the query:

Read: Getting current total and previous one from MySQL
$result=doQuery("select *, if(MONTH(tgl_lahir)>MONTH('2021-01-15'), MONTH(tgl_lahir), MONTH(tgl_lahir)+12) ord,
if(DAY(tgl_lahir)>DAY('2021-01-15"'), DAY(tgl_lahir), DAY(tgl_lahir)+31) ord2
where ( (DATE_FORMAT(tgl_lahir, '%m-%d') 
	DATE_FORMAT(?, '%m-%d'))
	(DATE_FORMAT(tgl_lahir, '%m-%d') 
	DATE_FORMAT(?, '%m-%d')) ) order by ord,ord2",

There are many variables we use here.

if(MONTH(tgl_lahir)>MONTH('2021-01-15'), MONTH(tgl_lahir), MONTH(tgl_lahir)+12) means that if birth month > 01 (January), then return birth month, else it will be added 12.
The same method is used too for if(DAY(tgl_lahir)>DAY('2021-01-15"'), DAY(tgl_lahir), DAY(tgl_lahir)+31) ord2

It is used, so we can use BETWEEN function because the last date is higher than from date.

The result is as follows:

  • Satria: 2000-12-21
  • Mega: 2001-01-14

Related Article

Web Design Services - Best Webdesign - Best SEO Services

The usage of Bootstrap-DateTimePicker for Date Range

Hallo now, we as Web Design Surabaya wants to give the tutorial about the usage of bootstrap-datetimepicker for choosing check in and check out date. You do not only choose the date range but also the date and time range. To run the tutorial of date ...

Getting current total and previous one from MySQL

As the part of the Best Webdesign, we ever get the request from our customer to show the total selling in a month including previous selling to count the percentage of profit.. To do that, we often use a very simple way, namely using two queris of M ...

The usage of if and else in MYSQL and PHP

Hallo friends of Tatamedia, web developer in Surabaya and Bali.Now we will learn about the usage of if in mysql, so the query can be shortened. Generally, if in mysql has a form as follows: IF(condition, value_if_true, value_if_false) where:condit ...


Responsive web is special program code, so the website can be read in all devices. It is very suitable for better page rank in search engine like google

graphic design in indonesia

Website Design in Indonesia

Graphic Design in Indonesia


+628113638531 / +628113865464


Webdesigner in Indonesia

Videographer in Bali


Tatamedia Solusindo Surabaya

Dekkophotography Bali

Fine Art Photography in Bali

Web Design


Send your request to