DaniWeb Forum Index > Web Development > PHP

birthday reminder

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2009

Posts: 1,396

Reputation: almostbob has a spectacular aura about

Solved Threads: 170

almostbob

Offline

Nearly a Posting Virtuoso

Re: birthday reminder

#11

Feb 12th, 2009

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$query = sprintf("UPDATE TABLE SET birthday = %s",
   strtotime($birthday));
$query = sprintf("UPDATE TABLE SET birthday = %s",
   strtotime($birthday));

that makes more sense

Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved

•

Join Date: Jan 2009

Posts: 1,396

Reputation: almostbob has a spectacular aura about

Solved Threads: 170

almostbob

Offline

Nearly a Posting Virtuoso

Re: birthday reminder

#12

Feb 12th, 2009

•

Originally Posted by oskido2010

Hope something like this will work perfectly!! any more ideas or suggestions, really appreciate folks:

The wright brothers airplane still flies,
but they dont keep bolting new bits on it to make it useable
they mothballed it in a museum and build jumbo jets

bigger
faster
able to move hundreds of people
thousands of times further and
hundred times faster than the original plane

this is a hint
ditch the biplane
build a jumbo
There would be less work in redoing the code, than always repairing it.

What about the next time you wish to add a feature.
it is real easy in timestamps to add StUfF,
"who's online"
"popular pages"
"most recently viewed'
without doing much programming work.

•

Join Date: Feb 2009

Posts: 11

Reputation: oskido2010 is an unknown quantity at this point

Solved Threads: 0

oskido2010 oskido2010 is offline

Offline

Newbie Poster

Re: birthday reminder

#13

Feb 12th, 2009

Thanks Guys!! really appreciate ur help ditch the biplane
build a jumbo nice 1.

Cheers

•

Join Date: Nov 2009

Posts: 2

Reputation: appiee is an unknown quantity at this point

Solved Threads: 0

appiee

Offline

Newbie Poster

#14

Nov 2nd, 2009

Bumped into this tread just now, and tried to build a nice jumbojet as adviced. But i can't put birthdays as timestamps in MySQL which are older than 1970. Am i wrong, or is there a nifty bypass?

•

Join Date: Nov 2009

Posts: 2

Reputation: appiee is an unknown quantity at this point

Solved Threads: 0

appiee

Offline

Newbie Poster

better yet

#15

Nov 3rd, 2009

I have stored the birthdates in the db in a YYYY-MM-DD format (just string) and want to get all birthdays from now to 7 days ahead.
Timestamps are no option as far as i know (see above post).
Here I found the solution to get it all by only using a MySQL-query:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
SELECT * FROM `members` WHERE DATE_FORMAT(`dateofbirth`, '%m%d') >= DATE_FORMAT(NOW(), '%m%d') AND DATE_FORMAT(`dateofbirth`, '%m%d') <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m%d') ORDER BY DATE_FORMAT(`dateofbirth`, '%m%d') ASC
SELECT * FROM `members` WHERE DATE_FORMAT(`dateofbirth`, '%m%d') >= DATE_FORMAT(NOW(), '%m%d') AND DATE_FORMAT(`dateofbirth`, '%m%d') <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m%d') ORDER BY DATE_FORMAT(`dateofbirth`, '%m%d') ASC

This works simply and beautifully, without any heavy php-parsing.

I am so happy!

•

Join Date: Sep 2005

Posts: 1,083

Reputation: digital-ether is just really nice

Solved Threads: 66

digital-ether digital-ether is offline

Offline

Veteran Poster

#16

Nov 3rd, 2009

•

Originally Posted by appiee

I have stored the birthdates in the db in a YYYY-MM-DD format (just string) and want to get all birthdays from now to 7 days ahead.
Timestamps are no option as far as i know (see above post).
Here I found the solution to get it all by only using a MySQL-query:
Help with Code Tags

PHP Syntax (Toggle Plain Text)
SELECT * FROM `members` WHERE DATE_FORMAT(`dateofbirth`, '%m%d') >= DATE_FORMAT(NOW(), '%m%d') AND DATE_FORMAT(`dateofbirth`, '%m%d') <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m%d') ORDER BY DATE_FORMAT(`dateofbirth`, '%m%d') ASC
SELECT * FROM `members` WHERE DATE_FORMAT(`dateofbirth`, '%m%d') >= DATE_FORMAT(NOW(), '%m%d') AND DATE_FORMAT(`dateofbirth`, '%m%d') <= DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY), '%m%d') ORDER BY DATE_FORMAT(`dateofbirth`, '%m%d') ASC
This works simply and beautifully, without any heavy php-parsing.

I am so happy!

If you are after optimized queries, another way is to save the day, month, and year as individual INT columns.

That way you can use the indexes on the INT columns, just as you would a timestamp, but allow any range of dates.

An example of a date range query, for birthdays within today (November 3rd) and the next 7 days.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM users WHERE YEAR = 2009 AND MONTH = 11 AND DAY >= 3 AND DAY <= 9
SELECT * FROM users WHERE year = 2009 AND month = 11 AND day >= 3 AND day <= 9

or for two overlapping months, such as last week:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM `users` WHERE 
(YEAR = 2009 AND MONTH = 10 AND DAY >=27)
OR
(YEAR = 2009 AND MONTH = 11 AND DAY <= 3)
SELECT * FROM `users` WHERE 
(year = 2009 AND month = 10 AND day >=27)
OR
(year = 2009 AND month = 11 AND day <= 3)

A few things need to be calculated on the PHP side though, such as the number of days in the month and the overlaps between months. I wrote a class to handle this: http://code.google.com/p/php-calendar-class/

That would be more efficient then DATE_FORMAT() which does not make use of indexes.

www.fijiwebdesign.com - web design and development and fun
Cpanel Email - Let users Register email accounts on your website upon registration
Ajax Chat - Fully browser based chat!