[SOLVED] Dates and Queries

[CodeMama]

I can't get this to work, I need to pull the last 30 days records:

 

:facewall:

   
                                     $pastDays = strtotime("-30 days");

                                    $date = date("m/d/y", $pastDays);
             
                                   $sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM 
restaurants, inspections WHERE restaurants.name != '' AND 
datediff(curdate(),inspections.inDate)>=$date GROUP BY restaurants.ID 
ORDER BY 'name' ";

[TeNDoLLA]

What about using this syntax? Straight from mysql manual date and time functions.. says it will fetch the results from last 30 days.. (also from future but you should not probably have future times in there anyway). IN case you do, you can add one AND there to prevent future dates.

SELECT something FROM tbl_name WHERE DATE_SUB('put_date_here', INTERVAL 30 DAY) <= date_column;

[rhodesa]

SELECT r.ID, r.name, r.address, i.inDate 
  FROM restaurants r, inspections i 
  WHERE r.ID = i.<FIELD WITH RESTAURANT ID>
    AND r.name != ''
    AND DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.inDate
  GROUP BY r.ID, r.name, r.address, i.inDate
  ORDER BY r.name

You need to fill in <FIELD WITH RESTAURANT ID> with the column that holds the restaurant id in the inspections table. Then the rest *should* work.

 

edit: with this method you don't need any of the PHP strtotime() code

[CodeMama]

Nope still can't get it to work tried this:

 

 
                                $pastDays = strtotime("-30 days");
                           $today = getdate();

                           $date = date("y/m/d", $today);
                           
                           

                           $sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM 
restaurants, inspections WHERE restaurants.name != '' AND 
DATE_SUB('$today', INTERVAL 30 DAY) <= inspections.inDate GROUP BY restaurants.ID 
ORDER BY 'name' ";

[CodeMama]

Thanks Rhodesa your way worked for me!!