How to Fetch Data as Date by Date (between two date) Range from MySQL

How to retrieve data from MySQL database based on  date range (from $from_date to $to_date) using PHP and MySQL, This is very simple, But some points have to be setup properly. We will see Step by Step this topics in this article:

Steps 1.
Ensure date format in your database matches the query format, If your date in the database is stored in the YYYY-MM-DD format, then you need to convert the date (DD-MM-YYYY) to match it.

Step 2.
Write a parameterized query to avoid SQL injection, Use prepared statements with MySQL.

Step 3.
Convert dates to the required format, You can use PHP default DateTime class or strtotime to format 02-11-2024 to 2024-11-02.

$from_date_final = DateTime::createFromFormat('d-m-Y', $from_date)->format('Y-m-d');
$to_date_final= DateTime::createFromFormat('d-m-Y', $to_date)->format('Y-m-d');

Example Code:

<?php
// 1- Database connection
$mysqli = new mysqli("localhost", "username", "password", "database");

// 2- Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 3- Set user inputs
$from_date = "02-01-2025";
$to_date = "10-02-2025";

// 4- Convert date format from DD-MM-YYYY to YYYY-MM-DD
$from_date_final = DateTime::createFromFormat('d-m-Y', $from_date)->format('Y-m-d');
$to_date_final= DateTime::createFromFormat('d-m-Y', $to_date)->format('Y-m-d');

// 5- Prepared SQL query
$stmt = $mysqli->prepare("SELECT * FROM transaction_table WHERE dates BETWEEN ? AND ?");
$stmt->bind_param("ss", $from_date_final, $to_date_final);

// 6- Execute query
$stmt->execute();
$result = $stmt->get_result();

// 7- Fetch and display the data for testing
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row['id'] . " - Date: " . $row['dates'] . "- Salary=". $row['salary'] . "<br>";
    }
} else {
    echo "No records found.";
}

// 8- Close the statement & connection
$stmt->close();
$mysqli->close();
?>

Note:
If your date stored as a string (VARCHAR or TEXT), MySQL will perform a lexicographical comparison instead of a proper date comparison, you do not get the correct result because of this mistake.

If the column is not of type DATE or DATETIME, update it using this query:

ALTER TABLE transaction MODIFY COLUMN dates DATE;

Replace transaction_table and dates with your actual table name and column name.

Ensure the date column in your database is of type DATE, DATETIME, or TIMESTAMP.