[SOLVED] Dates and Php

[CodeMama]

I am trying to find records within the last 30 days, this isn't working how would you do it?

 

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

                                    $sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM restaurants, inspections WHERE restaurants.name != '' AND inspections.inDate <> $pastDays GROUP BY restaurants.ID ORDER BY 'name' ";

[patrickmvi]

What type of field is inDate?  If it's a DateTime, then you would do something like this:

 

$pastDays = date("Y-m-d H:i:s", strtotime("-30 days"));

$sql = "SELECT DISTINCT restaurants.ID, name, address, inDate FROM restaurants, inspections WHERE restaurants.name != '' AND inspections.inDate > '" . $pastDays . "' GROUP BY restaurants.ID ORDER BY 'name' ";

[gevans]

This should work,

 

<?php
$pastDays = time() - (60*60*24*30);

$sql = "SELECT DISTINCT `restaurants`.`ID`, `name`, `address`, `inDate` 
   FROM `restaurants`, `inspections` 
   WHERE `restaurants`.`name` <> '' 
   AND `inspections`.`inDate` > $pastDays 
   GROUP BY `restaurants`.`ID`
   ORDER BY `name`";

 

Is `inspections`.`inDate` a unix timestamp?

[CodeMama]

ah, I think that's where I screwed up...the field `inDate` is a Varchar and all the dates are 00/00/00 like that because of where the data is being pulled from, so I guess I would have to find out what NOW() is, format that to 00/00/00 formatting and then compare it for a 30 day span...right? is that possible?

[rhodesa]

when dealing with MySQL you should use one of the DATE types. If you feel overwhelmingly compelled to NOT use them, you should at least use a unix timestamp in the varchar or int field. with dates formatted as MM/DD/YY you have to convert them each time you want to use them, which is a resource waste

[CodeMama]

Well this is screen scraped data from the county health site so I just stored them how they had them displayed

[darkfreaks]

Date tutorial

 

hope this helps to change out your date problems. i agree with the guru's here using a plain varchar field is a waste compared to using date()  in php and a DATE field in MYSQL.

[PFMaBiSmAd]

No matter what format you receive the data in, you should convert it to a DATE data type when you insert it into your database.

 

DATE data types use less storage, can be directly sorted and compared in less-than/greater-than comparisons, result in the fastest and simplest queries, and can use the many built-in mysql date/time functions which would make a query to do what you want directly possible.

[vineld]

No matter what format you receive the data in, you should convert it to a DATE data type when you insert it into your database.

 

DATE data types use less storage, can be directly sorted and compared in less-than/greater-than comparisons, result in the fastest and simplest queries, and can use the many built-in mysql date/time functions which would make a query to do what you want directly possible.

 

I definitely agree. Always try to choose relevant data types for your tables before inserting data. That will make your life easier and you will have complete freedom to both search the database and easily format everything in the html output.

 

You will save a LOT of time if you design your database structure carefully, both types, relations and indexes.