Narendra Verma Narendra Verma - 1 year ago 42
MySQL Question

How to display records for specific date in php?

I have to display records for specific date.Below Code i tried.I am getting output "0 Results";

$startDate=$_POST['startDate'];//date in format(d-m-y)
$endDate=$_POST['endDate'];//date in format(d-m-y)

include("../db/connection.php") ;

$sql ="SELECT *FROM Emp WHERE empDate BETWEEN $startDate AND $endDate";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["Id"]. " - Name: " . $row["First_name"]. " " . $row["Last_name"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
}


I have records in my database like this

2016-10-03 2:51:35
2016-10-03 2:53:25
2016-10-04 3:52:30
2016-10-04 4:15:55


I founded question on stackoverflow get values from table only for a specific day in mysql but that query is not working for me.Would you help me in this?

Answer Source

First of all you must need to change Date Format as "Y-M-D" by using strtotime():

<?php
$startDate = date("Y-m-d H:i:s",strtotime($_POST['startDate'])); // will return data something like 2016-10-05 00:00:00
$endDate = date("Y-m-d H:i:s",strtotime($_POST['endDate'])); // will return data something like 2016-10-05 00:00:00
?>

Than you need to use single quote around Date value in your SQL Statement:

$sql ="SELECT * FROM Emp WHERE empDate BETWEEN '$startDate' AND '$endDate'";

According to your question, you have date value as:

2016-10-03 2:51:35
2016-10-03 2:53:25
2016-10-04 3:52:30
2016-10-04 4:15:55

In your database, so you must need to use DATE in "Y-M-D" format.


Also note that, your code is open for SQL Injection, you must need to prevent your code with SQL Injection and this reference will help you: How can I prevent SQL injection in PHP?


Update:

In your case, if you are not getting H:i:s from $_POST input than you can use like that:

<?php
$startDate = date("Y-m-d",strtotime($_POST['startDate']));
$endDate = date("Y-m-d",strtotime($_POST['endDate']));
$sql ="SELECT * FROM Emp 
    WHERE empDate 
    BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59'";
?>