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: 5 Reasons Why You Must Learn Javascript Now

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

<?php
$result=doQuery("select * from [table] where (DATE_FORMAT(tgl_lahir, '%m-%d') 
BETWEEN DATE_FORMAT(?, '%m-%d') AND 
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: 5 Reasons Why You Must Learn Javascript Now
$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') 
	BETWEEN DATE_FORMAT(?, '%m-%d') AND 
	DATE_FORMAT(?, '%m-%d'))
OR
	(DATE_FORMAT(tgl_lahir, '%m-%d') 
	BETWEEN DATE_FORMAT(?, '%m-%d') AND 
	DATE_FORMAT(?, '%m-%d')) ) order by ord,ord2",
Array('2020-12-20','2020-12-31','2021-01-01','2021-01-15'));

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

7 Advantages of Using Web Programming With PHP

PHP is known as a popular web programming language in the world. It is used by millions of website developers and provides many advantages that are the main options for web developers in Indonesia. A Glimpse of PHP PHP is an open source programming ...


10 Tips to Learn JavaScript Easily

Basically, JavaScript is one of the best programming languages for web developers. You can develop a web and open the door for many employment opportunities with JavaScript. However, learning it can be a difficult task for beginners. Ultimate Tips t ...


How to Learn PHP for Beginners? Here Are Some Useful Tips

PHP which is short for Hypertext Preprocessor is a programming language that has been used by so many websites all over the globe since its creation in 1994. Even after almost three decades, some of the best and biggest websites are still using it su ...


Working Process

Web Design - Wedding Photography - Wedding Videography

Our Location at Surabaya and Bali

Website Design in Indonesia

Graphic Design in Indonesia

Contact

+628113638531 / +628113865464

info@tatamedia.com

Webdesigner in Indonesia

Videographer in Bali

Office

Tatamedia Solusindo Surabaya

Dekkophotography Bali

Fine Art Photography in Bali

Web Design

Support

Send your request to

info@tatamedia.com