PHP & MySQL - buttons to update display of records 1

[JustKIDn]

Hi all,

O.K. I don't know if this is a PHP or MySQL question, but since I'm doing the code in PHP, I'll start here.

This is my first attempt at doing anything with PHP.

So far so good! I have an address DB in MySQL in two tables. (names & address) This includes Birthdays.

I have a sql statement that pulls all the data and then I display it in a table (html). All that works fine.

Now I want to have links or buttons or maybe even a drop down box to select age groups.

i.e. $result = @mysql_query(&quot;SELECT * FROM names, address WHERE address.famID=names.famID AND (bd >= '1986-01-01' AND bd <= '1988-12-31')&quot;

This will give me all the kids that are 17 and turning 18

But I can't just use this code for this selection. Because next year and the next year it will be wrong if I use static years.

What I would like, is to have a link that says &quot;Kids 17 - 18&quot; and have it find all kids who will be 17 - 18 between Jan 1st and Dec 31st.

I need this for other age groups as well.

Any ideas?

tgus

____________________________
Families can be together forever...

 

[spookie]

make use of MySQL TO_DAYS() function
17years = 17*365 days = 6205
18years = 18*365days = 6570

$sql = &quot;SELECT * FROM names, address WHERE address.famID=names.famID AND (TO_DAYS(CURDATE())-TO_DAYS(bd)) >= 6205 AND (TO_DAYS(CURDATE())-TO_DAYS(bd)) <= 6570&quot; ;

$result = mysql_query($sql) ;

Also have a look at MySQL date and time functions

http://www.mysql.com/doc/en/Date_and_time_functions.html

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[JustKIDn]

Thanks spookie,

That might work. It looks like if I used that, it would go back say 17 years from today. How would I get it to find the dates from Jan 1 through Dec 31?


tgus

____________________________
Families can be together forever...

 

[spookie]

Not understood your query.
MySQL To_DAYS() gives the date from the beginning till the date parameter provided to function TO_DAYS().
so To_DAYS('2003-01-01') - TO_DAYS('2003-12-31') will give you 365 as expected.




--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[JustKIDn]

O.K. I guess maybe I'm confused,

Let's try to work out your code example. Correct me where I'm wrong!
Here is your code example:

$sql = &quot;SELECT * FROM names, address WHERE address.famID=names.famID AND (TO_DAYS(CURDATE())-TO_DAYS(bd)) >= 6205 AND (TO_DAYS(CURDATE())-TO_DAYS(bd)) <= 6570&quot; ;

~ I think I understand TO_DAYS() converts a date into days.
~ I think CURDATE() is todays Date. = 2003-10-05
~ bd is the Birthdate - Let's say bd = 1986-05-14

So (TO_DAYS(CURSHAKE()) would be approx 731854
and then TO_DAYS(bd) would be approx 725505

(TO_DAYS(CURDATE())-TO_DAYS(bd)) should equal approx 6349
6349 does fall between 6205 and 6570 so it would be included.

So again this could work. But instead of CURDATE(),I need to move to the first day of next year (2004-01-01). And then when I subtract 6205 and 6570 it will almost get bact to the first day of the year (1986).

I say almost because this doesn't appear to account for leap year.

What if I used DATESUB() ?
If I could find a way to get next years YEAR and then set a value with:
(YEAR=2004) YEAR-01-01 then maybe I could do;

SUBDATE(YEAR-01-01, INTERVAL 17 Year)

If I could figure out how to do that then leap year wouldn't be a problem.

What do you think? Is this correct?

Thank you for your continued help!

tgus

____________________________
Families can be together forever...

 

[spookie]

U can get the next year by using year() of MySQL.
something like

SELECT * FROM names, address 
WHERE
address.famID=names.famID
AND
(TO_DAYS(YEAR(CURDATE)+1-01-01)-TO_DAYS(bd)) >= 6205 AND (TO_DAYS(YEAR(CURDATE)+1-01-01)-TO_DAYS(bd)) <= 6570

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[JustKIDn]

Thanks,

O.K. that looks easy.

But what about leap years?


tgus

____________________________
Families can be together forever...

 

[spookie]

OK ,

probably using UNIX_TIMESTAMP() will be lot easier.

SELECT * from tablename where FLOOR((UNIX_TIMESTAMP(NOW()) - 
UNIX_TIMESTAMP(db))/60/60/24/365) = 17

if u want to calculate from next year 1st Jan, replace NOW() with appropriate combination of year() and so.ieYEAR(curdate())+1-01-01



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[JustKIDn]

What does /60/60/24/365 mean?

tgus

____________________________
Families can be together forever...

 

[spookie]

I recommend to have a look at date and time functions of MySQL which can be seen here

http://www.mysql.com/doc/en/Date_and_time_functions.html.

There are some usefull information and tips by the users for age calculation.

Hope that will help u

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[JustKIDn]

O.K. So this is what I came up with.
It takes care of Leap Year because I always set the $NextYearBegin to the first of Jan and $NextYearEnd to the last day of Dec.

$sqlbase = "SELECT * FROM names, address WHERE address.famID=names.famID";

$NextYearBegin = DATE("Y") ."-01-01";
$NextYearEnd = DATE("Y") ."-12-31";
...
..
.
$sql = &quot; AND (bd >= DATE_SUB('$NextYearBegin', INTERVAL $agelist YEAR) AND bd <= DATE_SUB('$NextYearEnd', INTERVAL $agelist YEAR))&quot;;
...
..
.
$result = @mysql_query($sqlbase . $sql);

So if $agelist is 13, and $NextYearBegin is 2003-01-01, then I will get a list of everyone who will be 13 turning 14 between next Jan and next Dec.

tgus

____________________________
Families can be together forever...