0

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?

Community
  • 1
  • 1

2 Answers2

2

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'";
?>
Community
  • 1
  • 1
devpro
  • 16,184
  • 3
  • 27
  • 38
  • Thanks for reply Mr.devpro, $startDate,$endDate results are showing in 2016-10-05 format, –  Oct 05 '16 at 06:57
  • u need this format, i have updated my answer: `Y-m-d H:i:s` @NarendraVerma – devpro Oct 05 '16 at 06:58
  • i tried your sql query it displayin only starDate records –  Oct 05 '16 at 07:00
  • can u please share the result of `print_r($_POST)` ?? @NarendraVerma – devpro Oct 05 '16 at 07:01
  • I am getting output in array format...Array ( [startDate] => 03-10-2016 [endDate] => 04-10-2016 [submit] => Export ) 2016-10-032016-10-04id: 1 - Name: Nivas Jadhav id: 2 - Name: Javandal Ravindra –  Oct 05 '16 at 07:03
  • And this records are 2016-10-03 –  Oct 05 '16 at 07:04
  • 1
    Yup,That's cool!!!! Your update code is working for me now...Thanks for help Mr.devpro –  Oct 05 '16 at 07:14
  • @NarendraVerma: glad to help u dont forgot to accept the answer this will help to others. – devpro Oct 05 '16 at 07:14
0

Convert date into Standard Format and execute the query

$startDate=Date('Y-m-d',strtotime($startDate));
$endDate=Date('Y-m-d',strtotime($endDate));

After try this query

SELECT *FROM Emp WHERE DATE(empDate) BETWEEN '$startDate' AND '$endDate'
anuraj
  • 157
  • 8