Justtechjobs.com Find a programming school near you






Online Campus Both


php-general | 2005051

Re: [PHP] Between Query (0T) From: Matthew Weier O'Phinney (matthew <email protected>)
Date: 05/08/05

* Ryan A <ryan <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-YYYY' won't work in most RDBMS'; typically
you go from most general to most specific, e.g. 'YYYY-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 (mailings <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:matthew <email protected>         | http://vermontbotanical.org

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php