MySQL: SELECT all records from today using PHP.

This is a guide on how to select all MySQL records from today (or another specified day). In this tutorial, I will be using PHP’s PDO object to select records from a given date.

For example purposes, I’ve created a MySQL table called posts:

MySQL table structure example.

As you can see, at the end of our posts table, we have a DATETIME column called date_posted. This means that any dates stored in our posts table will look something like this: “2016-08-23 09:40:00”

A few example records:

Results Example MySQL

In the result set above, we have three posts. Two were posted yesterday and one was posted today.

To select all records from today using PHP and the PDO object, we can do the following:

<?php

//Connect to MySQL with the PDO object.
$pdo = new PDO("mysql:host=localhost;dbname=test", 'root', '');

//Today's date in a YYYY-MM-DD format.
$date = date("Y-m-d");

//Our SQL statement.
$sql = "SELECT * FROM posts WHERE DATE(date_posted) = ?";

//Prepare our SQL statement.
$stmt = $pdo->prepare($sql);

//Execute.
$stmt->execute(array($date));

//Fetch the rows from today.
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

//var_dump today's result set
var_dump($rows);

In the PHP code above:

  • We connected to MySQL using the PDO object.
  • We assigned today’s date to our $date variable, which will be used in our SQL query. In PHP, you can get a YYYY-MM-DD date string by providing the date function with the format characters “Y-m-d”.
  • We constructed our SELECT query. In this query, we use MySQL’s DATE() function, which extracts the date segment from a given DATE or DATETIME format. This function basically excludes the Hour, Minutes and Seconds part of our date_posted column.
  • We prepared our SELECT statement.
  • We executed our SELECT statement – providing the $date variable as a parameter.
  • We fetched today’s results using the fetchAll function.
  • Finally, we used var_dump to dump today’s records out onto the page.

If we wanted to show yesterday’s posts, then we can simply change the $date variable to yesterday’s date like so:

//Yesterday's date in a YYYY-MM-DD format.
$date = date("Y-m-d", strtotime("yesterday"));

In the code snippet above, we used the strtotime function to specify yesterday’s UNIX timestamp.