Re: [PHP] Between Query (0T)
* [EMAIL PROTECTED] <[EMAIL PROTECTED]> : > I am not sure > but maybe something like this is posible > > $current_year = date(Y); > $qry = "SELECT * FROM > `table` > WHERE ($current_year-YEAR(birthday)) BETWEEN 25 AND 26" No -- because this incorrectly identifies somebody born on June 14 as 26 even though today is May 9, and they're still 25. > "Matthew Weier O'Phinney" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > * Ryan A <[EMAIL PROTECTED]> : > > > Thanks for replying. > > > > > > > SELECT * FROM > > > > `table` > > > > WHERE `age` BETWEEN 25 AND 26; > > > > > > I knew the above, but how do i use it with my date field when i have > > > birthdates like this: > > > 01-01-1969 > > > and 03-05-1955 -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Between Query (0T)
> -Original Message- > From: Ryan A [mailto:[EMAIL PROTECTED] > Sent: Monday, May 09, 2005 1:02 PM > Luck and me? You dont know who you are talking to my friendI am still > on > mysql 3.23 not even > 4.1 !!! TO_DAYS() works on 3.23 too ;-) -- Med venlig hilsen / best regards ComX Networks A/S Kim Madsen Systemudvikler/Systemdeveloper -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
I am not sure but maybe something like this is posible $current_year = date(Y); $qry = "SELECT * FROM `table` WHERE ($current_year-YEAR(birthday)) BETWEEN 25 AND 26" "Matthew Weier O'Phinney" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >* Ryan A <[EMAIL PROTECTED]> : >> Thanks for replying. >> >> > SELECT * FROM >> > `table` >> > WHERE `age` BETWEEN 25 AND 26; >> >> I knew the above, but how do i use it with my date field when i have >> birthdates like this: >> 01-01-1969 >> and 03-05-1955 > > Just like you would in the example above -- only with dates that are > compliant with your RDBMS, and using the earliest date first. The > formats you give -- 'MM-DD-' won't work in most RDBMS'; typically > you go from most general to most specific, e.g. '-MM-DD'. So, using > the dates you gave: > >SELECT * FROM `table` WHERE `age` BETWEEN '1955-03-05' AND > '1969-01-01'; > > The date fields may be subject to the UNIX epoch; if so, neither of the > dates above will be valid (epoch started 1970-01-01). Check the manual > for your RDBMS to see if this is the case. > > Now, based on the OP's original question, about finding all users > between an age range, one would need to determine the start date and end > date prior to passing in the SQL. This can easily be done with > strtotime(): > >$ages = explode(',', $_GET['ages'], 2); // get the start/end ages >$startDate = date("Y-m-d", strtotime("-$ages[0] years")); >$endDate = date("Y-m-d", strtotime("-$ages[1] years")); >$sql = "SELECT * FROM `table` WHERE `age` BETWEEN $startDate AND > $endDate"; > > Hope that helps. > >> On 5/8/2005 4:28:44 PM, Andy Pieters ([EMAIL PROTECTED]) wrote: >> > On Sunday 08 May 2005 15:20, Ryan A wrote: >> > > Sorry I know this is OT but I'm hoping someone will still >> > > help...it should be quite simple :-) >> > > I have a field in the database called "age" which is a DATE field. >> > > >> > > I also have a webform where the user can select between which ages >> > > he wants the records shown... >> > > eg: if he types 23,25 then I should get all results where >> > > age > =23 and age <=25 >> > > >> > SELECT * FROM >> > `table` >> > WHERE `age` BETWEEN 25 AND 26; >> > >> > You might want to sanitize your input first. >> > >> > Like using intval() on your input or mysql_escape_string > > > -- > Matthew Weier O'Phinney | WEBSITES: > Webmaster and IT Specialist | http://www.garden.org > National Gardening Association| http://www.kidsgardening.com > 802-863-5251 x156 | http://nationalgardenmonth.org > mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
On 5/9/2005 9:47:50 AM, Burhan Khalid ([EMAIL PROTECTED]) wrote: > Ryan A wrote: > > > > > > Your looking at something like. For ages between 21 and 23 > > > > > > $Upper = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-22)); > > > $Lower = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-23)); > > > > > > $sql = > "SELECT field1,field2,field3 FROM `table_name` WHERE `age` > > BETWEEN " . > $Lower . " AND " . $Upper; > > > > > > $result = myqsl_query($sql); > > > > > > etc > > > > > > Cheers > > > Richard > > > > > If you are lucky enough to use MySQL 5 *grin* you can simply do : > > SELECT field1,field2 FROM `sometable` WHERE > TIMESTAMPDIFF(YEAR,NOW(),`age`) = 22 > > to find all 22 year olds. Hey, Luck and me? You dont know who you are talking to my friendI am still on mysql 3.23 not even 4.1 !!! :-D Cheers, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
Ryan A wrote: Your looking at something like. For ages between 21 and 23 $Upper = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-22)); $Lower = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-23)); $sql = "SELECT field1,field2,field3 FROM `table_name` WHERE `age` BETWEEN " . $Lower . " AND " . $Upper; $result = myqsl_query($sql); etc Cheers Richard Hey Rich, Thanks! That is EXACTLY what I was looking for and instead I got around 10 people sending me a link to the mySql manual's BETWEEN syntax :-) If you are lucky enough to use MySQL 5 *grin* you can simply do : SELECT field1,field2 FROM `sometable` WHERE TIMESTAMPDIFF(YEAR,NOW(),`age`) = 22 to find all 22 year olds. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
* Richard Collyer <[EMAIL PROTECTED]> : > Ryan A wrote: > > On 5/8/2005 5:33:46 PM, Richard Collyer ([EMAIL PROTECTED]) wrote: > > > Ryan A wrote: > > > > Sorry I know this is OT but I'm hoping someone will still > > > > help...it should be quite simple :-) > > > > I have a field in the database called "age" which is a DATE field. > > > > I also have a webform where the user can select between which > > > > ages he wants the records shown... > > > > eg: if he types 23,25 then I should get all results where > > > > age > =23 and age <=25 > > > > > > What are you wanting? An sql query or a form? > > > > Thanks for replying. > > > > I have the form without a problem, I dont know how to format the SQL > > query... > > the birthdates in the DB are like this: > > > > 12-01-1979 > > 05-03-7955 > > etc > > Your looking at something like. For ages between 21 and 23 > > $Upper = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-22)); > $Lower = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-23)); Easier than mktime() is strtotime: $upper = date("m-d-Y", strtotime("-22 years")); $lower = date("m-d-Y", strtotime("-23 years")); However... since the OP will be using this in MySQL, use "Y-m-d" as the date format (see the MySQL manual for valid date formats for comparisons). -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
Your looking at something like. For ages between 21 and 23 $Upper = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-22)); $Lower = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-23)); $sql = "SELECT field1,field2,field3 FROM `table_name` WHERE `age` BETWEEN " . $Lower . " AND " . $Upper; $result = myqsl_query($sql); etc Cheers Richard Hey Rich, Thanks! That is EXACTLY what I was looking for and instead I got around 10 people sending me a link to the mySql manual's BETWEEN syntax :-) Thanks mate, it works perfectly. Cheers, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
Ryan A wrote: On 5/8/2005 5:33:46 PM, Richard Collyer ([EMAIL PROTECTED]) wrote: Ryan A wrote: Hi, Sorry I know this is OT but I'm hoping someone will still help...it should be quite simple :-) I have a field in the database called "age" which is a DATE field. I also have a webform where the user can select between which ages he wants the records shown... eg: if he types 23,25 then I should get all results where age >=23 and age <=25 Thanks, Ryan What are you wanting? An sql query or a form? Hey Richard, Thanks for replying. I have the form without a problem, I dont know how to format the SQL query... the birthdates in the DB are like this: 12-01-1979 05-03-7955 etc Thanks, Ryan Your looking at something like. For ages between 21 and 23 $Upper = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-22)); $Lower = date("m-d-Y", mktime(0,0,0,date("m"),date("d"),date("y")-23)); $sql = "SELECT field1,field2,field3 FROM `table_name` WHERE `age` BETWEEN " . $Lower . " AND " . $Upper; $result = myqsl_query($sql); etc Cheers Richard -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
On 5/8/2005 4:31:18 PM, Duncan Hill ([EMAIL PROTECTED]) wrote: > On Sunday 08 May 2005 14:20, Ryan A wrote: > > > Hi, > > > Sorry I know this is OT but > I'm hoping someone will still help...it should > > be quite simple :-) > > I have a field in the database called "age" which is a DATE field. > > http://dev.mysql.com/doc/mysql/en/comparison-operators.html > > 'BETWEEN' > Thanks, But I already know how to use BETWEEN, just dont know how to format it when I am getting 2 numbers like this: 20 66 and query a DATE field Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
On 5/8/2005 5:33:46 PM, Richard Collyer ([EMAIL PROTECTED]) wrote: > Ryan A wrote: > > > Hi, > > > Sorry I know this is OT but I'm hoping someone will still help...it should > > be quite simple :-) > > I have a field in the database called "age" which is a DATE field. > > > > I also have a webform where the user can select between which ages he wants > > the records shown... > > eg: if he types 23,25 then I should get all results where > > age >=23 and age <=25 > > > > Thanks, > > Ryan > > > > > > > What are you wanting? An sql query or a form? Hey Richard, Thanks for replying. I have the form without a problem, I dont know how to format the SQL query... the birthdates in the DB are like this: 12-01-1979 05-03-7955 etc Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
* Ryan A <[EMAIL PROTECTED]> : > Thanks for replying. > > > SELECT * FROM > > `table` > > WHERE `age` BETWEEN 25 AND 26; > > I knew the above, but how do i use it with my date field when i have > birthdates like this: > 01-01-1969 > and 03-05-1955 Just like you would in the example above -- only with dates that are compliant with your RDBMS, and using the earliest date first. The formats you give -- 'MM-DD-' won't work in most RDBMS'; typically you go from most general to most specific, e.g. '-MM-DD'. So, using the dates you gave: SELECT * FROM `table` WHERE `age` BETWEEN '1955-03-05' AND '1969-01-01'; The date fields may be subject to the UNIX epoch; if so, neither of the dates above will be valid (epoch started 1970-01-01). Check the manual for your RDBMS to see if this is the case. Now, based on the OP's original question, about finding all users between an age range, one would need to determine the start date and end date prior to passing in the SQL. This can easily be done with strtotime(): $ages = explode(',', $_GET['ages'], 2); // get the start/end ages $startDate = date("Y-m-d", strtotime("-$ages[0] years")); $endDate = date("Y-m-d", strtotime("-$ages[1] years")); $sql = "SELECT * FROM `table` WHERE `age` BETWEEN $startDate AND $endDate"; Hope that helps. > On 5/8/2005 4:28:44 PM, Andy Pieters ([EMAIL PROTECTED]) wrote: > > On Sunday 08 May 2005 15:20, Ryan A wrote: > > > Sorry I know this is OT but I'm hoping someone will still > > > help...it should be quite simple :-) > > > I have a field in the database called "age" which is a DATE field. > > > > > > I also have a webform where the user can select between which ages > > > he wants the records shown... > > > eg: if he types 23,25 then I should get all results where > > > age > =23 and age <=25 > > > > > SELECT * FROM > > `table` > > WHERE `age` BETWEEN 25 AND 26; > > > > You might want to sanitize your input first. > > > > Like using intval() on your input or mysql_escape_string -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
Hi, On 5/8/05, Ryan A <[EMAIL PROTECTED]> wrote: > Hi, > Sorry I know this is OT but I'm hoping someone will still help...it should > be quite simple :-) > I have a field in the database called "age" which is a DATE field. are u storing in terms of Date of Birth??? if so the below lines u cannot do > > I also have a webform where the user can select between which ages he wants > the records shown... > eg: if he types 23,25 then I should get all results where > age >=23 and age <=25 Instead store the age in a column with int data type. or else you must provide date range using some calendar script > > Thanks, > Ryan > -- bala> balachandar muruganantham blog> lynx http://chandar.blogspot.com web> http://www.chennaishopping.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
Hey, Thanks for replying. > SELECT * FROM > `table` > WHERE `age` BETWEEN 25 AND 26; I knew the above, but how do i use it with my date field when i have birthdates like this: 01-01-1969 and 03-05-1955 Thanks, Ryan On 5/8/2005 4:28:44 PM, Andy Pieters ([EMAIL PROTECTED]) wrote: > On Sunday 08 May 2005 15:20, Ryan A wrote: > > > Hi, > > > Sorry I know this is OT but I'm hoping someone will still help...it should > > be quite simple :-) > > I have a field in the database called "age" which is a DATE field. > > > > I also have a webform where the user can select between which ages he wants > > the records shown... > > eg: if he types 23,25 then I should get all results where > > age >=23 and age <=25 > > > SELECT * FROM > `table` > WHERE `age` BETWEEN 25 AND 26; > > You might want to sanitize your input first. > > Like using intval() on your input or mysql_escape_string > > > Regards > > > Andy > > > Thanks, > > Ryan > > > > > > > > -- > > No virus found in this outgoing message. > > Checked by AVG Anti-Virus. > > Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 > > -- > Registered Linux User Number 379093 > -- --BEGIN GEEK CODE BLOCK- > Version: 3.1 > GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL> -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
On Sunday 08 May 2005 14:20, Ryan A wrote: > Hi, > Sorry I know this is OT but I'm hoping someone will still help...it should > be quite simple :-) > I have a field in the database called "age" which is a DATE field. http://dev.mysql.com/doc/mysql/en/comparison-operators.html 'BETWEEN' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Between Query (0T)
On Sunday 08 May 2005 15:20, Ryan A wrote: > Hi, > Sorry I know this is OT but I'm hoping someone will still help...it should > be quite simple :-) > I have a field in the database called "age" which is a DATE field. > > I also have a webform where the user can select between which ages he wants > the records shown... > eg: if he types 23,25 then I should get all results where > age >=23 and age <=25 > SELECT * FROM `table` WHERE `age` BETWEEN 25 AND 26; You might want to sanitize your input first. Like using intval() on your input or mysql_escape_string Regards Andy > Thanks, > Ryan > > > > -- > No virus found in this outgoing message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- Registered Linux User Number 379093 -- --BEGIN GEEK CODE BLOCK- Version: 3.1 GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL>$ P-(+)>++ L+++>$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++) PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+) e>$@ h++(*) r-->++ y--()> -- ---END GEEK CODE BLOCK-- -- Check out these few php utilities that I released under the GPL2 and that are meant for use with a php cli binary: http://www.vlaamse-kern.com/sas/ -- -- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Between Query (0T)
Hi, Sorry I know this is OT but I'm hoping someone will still help...it should be quite simple :-) I have a field in the database called "age" which is a DATE field. I also have a webform where the user can select between which ages he wants the records shown... eg: if he types 23,25 then I should get all results where age >=23 and age <=25 Thanks, Ryan -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 5/6/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php