> On Tue, 2009-07-28 at 09:42 -0400, Miller, Terion wrote:
> >
> >
> > On 7/28/09 8:35 AM, "Ashley Sheridan" <ash@xxxxxxxxxxxxxxxxxxxx> wrote:
> >
> > $pastDays = strtotime("-30 days");
> > $date = date("d/m/y", $pastDays);
> >
> > Well I tried and got no results from my query and I know there results with date ranges in the last 30 days, I basically need to count backward from now() 30 days I thought strtotime() would work well..but the fields in the db are varchar not date fields they are all formatted the same though 00/00/00:
> >
> > $sql = " SELECT DISTINCT restaurants.ID, name, address, inDate
>> FROM restaurants, inspections
>> WHERE restaurants.name != '' AND inspections.inDate <= $date
>> GROUP BY restaurants.ID ORDER BY 'name' ";
> >
>
> I believe the query is suspect. From memory, don't you need to enclose
> dates in single quotes in MySQL statements? Also, I believe it uses
> American data format, so you might have to put the month before the day
> like was in Richards example.
Hi,
Instead of using php to work out the 30 days part you could just let the database do it:
$sql = " SELECT DISTINCT restaurants.ID, name, address, inDate
FROM restaurants, inspections
WHERE restaurants.name != '' AND
CAST(inspections.inDate AS DATE) <=
DATE_SUB(NOW(),INTERVAL 30 DAYS)
GROUP BY restaurants.ID ORDER BY 'name' ";
You will have to check that the CAST function is working properly on your varchar'ed date
fields before testing this query. It should point you in the right direction though.
Regards
Ian
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
