Welcome Guest, Not a member yet? Register   Sign In
find birthday
#1

[eluser]Bigil Michael[/eluser]
i want to find birthdays between two dates

my table structure is

Code:
id dateofbirth  birthday  birthmonth  email

1  2010-08-02    08        02          [email protected]
2  2009-08-02    08        02          [email protected]
3  2001-07-03    07        03          [email protected]

i want to find out birthdays between 2 dates?

if 2 days are like this 2011-02-01 and 2011-02-28 it will show correct result

if i change the month 2011-02-01 and 2011-03-01 it doesnot give correct result?????

query used is
Code:
function list_date_email($d1,$m1,$d2,$m2,$limit,$pgoffset)
    {    
        $result_item = $this->db->query("SELECT id, first_name, email FROM cars_reservation  WHERE birth_day BETWEEN  '$d1' AND '$d2' AND birth_month BETWEEN '$m1' AND '$m2' LIMIT $pgoffset,$limit");
        return $result_item->result();
    }
here d1 =day1 m1=month1 d2=day2 m2=month2

i want to know what is the change required in the query to calculate birthdays between any two dates???
or any other method to find the birthday between two dates????
thanks in advance.....
#2

[eluser]RobertB.[/eluser]
Maybe this help, I don't know why you are making extra columns for dates and months but I don't think that you need them.

This will return the 3 dates
Code:
WHERE dateofbirth >= '2010-08-02' AND dateofbirth <= '2010-08-03'
or
Code:
$this->db->where('dateofbirth >=', '2010-08-02');
$this->db->where('dateofbirth <=', '2010-08-03');

This will return the 1 date
Code:
WHERE dateofbirth > '2010-08-02' AND dateofbirth < '2010-08-03'
or
Code:
$this->db->where('dateofbirth >', '2010-08-02');
$this->db->where('dateofbirth <', '2010-08-03');

hope this is what you're looking for.
#3

[eluser]Bigil Michael[/eluser]
thanks for your reply

but this is not the required result

i want to find out the birthdays

eg: find the persons with birthdays within 2011-02-01 and 2011-02-03

then the result must be like this
Code:
persons with birthdays  within
                        2010-02-01..............2010-02-03
                        2009-02-01..............2009-02-03
                        2008-02-01..............2008-02-03
                         .
                         .
                         .
                         .
                        1950-02-01..............1950-02-03

i think now u got the idea
here year is not important only day and month are important
so i generated birthday and birth month....

if possible please help?? urgent
any other method to calculate the result is also accepted...
#4

[eluser]tomcode[/eluser]
What's about :

Code:
function list_date_email($d1,$m1,$d2,$m2,$limit,$pgoffset)
{    

    $query_string = "SELECT id, first_name, email FROM cars_reservation"
        ." WHERE MONTH(birth_day) BETWEEN '$m1' AND '$m2'"
        ." AND DAY(birth_day) BETWEEN '$d1' AND '$d2'";
        ." LIMIT $pgoffset,$limit";

    $result_item = $this->db->query($query_string);

    return $result_item->result();
}
#5

[eluser]Bigil Michael[/eluser]
thank you .
thank you so much for your help
#6

[eluser]mcrumley[/eluser]
You can't compare the month and day separately. Consider the following date range: 2011-01-01 to 2011-03-01. The day part is always 1, so any date that is not the first day of the month will not match.

You need to compare both month and day together. You could do a string comparison using only the month and day.

Code:
DATE_FORMAT(dateofbirth, "%m-%d") BETWEEN "01-01" AND "03-01".

Another way is to multiply the month by 31 and add the day. That will calculate a unique ordered number for each day of the year, avoiding issues with leap years that come up using day-of-year calculations.

Code:
MONTH(dateofbirth)*31 + DAY(dateofbirth) BETWEEN 1*31+1 AND 3*31+1




Theme © iAndrew 2016 - Forum software by © MyBB