Ad:

MySQL Discussion Thread
Unsolved
Views: 6026

Jun 23rd, 2007

Help with birthday MySQL Script

Expand Post »

Ok this is what my table structure looks like:
username varchar(20)
eadd varchar(40) for email addresses
bday varchar(4) everyone is posting mmdd
emailques varchar(2) they answer yes or no
I want my query statement to tell me what birthdays are today and as a bonus email them on their birthday.
I tried:
select * from lpbdays where bday = curdate();

That didn't work the closest I got was when I did:
select * from lpdays where bday = 0622 (if I do it manually);

If anyone out there can help me out on this that would be great.

Thanks,
Elderp

Similar Threads

Traffic Exchange in PHP
MySql file in MySQL
PHP Confirm Delete in PHP
what to do with this .SQL script? in MySQL

elderp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Mar 2007

Permalink

Jun 23rd, 2007

Re: Help with birthday MySQL Script

I guess this is a problem with data types.

Try looking here. Curdate should return something like 2004-11-30. Date variables and string (varchars) cannot be compared directly.

You are storing your dates as 0622. So first you would have to convert the curdate() to a string, strip it of the hypens and take of the year.

Last edited by iamthwee; Jun 23rd, 2007 at 4:48 am.

iamthwee

Featured Poster

Reputation Points: 1536

Solved Threads: 431

Posting Expert

Offline

5,865 posts
since Aug 2005

Permalink

Jun 23rd, 2007

Re: Help with birthday MySQL Script

Thanks for the help, I actually did solve this issue (with some help from a European Discussion Board).
Here was my solution:
select * from lpbdays where bday =date_format(now(),'%m%d');

elderp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Mar 2007

Permalink

Jun 24th, 2007

Re: Help with birthday MySQL Script

Anyone know or have an idea how I can change this query so it will give me a result one day from now. For example I want it to give me a list of all the birthdays that will happen tommorow that way I can prepare for them.

elderp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Mar 2007

Permalink

Jun 24th, 2007

Re: Help with birthday MySQL Script

Click to Expand / Collapse Quote originally posted by elderp ...

Anyone know or have an idea how I can change this query so it will give me a result one day from now. For example I want it to give me a list of all the birthdays that will happen tommorow that way I can prepare for them.

I don't see why you would need to prepare this in advance. Databases are real time apps ya know.

(Toggle Plain Text)

select DATE_ADD('2007-06-27',INTERVAL 1 DAY);

See for further details...
http://dev.mysql.com/doc/refman/4.1/...nction_curdate

Last edited by iamthwee; Jun 24th, 2007 at 10:17 am.

iamthwee

Featured Poster

Reputation Points: 1536

Solved Threads: 431

Posting Expert

Offline

5,865 posts
since Aug 2005

Permalink

Jun 25th, 2007

Re: Help with birthday MySQL Script

Found a super simple solution:
select * from lpbdays where bday =(date_format(now(),\'%m%d\')+1)';

elderp

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Mar 2007

Permalink

Sep 28th, 2007

Re: Help with birthday MySQL Script

I have a similar situation but I am storing my birthday as a date format yyyy-mm-dd. I need to search for birthdays from the current day up to 2 weeks. Thank you.

today31

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2007

Permalink

Sep 30th, 2007

Re: Help with birthday MySQL Script

Maybe you could use something like:

MySQL Syntax (Toggle Plain Text)
SELECT <whatever> FROM <TABLE_NAME> WHERE DATE >= 'starting date' AND DATE < 'ending date'
SELECT <whatever> FROM <TABLE_NAME> WHERE date >= 'starting date' AND date < 'ending date'

perhaps?

The ending date could be got by using the data_add (14 days) to the current date.

Last edited by iamthwee; Sep 30th, 2007 at 10:51 am.

iamthwee

Featured Poster

Reputation Points: 1536

Solved Threads: 431

Posting Expert

Offline

5,865 posts
since Aug 2005

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 MySQL Forum Timeline: Indexing and Query optimisation.

Next Thread in MySQL Forum Timeline: Trigger problem...

DaniWeb Message

Cancel Changes

User Name
Password