Narendra Verma Narendra Verma - 1 month ago 5
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

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'";
?>
Comments