Page 1 of 1
Finding Birth days dew in next 10days
Posted: Sat Nov 08, 2008 11:02 am
by arunkar
Hi Guys,
My head dosent seem to get this logic...
This is the code:
Code: Select all
// database Table
// day , month , year are three different fields chars and year varchar(4)
// eg data 19 , 11, 1990
$birthdays = "10";
$daystime = time() + ($birthdays * 24 * 60 * 60 );
$lastday = date('Ynj', $daystime);
$lastdaymonth = date('n', $daystime);
$lastdayyear = date('Y', $daystime);
$tday= date(j);
$tmonth= date(n);
$tyear = date(Y);
$nowdate = date(Ynj);
//$todaymd= "$tyear$tmonth$tday";
$todaymd= "$tday$tmonth$tyear";
$qb = "select distinct `user`,`day`,`month`,`year` from `user` WHERE `month` = ('$tmonth') OR `month` > ('$tmonth') OR `month` = ('$lastdaymonth ') order by `year` ASC";
There is a error in this logic it displays all the peoples date of bith until june next year...
Any suggestions experts?
thanks
Arun
Re: Finding Birth days dew in next 10days
Posted: Sat Nov 08, 2008 12:13 pm
by sparrrow
Can you let MySQL do the date logic for you?
Code: Select all
SELECT * from table where somedate >= now() AND somedate <= ADDDATE(now(), 10)
Re: Finding Birth days dew in next 10days
Posted: Sat Nov 08, 2008 9:58 pm
by arunkar
Thanks Sparrow,
but how would I do it when the date is stored in three different fields namely, 'day' 'month' and 'year' and evey field stores numbers...
// day , month , year are three different fields chars and year varchar(4) respectively
//eg data 19 , 11, 1990
Any suggestions experts?
thanks
Re: Finding Birth days dew in next 10days
Posted: Sun Nov 09, 2008 3:47 am
by Mark Baker
Code: Select all
SELECT *
FROM TABLE
WHERE STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d' >= now()
AND STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d' <= ADDDATE(now(), 10)
Re: Finding Birth days dew in next 10days
Posted: Sun Nov 09, 2008 9:00 am
by arunkar
Thanks Mark Baker,
But when I tried the below code I got some error:
Code: Select all
SELECT * FROM php_user WHERE STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d' >= now()
AND STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d' <= ADDDATE(now(), 10)
Error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
(0 ms taken)
I tried ADDDATE(now(), "10") and ADDDATE(now(), '10') nothing worked...
Any ideas to overcome the above error?
Thanks guys
Arun
Re: Finding Birth days dew in next 10days
Posted: Sun Nov 09, 2008 11:43 am
by Mark Baker
Code: Select all
SELECT *
FROM php_user
WHERE STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d') >= now()
AND STR_TO_DATE(CONCAT(year,'/',month,'/',day), '%Y/%m/%d') <= ADDDATE(now(), 10)
Missing closing brackets
Re: Finding Birth days dew in next 10days
Posted: Mon Nov 10, 2008 3:43 am
by arunkar
Hi Mark Baker,
Thanks for helping me to fix the bug,
On executing the SQL it returned no data. Although in the table there are records as bleow:
Data in table:
Code: Select all
day month year
12 11 1980
11 11 1984
17 11 1982
So by our logic the result should show me three birthdays right? as todays date is 10th Nov 2008 its should it not show birthdays upto 10th Nov? But no records are displayed and there are no errors too
Code: Select all
The Fields types in the table are:
[b]Field Type Collation Null Privileges [/b]
day char(2) latin1_swedish_ci NO select,insert,update,references
month char(2) latin1_swedish_ci NO select,insert,update,references
year varchar(4) latin1_swedish_ci NO select,insert,update,references
Any suggestions Experts?
Thanks
Arun
Re: Finding Birth days dew in next 10days
Posted: Mon Nov 10, 2008 5:00 am
by VladSun
arunkar wrote:Hi Mark Baker,
Thanks for helping me to fix the bug,
On executing the SQL it returned no data. Although in the table there are records as bleow:
Data in table:
Code: Select all
day month year
12 11 1980
11 11 1984
17 11 1982
So by our logic the result should show me three birthdays right? as todays date is 10th Nov 2008 its should it not show birthdays upto 10th Nov? But no records are displayed and there are no errors too
Code: Select all
The Fields types in the table are:
[b]Field Type Collation Null Privileges [/b]
day char(2) latin1_swedish_ci NO select,insert,update,references
month char(2) latin1_swedish_ci NO select,insert,update,references
year varchar(4) latin1_swedish_ci NO select,insert,update,references
Any suggestions Experts?
Thanks
Arun
Maybe because today is year 2008

A modified version would be:
Mark Baker'
[sql]SELECT * FROM php_user WHERE STR_TO_DATE(CONCAT(YEAR(NOW()),'/',month,'/',day), '%Y/%m/%d') >= now() AND STR_TO_DATE(CONCAT(YEAR(NOW()),'/',month,'/',day), '%Y/%m/%d') <= ADDDATE(now(), 10)[/sql]
Take a look at
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
There are a lot of helpful functions
PS: Also reconsider your DB design - these year,month,day fields should be a single DATE type field. This way you will be able to have an index build and your queries speeded up.
PPS: Hm, maybe the 29th of February will be a problem with this query - test it

Re: Finding Birth days dew in next 10days
Posted: Mon Nov 10, 2008 12:24 pm
by sparrrow
Mark Baker
Code: Select all
SELECT *
FROM php_user
WHERE STR_TO_DATE(CONCAT(YEAR(NOW()),'/',month,'/',day), '%Y/%m/%d') >= now()
AND STR_TO_DATE(CONCAT(YEAR(NOW()),'/',month,'/',day), '%Y/%m/%d') <= ADDDATE(now(), 10)
Mark nailed it. This query should be correct. And I absolutely agree it is much much easier to store your dates inside a single date type column. If you end up needing just a piece of the date, you can pick it apart similar to how YEAR is grabbed for the above query "YEAR(birthday)".