CODEWITHSUNDEEP
phpmysql
Aaron Turecki
Aaron Turecki

Reputation: 355

php - select from mysql table column between date range

I'm trying to select a number of time items from a table between certain dates but I think I'm having a timestamp/datetime conversion issue.

First of all, the records stored in my log_table MySQL table are stored in the DATETIME data type, henceforth, can I use the strtotime() results as date ranges in my SELECT clause?

Second, can I SELECT from my log_time column between dates in my log_date column without unintentionally selecting my log_date column? As of now, I was returned an empty array.

PHP Script

$date = date("Y-m-d");
$date1 = str_replace('-', '/', $date);
$from_date = date('m-d-Y',strtotime($date1 . "-15 days"));

$date = date("Y-m-d");
$date1 = str_replace('-', '/', $date);
$to_date = date('m-d-Y',strtotime($date1));

$sql2 = "SELECT log_time FROM log_table WHERE log_date >= $from_date AND log_date < $to_date";
$query = $this->db->prepare($sql2);
$query->execute();
$Pro1 = $query->fetchAll();
$Pro1 = array_reverse($Pro1);

print "<pre>";
print_r($from_date);
print "</pre>";

Upvotes: 2

Views: 17660

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You are doing some really weird things in an attempt to get your dates in the right format. For example, why specify a data in format Y-m-d only to turn around in the next line and convert to Y/m/d via string replace? This is very odd.

I would suggest a few things:

First, get familiar with PHP DateTime, DateInterval, and related classes. These are really the most useful tools provided to work with dates.

Second, if your MySQL DB column is a date column, you must use Y-m-d format as that is expected format for this column type.

Example using DateTime:

$date = new DateTime();
$to_date = $date->format('Y-m-d');
$date->modify('-15 days');
$from_date = $date->format('Y-m-d');

That's it. That's all you need to get your two dates in format for DB query.

Third, you need to quote the date string being used in your query. Without this, you will get invalid syntax.

$sql2 = "SELECT log_time FROM log_table WHERE log_date >= '$from_date' AND log_date < '$to_date'";

Fourth, you should actually be checking the results of prepare(), execute(), etc. to make sure you are getting desired behavior and logging the errors so you can understand any problems better.

$query = $this->db->prepare($sql2);
if (false === $query) {
    // something went wrong get error message and log it here
} else {
    $result = $query->execute();
    if (false === $result) {
        // the query failed log an error message
    } else {
        // fetch your data
    }
}

Fifth, since you are going through the trouble of using a prepared statement, you may want to consider using a parametrized prepared statement (it's a good habit) though in this case there is no compelling security reason for it since you control all the query input.

Sixth, why use array_reverse() on the returned array? It should be noted here that array_reverse() does absolutely nothing to guarantee any specific ordering, as your query, as written, does not guarantee any specific ordering. If you want your data in a specific order, just apply the sort in your query using an ORDER BY clause. Currently, you are simply reversing the order of an unordered result set, which is unnecessary. Note: you will need to have an index on all the fields used in the sort in order to optimize the query.

For example:

$sql2 = "SELECT log_time FROM log_table WHERE log_date >= '$from_date' AND log_date < '$to_date' ORDER BY log_date DESC, log_time DESC";

Upvotes: 6

Dan
Dan

Reputation: 9468

Try this:

$from_date = date('Y-m-d H:i:s',strtotime('-15 days'));
$to_date = date('Y-m-d H:i:s');

$sql2 = "SELECT log_time FROM log_table WHERE log_date >=".$from_date." AND log_date <".$to_date;

Upvotes: 1

Related Questions