Finding Birth days dew in next 10days

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Finding Birth days dew in next 10days

Post 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
sparrrow
Forum Commoner
Posts: 81
Joined: Mon Oct 20, 2008 12:22 pm

Re: Finding Birth days dew in next 10days

Post 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)
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Finding Birth days dew in next 10days

Post 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
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Finding Birth days dew in next 10days

Post 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)
 
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Finding Birth days dew in next 10days

Post 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
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Finding Birth days dew in next 10days

Post 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
arunkar
Forum Commoner
Posts: 50
Joined: Mon Feb 25, 2008 10:37 pm

Re: Finding Birth days dew in next 10days

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Finding Birth days dew in next 10days

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
sparrrow
Forum Commoner
Posts: 81
Joined: Mon Oct 20, 2008 12:22 pm

Re: Finding Birth days dew in next 10days

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