Ad:

PHP Discussion Thread
Unsolved
Views: 5614

You are currently viewing page 2 of this multi-page discussion thread; Jump to the first page

Feb 12th, 2009

Re: birthday reminder

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

almostbob

Reputation Points: 562

Solved Threads: 369

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Feb 12th, 2009

Re: birthday reminder

Click to Expand / Collapse Quote 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.

almostbob

Reputation Points: 562

Solved Threads: 369

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Feb 12th, 2009

Re: birthday reminder

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

Cheers

oskido2010

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Feb 2009

Permalink

Nov 2nd, 2009

Re: birthday reminder

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?

appiee

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2009

Permalink

Nov 3rd, 2009

better yet

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:

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!

appiee

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2009

Permalink

Nov 3rd, 2009

Re: birthday reminder

Click to Expand / Collapse Quote 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:
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.

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:

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.

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Previous
1
Page 2

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Deleting rows

Next Thread in PHP Forum Timeline: Suggestions on X-cart opensource ?

DaniWeb Message

Cancel Changes

User Name
Password