php - select from mysql table column between date range

Question

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>";

Is your query $sql2 shown exactly as is? You need to pass the variables to your query, right now you're passing the text $from_date — Dan
– Dan, Commented Jun 12, 2014 at 15:59
If the DB columns are date columns, you need to use Y-m-d format in your query not m-d-Y. You also need to quote your date values in the query (i.e. '$from_date') — Mike Brant
– Mike Brant, Commented Jun 12, 2014 at 16:01

1234567 · Accepted Answer · 2014-06-12 16:36:43Z

6

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";

edited Jun 12, 2014 at 16:36

1234567

9566 silver badges22 bronze badges

answered Jun 12, 2014 at 16:07

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aaron Turecki Over a year ago

Very nice Mike, thank you for the detailed explanation.

1234567 Over a year ago

+1, good recommendation to the original poster to use the DateTime and DateInterval classes.

Mr. Llama Over a year ago

Using prepared statements... and embedding values? What?

Mike Brant Over a year ago

@Mr.Llama Yes. He is using a prepared statement right now as evidenced by call to prepare() He could go a step further and parametrize the query like SELECT log_time FROM log_table WHERE log_date >= ? AND log_date < ? ORDER BY log_date DESC, log_time DESC (or using named parameters), which is the most common usage when using prepared statements. In this case since he knows the values being used in query are clean there really is no SQL injection concern so he could just as easily use regular query() instead of prepared statement.

Dan · Accepted Answer · 2014-06-12 16:07:32Z

1

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;

edited Jun 12, 2014 at 16:07

answered Jun 12, 2014 at 16:01

Dan

9,4885 gold badges44 silver badges76 bronze badges

Collectives™ on Stack Overflow

php - select from mysql table column between date range

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related