CODEWITHSUNDEEP
phpmysqlwordpresssortingsql-order-by
tom
tom

Reputation: 95

Find Upcoming birthdays with Mysql

I want to select the next upcoming birthdays in MYSQL. My date is stored as: 02/19/1981 and not in a date field. I think it has to sort by day and month and not year but i can not find out how.

How can i do this? This is the query till now:

$sql = "SELECT * FROM wp_postmeta WHERE meta_key='_web_date' ORDER BY ....";

Upvotes: 1

Views: 978

Answers (3)

JProgrammer
JProgrammer

Reputation: 38

If it's possible for you change the date column to type date.

Otherwise try this:

SELECT month(str_to_date(birthdayColumn, "%m/%d/%Y")) as month, day(str_to_date(birthdayColumn, "%m/%d/%Y")) as day FROM yourTable order by month, day;

Result:

+-------+------+
| month | day  |
+-------+------+
|     1 |   12 |
|     2 |   19 |
|     9 |   10 |
|    12 |   15 |
+-------+------+

Upvotes: 1

ChrisGuest
ChrisGuest

Reputation: 3608

This is a test environment.

    CREATE TEMPORARY TABLE `birthdays` (
       `id` int(4),
       `name` VARCHAR(50),
       `dob` CHAR(10)
    ) ENGINE=MEMORY;

    INSERT INTO birthdays VALUES (1,'Alice', '02/19/1951'), (2,'Bob', '09/10/2015'), (3,'Carol', '12/15/2000'), (4,'Doug', '01/12/2011');

I created this function to get the next birthday. The logic may throw some interesting results over 29th Feb / 1st March.

    DELIMITER $$

    CREATE FUNCTION `next_birth_day`(d_dob DATE) RETURNS DATE
        DETERMINISTIC
    BEGIN
            /* NOTE: this logic ignores the handling of leap years */
            /* MySQL will happily construct invalid leap years and they are ordered
            between 29/2 & 1/3 in this code. */

            DECLARE d_today DATE;
            DECLARE d_this_year_bday DATE;
            DECLARE d_next_year_bday DATE;

            SET d_today = DATE(NOW());
            SET d_this_year_bday = CONCAT(YEAR(d_today), '-', MONTH(d_dob), '-', DAY(d_dob));
            SET d_next_year_bday = CONCAT(YEAR(d_today)+1, '-', MONTH(d_dob), '-', DAY(d_dob));

            RETURN IF( d_this_year_bday < d_today, d_next_year_bday, d_this_year_bday);
    END

    $$


    DELIMITER ;

Then you can do a query and order by next_birth_day:

    SELECT *, str_to_date(dob, "%m/%d/%Y") AS dob_dt,   
        next_birth_day(str_to_date(dob, "%m/%d/%Y")) AS next_bday
    FROM birthdays
    ORDER BY next_birth_day(str_to_date(dob, "%m/%d/%Y")) ASC

giving results like this:

+------+-------+------------+------------+------------+
| id   | name  | dob        | dob_dt     | next_bday  |
+------+-------+------------+------------+------------+
|    3 | Carol | 12/15/2000 | 2000-12-15 | 2015-12-15 |
|    4 | Doug  | 01/12/2011 | 2011-01-12 | 2016-01-12 |
|    1 | Alice | 02/19/1951 | 1951-02-19 | 2016-02-19 |
|    2 | Bob   | 09/10/2015 | 2015-09-10 | 2016-09-10 |
+------+-------+------------+------------+------------+

Upvotes: 0

Ramos Alcarez Jr
Ramos Alcarez Jr

Reputation: 19

You can use the php date() function. For example ate('Y-m-d',strtotime("+7 day")); then create a sql query which selects dates which are in the upcoming 7 days

Upvotes: 0

Related Questions