Re: [PHP] Between Query (0T)
Ryan A wrote: clip 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 /clip 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)
On 5/9/2005 9:47:50 AM, Burhan Khalid ([EMAIL PROTECTED]) wrote: Ryan A wrote: clip 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 /clip 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)
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)
-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)
* [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
[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
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
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)
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)
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)
* 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/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)
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)
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)
clip 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 /clip 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)
* 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
