visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Can someone here help me with a Birthday Script
By: elderp U.S.A.  Date: 23/06/2007 04:49:50  English  Points: 20 Status: Answered
Quality : Excellent
I am trying to improve my birthday script by trying to have it query who has a birthday today and then email that person. Here is my table structure
Table: lpbdays
Fields:
username Varchar(20)
bday Varchar(4) MMDD
eadd varchar (40) email address

This is what I thought but it didn't work
SELECT * FROM `lpbdays` WHERE mid(NOW(),6,5)=CONCAT(bday);
This gets a username but I want it to go automatically:
SELECT * FROM `lpbdays` WHERE `bday` LIKE CONVERT(_utf8 \'0622\' USING latin1) COLLATE latin1_swedish_ci;

Any help would be much appreciated.
By: VGR Date: 23/06/2007 07:21:40 English  Type : Comment
excuse-me, but :
1) why do you use concat() ? You don't concat anything.
2) the first query should be enough so I don't understand what is the "to go automatically" remark and the usefulness (or purpose ! ) of the second query
3) moreover, it doesn't work right out :

mysql> select CONVERT(_utf8 \'0622\' USING latin1) COLLATE latin1_swedish_ci; ERROR: Unknown command '\''.



could you enlighten me a bit ? Thanks.

in the meantime, here's what I think is the best for you so far :

#assuming bday is a real MMDD format select pseudo from Ipbdays where bday=date_format(now(),'%m%d'); # it's better than trying to use concat(month(now()),day(now())) ...



this said, DAYOFYEAR() is probably a good putative friend for your problem solving.
By: elderp Date: 23/06/2007 08:32:00 English  Type : Comment
1. I was trying to follow a thread you had helped another person with.
2. I was trying to convey the result I wanted
3. Worked for me, I later simplified it to select * from lpdays where bday = 0622; and works the same but I wish I could substitute the 0622 for the current month and year.
4. I used select * from lpbdays where bday =date_format(now(),'%m%d'); and it came back with no rows selected.

Anyhow, I appreciate the effort. If you got any more suggestions I am all ears. Thanks.
By: VGR Date: 23/06/2007 08:55:24 English  Type : Comment
1. Yeah. Using mid() on a string is unefficient, especially if the column is a datetime. Sorry if I wrote that for a datetime column.

2. given you have a bday = varchar(4) and not DATE all we've to do is return the now() call as a string with the same format.

That's the purpose of the above query with date_format()

It will work. I suggest you test it using, now NOW(), but a valid bday value, like your own bday ;-)

select * from lpbdays where bday =date_format('2007-05-27','%m%d');


this returns a WHERE condition = where bday='0527'
so it should work if any row in your table has that bday.
Note that your bday=varchar(4) should in fact be a char(4) as you absolutely need to have the month padded with zero on the left, or it won't work.
If you stored '527' in stead of '0527' (for instance ;-), then you're not completely lost. Date_format() can accept %e in stead of %d ; %e goes from 1 to 31 while %d goes from 01 to 31

regards
By: elderp Date: 23/06/2007 09:15:48 English  Type : Comment
It's working now, not sure why but it works. Thanks, for the tips.
I am not worried about the varchar thing because I have a validation string on my php script that validates it before it accepts it, so far I haven't had any bad input. Now, I just need to find out how to email me a reminder automatically and I am set! I don't think it should be too hard I have a few ideas. Just got to try them.

Again thanks for the help.
By: VGR Date: 23/06/2007 09:42:37 English  Type : Answer
no problem. Sending the email reminder is also very easy. Basically, it's

if (!mail($dest,$subject,$body,$extraheaders)) { // $extraheaders can be useful to specify the From: the obsolete Reply-To: and is necessary for the BCC: // email sending failed } else { // success }



search on this site, plenty of examples

PS don't forget to close this Question with the appropriate ra,king (+ to +++) and button (Accept or Split with points repartition)
By: elderp Date: 24/06/2007 07:08:26 English  Type : Comment
Sorry to bug you but how would you change this script to pull a result a day from now. For example I want a list of everyone that is going to have their birthday tommorow, that way I am one day ahead.
By: VGR Date: 24/06/2007 10:13:30 English  Type : Comment
of course. The solution is to use INTERVAL , DATE_DIFF() or a simple arthmetic operation on the date (hence the usefulness to have a DATE column, and not a varchar(4) ... ;-)
Thanks you-know-who, MySql is very "intelligent" and performs automatic type conversion between date, datetimes and strings as is fit.
By: elderp Date: 25/06/2007 05:37:40 English  Type : Comment
Found a super simple solution:

select * from lpbdays where bday =(date_format(now(),\'%m%d\')+1)';

By: VGR Date: 25/06/2007 07:29:09 English  Type : Comment
yes, arithmetic, BUT it has limitations that explain the usefulness of INTERVAL 1 DAY or INTERVAL 1 MONTH :

mysql> select date_format(now(),'%m%d')+1; +-----------------------------+ | date_format(now(),'%m%d')+1 | +-----------------------------+ | 626 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),'631')+1; +----------------------------+ | date_format(now(),'631')+1 | +----------------------------+ | 632 | +----------------------------+ 1 row in set (0.00 sec)



do you know the 32nd of June ? ;-)))
By: elderp Date: 26/06/2007 03:07:53 English  Type : Comment
I don't know how to use Interval but if you tell me how it works I am more than happy to learn.
By: VGR Date: 26/06/2007 07:40:43 English  Type : Comment
roughly :

select * from lpbdays where concat(year(),bday)<now()+interval 1 month;

things would be simplier if your bday was stored as a DATE, ie YYYY-MM-DD : direct date arithmetics could apply.
By: elderp Date: 26/06/2007 15:00:23 English  Type : Comment
I would except for the fact that no one on the list wants to give their birthyear so it isn't in that format.
By: VGR Date: 26/06/2007 17:54:00 English  Type : Comment
strange idea ;-)
they could give it out, as long as it isn't displayed anywhere ;-)

to stick purely with your varchar(4) current datatype, here's the solution :

mysql> select concat(year(curdate()),'-',substr('0625',1,2),'-',substr('0625',3,4)) - interval 1 month; +------------------------------------------------------------------------------------------+ | concat(year(curdate()),'-',substr('0625',1,2),'-',substr('0625',3,4)) - interval 1 month | +------------------------------------------------------------------------------------------+ | 2007-05-25 | +------------------------------------------------------------------------------------------+



so you could use :

select * from lpbdays where concat(year(curdate()),'-',substr(bday,1,2),'-',substr(bday,3,4)) < now() + interval 1 month;



regards ;-)
By: elderp Date: 27/06/2007 02:48:34 English  Type : Comment
I got:

Error SQL query: SELECT * FROM lpbdays WHERE concat( year( curdate( ) ) , '-', substr( bday, 1, 2 ), '-', substr( bday, 3, 4 ) ) < now( ) + INTERVAL 1 MONTH LIMIT 0 , 30 MySQL said: #1305 - FUNCTION pedroza_lpbirthday.substr does not exist

By: VGR Date: 27/06/2007 07:37:20 English  Type : Comment
rtfm :
SUBSTR() is a synonym for SUBSTRING(), added in MySQL 4.1.1.

try with SUBSTRING() in stead if you run mysql < 4.1.1

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 329.669000 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page