mysql upcoming birthday query

@kprocDec 10.2006

HI,

I’m trying to find script to run a query that will list all birthdays within 20 days.

the date is stored in a column DOB in a mysql table as yyyy-mm-dd

any help is great

to post a comment

PHP

45 Comments(s) _↴

@kprocauthorDec 10.2006 — #below is what I have this far and it retuns nothing any idea why

[code=php]
  include '../config/db.php';
 
 $today = strtotime("Now"); 
 $leadDate = strtotime("+350 day", $today);
 
 $get_childdob = mysql_query("SELECT * FROM children WHERE childdob BETWEEN CURDATE() AND '$leaddate'")or die (mysql_error());
 
 while($row_get_childdob = mysql_fetch_assoc($get_childdob)){
 
 $name = $row_get_childdob['childfirstname'];
 echo $name;
 }
 [/code]

@kprocauthorDec 10.2006 — #Hi

here is what I have, but for some reson my query to get the up coming birthdays is not working.

any ideas why.

thank you

[code=php]
  <?php
 
 include ('../config/db.php');
 
 
 $today = strtotime("Now"); 
 $leadDate = strtotime("+150 day", $today);
 
 $get_childdob = mysql_query("SELECT * FROM children WHERE childdob BETWEEN CURDATE() AND '$leadDate'")or die (mysql_error());
 
 $childdob_count =  mysql_num_rows($get_childdob);
 
 while($row_get_childdob=mysql_fetch_assoc($get_childdob)){
 
 $fname = $row_get_childdob['childfirstname'];
 $lname = $row_get_childdob['childlastname'];
 $owner_id = $row_get_childdob['owner_id'];
 $childdob = $row_get_childdob['childdob'];
 echo $name;
 
 if(isset($_POST['childdob_submit'])){
 
 $subject = "Birthday Reminder from Family Click";
 
 $message = " $fname lname birth is on $chiddob. be sure to give them a call or send 
 them a message wishing them a Happy Birthday. 
 
 Visit www.familyclick.ca to get their conact information and check out their gift
 wish list.
 
  This message was sent to you by Family Click
 
 
 
  This is an automated message, please do not reply!";
 
 $x = 1;
 $hold = 50; // quantity of emails sent before 3 sec delay
 
 $query_email_owner = mysql_query("SELECT email_address FROM users WHERE  user_id = '$owner_id'");
 
 $email_count=mysql_num_rows($query_email_owner);
 
 
 while($row_email_owner=mysql_fetch_assoc($query_email_owner)){
 $email_address = $row_email_owner['email_address'];
 
  mail($email_address, $subject,
 $message, "From:FamilyClick.ca <[email protected]>");
 
 $x++;
 if($x == $hold) { // When $x is equal to $hold, a 3 sec delay will occur avoiding php to timeout
 sleep(3);
 $x = 0;
 
 } // end of while loop
 
 }
 
 }
 }
 [/code]

@kprocauthorDec 11.2006 — #Hi

I found this wich kinda works but I need to figure out how to change it to show birthdays within the next 20 days

[code=php]
 $get_childdob = mysql_query("SELECT owner_id,childdob,IF(
 DAYOFYEAR(childdob) < DAYOFYEAR(CURDATE()), DAYOFYEAR(childdob)+366,DAYOFYEAR(childdob)
 )
 as birthdayofyear
 FROM children ORDER BY birthdayofyear")or die (mysql_error());
 
 [/code]

@kprocauthorDec 11.2006 — #I also found this but it return no results

I played around with INTERVAL to make sure there was dates that would result in a positive query

[code=php]
 $get_childdob = mysql_query("SELECT owner_id, childdob FROM children WHERE childdob Between CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 366 DAY)")or die (mysql_error());
 [/code]

@aussie_girlDec 11.2006 — #what happens if you use and is there any records in that range?

[code=php] $get_childdob = mysql_query("SELECT owner_id, childdob FROM children WHERE childdob Between CURDATE() AND DATE_ADD(CURDATE(),INTERVAL 20 DAY)")or die (mysql_error()); [/code]

@kprocauthorDec 11.2006 — #returns nothing

@bathurst_guyDec 11.2006 — #]the date is stored in a column DOB in a mysql table as yyyy-mm-dd[/QUOTE]
what field type?

@aussie_girlDec 11.2006 — #do you have records in that range

@kprocauthorDec 11.2006 — #its set ad date, I changed the interval to 366 to make sure and nothing shows.

@aussie_girlDec 11.2006 — #try using ADDDATE instead of DATE_ADD it works for me

@kprocauthorDec 11.2006 — #I get error message

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 '(CURDATE(),INTERVAL 20 DAY)' at line 1

[code=php]
 $get_childdob = mysql_query("SELECT owner_id, childdob FROM children WHERE childdob Between CURDATE() AND ADDDATE (CURDATE(),INTERVAL 20 DAY)")or die (mysql_error());
 [/code]

@kprocauthorDec 11.2006 — #date feild type

@aussie_girlDec 11.2006 — #get rid of the space after ADDDATE

<i>
 </i>mysql&gt; SELECT *
 -&gt; FROM blog
 -&gt; WHERE date_added
 -&gt; BETWEEN CURDATE()
 -&gt; AND ADDDATE(CURDATE() , INTERVAL 20 DAY ) ;
 +---------+-------+-----------------+------------+----------+
 | blog_id | title | message         | date_added | add_name |
 +---------+-------+-----------------+------------+----------+
 |      13 | meee  | eererqerqerqewr | 2006-12-30 | NULL     |
 +---------+-------+-----------------+------------+----------+

@kprocauthorDec 11.2006 — #The error message is gone but it produces no results. I change the number after interval to 366. so that regarless of the date it should show. the date column is a date of birth so I want it to show 20days before the birth day then after the birthday passess don't show it any more

thank you for the help

@aussie_girlDec 11.2006 — #well it must be your table structure because the query works fine

<i>
 </i>mysql&gt;  SELECT *
 -&gt;  FROM blog
 -&gt;  WHERE date_added
 -&gt;  BETWEEN CURDATE()
 -&gt;  AND ADDDATE(CURDATE() , INTERVAL 20 DAY ) ;
 +---------+------------+-----------------+------------+----------+
 | blog_id | title      | message         | date_added | add_name |
 +---------+------------+-----------------+------------+----------+
 |      13 | meee       | eererqerqerqewr | 2006-12-30 | NULL     |
 |      14 | erqewrqwer | qewrqewrqwerqwe | 2006-12-11 | NULL     |
 |      15 | qewrqwer   | qwerqwer        | 2006-12-12 | NULL     |
 +---------+------------+-----------------+------------+----------+
 3 rows in set (0.00 sec)

@kprocauthorDec 11.2006 — #table field details

name: childdob

type: date

Null: no

default: 0000-00-00

thank you for the help, did you see the post I made at the same time you did

@aussie_girlDec 11.2006 — #You haven't got any dates set in your last post

Showing all dates;

mysql> select date_added from bl

-> order by date_added DESC;

+------------+

| date_added |

+------------+

| 2007-01-01 |

| 2006-12-31 |

| 2006-12-30 |

| 2006-12-12 |

| 2006-12-11 |

| 2006-04-29 |

| 2006-04-29 |

| 2006-03-12 |

| 2006-03-12 |

| 2006-02-23 |

| 2006-02-22 |

| 2006-02-22 |

| 2006-02-21 |

| 2006-02-20 |

| 2006-02-11 |

+------------+

15 rows in set (0.00 sec)

Showing filtered dates

mysql> SELECT date_added

-> FROM blog

-> WHERE date_added

-> BETWEEN CURDATE( )

-> AND ADDDATE( CURDATE( ) , INTERVAL 20

-> DAY ) order by date_added desc;

+------------+

| date_added |

+------------+

| 2006-12-31 |

| 2006-12-30 |

| 2006-12-12 |

| 2006-12-11 |

+------------+

4 rows in set (0.00 sec)

@kprocauthorDec 11.2006 — #this is to work with birthdays,

I have everything set as you do but nothing is produced

I'm calling the results like this

$childdob_count = mysql_num_rows($get_childdob);

then i echo

$childdob_count

and nothing is returned

I just added this date to the table

2006-12-02

which should show

its not working for me ?

@kprocauthorDec 11.2006 — #I got it to return a value but I had to use a date in the future. all my dates are in the past. peoples dates of birth. in your examples all dates are in the future.

i'm worth with birthdates

@aussie_girlDec 11.2006 — #Well this works for me...

[code=php]
 <?php
 require_once('db.connect.php');
 $get_childdob = mysql_query("SELECT date_added FROM blog WHERE date_added BETWEEN CURDATE()
 AND ADDDATE(CURDATE() , INTERVAL 20 DAY) order by date_added desc")or die (mysql_error());
 $num = mysql_num_rows($get_childdob);
 echo "There are " . $num . " birthdays coming up";
 echo '<br>';
 while($row_get_childdob = mysql_fetch_array($get_childdob)){
 
 $name = $row_get_childdob['date_added'];
 echo $name .'<br>';
 } 
 ?>
 [/code]

Results

<i>
 </i>There are 4 birthdays coming up
 2006-12-31
 2006-12-30
 2006-12-12
 2006-12-11

@aussie_girlDec 11.2006 — #AHH you were working on their date of birth? well that's a different thing all together

I got it to return a value but I had to use a date in the future. all my dates are in the past. peoples dates of birth. in your examples all dates are in the future.

i'm worth with birthdates[/QUOTE]

@kprocauthorDec 11.2006 — #yes the ones coming up, but the date in my table is the persons birth year not when their next birthday is.

@aussie_girlDec 11.2006 — #well you can always try CURDATE() - dob see what happens

@kprocauthorDec 11.2006 — #Is there a way that i can use mysql to temp change the date from a table. I was trying to figure out a way to minipulate the the date to the current year

@aussie_girlDec 11.2006 — #you should do a search on these forums, I'm sure there were alot of post about dates and such

@aussie_girlDec 11.2006 — #**deleted**

@theBuDDzDec 11.2006 — #I think i have the codes you were looking for... if you can wait, i will find dat for you...

BuDDz

@kprocauthorDec 11.2006 — #thank you that would be great if you could provide that code

@kprocauthorDec 12.2006 — #There has to be a way to take a date from a table and change the year section of the date to the current year and then compare that date to the current year and display it if the date is within 20 days and once the date is past not to display it any more. I have seached google many times and I have not had any luck getting an answer to this question. I'm trying to limit it to one mysql query.

help please

@theRamonesDec 12.2006 — #

[code=php]
  $timeOfpast20days = mktime(0, 0, 0, date("m")  , date("d")-20, date("Y"));
 $dateOfpast20days = date("Y-m-d" , $timeOfpast20days);
 $query = "select * from tables where date_column < '$dateOfpast20days'";
 [/code]

@kprocauthorDec 12.2006 — #thank you for the reply, the code posted does not get any results, I changed the 20 to any date.

does this take into condiseration that the date is in the passt say 1980

@chazzyDec 12.2006 — #why are you doing this so difficultly?

<i>
 </i>SELECT * from table WHERE datecolumn &lt; DATE_SUB(CURDATE(),INTERVAL 20 DAYS);

This returns all columns in all rows of table where datecolumn is less than (happened before) 20 days ago.

If the issue is still converting datecolumn to this year, use

<i>
 </i>STR_TO_DATE(CONCAT(EXTRACT(DAY from datecolumn),'-',EXTRACT(MONTH from datecolumn),'-',EXTRACT(YEAR from CURDATE()),'%d-%m-%y')

@kprocauthorDec 13.2006 — #so to bring it all to gether I need something like this.

I get error

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 '(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM child

[code=php]
 $get_childdob = mysql_query("SELECT STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob),'-',EXTRACT(YEAR FROM CURDATE()),'%d-%m-%y') as newdate, owner_id FROM children WHERE childdob < DATE_SUB(CURDATE(),INTERVAL 20 DAYS); ")or die (mysql_error()); 

 [/code]

@theRamonesDec 13.2006 — #What is your field data types?????, is date or varchar?

Because i'm sure that one of the code of people try to help is must be true... for date data types, of course

@kprocauthorDec 13.2006 — #I edited my last post and the table field is set to date

@chazzyDec 13.2006 — #kproc,

For one thing, you need your where clause to reference that str_to_date, not the column. I used it as example only.

Second, looks like i probably missed a parenthesis. How are you going to support this code if you didn't see it though?

STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob),'-',EXTRACT(YEAR FROM CURDATE())),'%d-%m-%y') as ThisYearsDate

You can reference "ThisYearsDate" in your where clause.

@kprocauthorDec 13.2006 — #chazzy, I have done reading on each mysql statement you used in your code, I kinda figured out the issue with the reference in the where statement . I added the ) that was missing and I'm still getting the error message that I posted above

"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 '(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM child"

thank you for the help

[code=php]
 $get_childdob = mysql_query("SELECT STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob),'-',EXTRACT(YEAR FROM CURDATE())),'%d-%m-%y') as ThisYearsDate, owner_id FROM children WHERE ThisYearsDate < DATE_SUB(CURDATE(),INTERVAL 20 DAYS)")or die (mysql_error());
 [/code]

@chazzyDec 13.2006 — #try %d-%m-%Y as the format.

@kprocauthorDec 13.2006 — #thats did not change the anything. from what i can see it I think it has something to do with '-'

@chazzyDec 13.2006 — #I don't know what to tell you.

I just tried this query on my local machine and it worked fine.

<i>
 </i>select STR_TO_DATE(CONCAT(EXTRACT(DAY FROM CURDATE()),'-',EXTRACT(MONTH FROM CURDATE()),'-',EXTRACT(YEAR FROM CURDATE())),'%d-%m-%Y') as ThisYearsDate;

Obviously, i used curdate() instead of your date column. [b]Are you positive that this column, childdob, is a date/time type?[/b]

@kprocauthorDec 13.2006 — #It's just date not datetime. I changed it to datetime and that makes no difference

@chazzyDec 13.2006 — #are you using mysql > 4.1?

@kprocauthorDec 13.2006 — #yes,

I have been playing a round with the it and created errors to see how far its reading into the code and its not

("SELECT STR_TO_DATE(CONCAT(EXTRACT(DAY FROM childdob),'-',EXTRACT(MONTH FROM childdob)

@kprocauthorDec 13.2006 — #alright this is wierd.

I just copied your code and changed nothing and I still get the error message

[B]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 '(CONCAT(EXTRACT(DAY FROM CURDATE()),'-',EXTRACT(MONTH FROM CURD[/B]

[code=php]
  $query = "select STR_TO_DATE(CONCAT(EXTRACT(DAY FROM CURDATE()),'-',EXTRACT(MONTH FROM CURDATE()),'-',EXTRACT(YEAR FROM CURDATE())),'%d-%m-%Y') as ThisYearsDate";
 
 $get_childdob = mysql_query($query)or die(mysql_error());
 
 [/code]

@russellDec 13.2006 — #you are just trying to pull up birthdays that will occur in the next 20 days?

check out [URL=http://us2.php.net/datetime]this[/URL] link. there are a couple of functions posted by users that do exactly what u want.

search for "function dateDiff($"

and for "class recur {"

Also in #PHP _↴

[RESOLVED] upload image + create txt file same name How to parse an array of stdClass Objects How to get the OS Type?

Success!

Help @kproc spread the word by sharing this article on Twitter...

Tweet This

mysql upcoming birthday query

45 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

mysql upcoming birthday query

45 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

45 Comments(s) _↴

Also in #PHP _↴