www.webdeveloper.com
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46

Thread: mysql upcoming birthday query

  1. #1
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197

    mysql upcoming birthday query

    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
    Last edited by kproc; 12-10-2006 at 09:54 AM.

  2. #2
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    below is what I have this far and it retuns nothing any idea why

    PHP Code:
     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;


  3. #3
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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


    PHP Code:
        <?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 <kevin.proctor@familyclick.ca>");

       
    $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
       
     
    }
     
     }
         }

  4. #4
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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

    PHP Code:
    $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()); 
    Last edited by kproc; 12-10-2006 at 08:09 PM.

  5. #5
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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

    PHP Code:
    $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()); 

  6. #6
    Join Date
    Jun 2006
    Posts
    472
    what happens if you use and is there any records in that range?
    PHP Code:
     $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()); 

  7. #7
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    returns nothing

  8. #8
    Join Date
    Apr 2005
    Location
    Bathurst, NSW, Australia
    Posts
    3,357
    Quote Originally Posted by kproc
    ]the date is stored in a column DOB in a mysql table as yyyy-mm-dd
    what field type?

  9. #9
    Join Date
    Jun 2006
    Posts
    472
    do you have records in that range

  10. #10
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    its set ad date, I changed the interval to 366 to make sure and nothing shows.

  11. #11
    Join Date
    Jun 2006
    Posts
    472
    try using ADDDATE instead of DATE_ADD it works for me

  12. #12
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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


    PHP Code:
    $get_childdob mysql_query("SELECT owner_id, childdob FROM children WHERE childdob Between CURDATE() AND ADDDATE (CURDATE(),INTERVAL 20 DAY)")or die (mysql_error()); 

  13. #13
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    date feild type

  14. #14
    Join Date
    Jun 2006
    Posts
    472
    get rid of the space after ADDDATE
    Code:
    mysql> SELECT *
        -> FROM blog
        -> WHERE date_added
        -> BETWEEN CURDATE()
        -> AND ADDDATE(CURDATE() , INTERVAL 20 DAY ) ;
    +---------+-------+-----------------+------------+----------+
    | blog_id | title | message         | date_added | add_name |
    +---------+-------+-----------------+------------+----------+
    |      13 | meee  | eererqerqerqewr | 2006-12-30 | NULL     |
    +---------+-------+-----------------+------------+----------+

  15. #15
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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
    Last edited by kproc; 12-10-2006 at 11:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles