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)".