How can i get data between two dates in php?

I'm using a database to store logs, with a column "date" which holds the date it was inserted. The format of the date is "MM/DD/YY". Please can anyone suggest how I would SELECT data in between two certain dates. For example, I tried this:

$from_date = "01/01/12";
$to_date = "02/11/12";

$result = mysql_query("SELECT * FROM logs WHERE date >= " . $from_date . " AND date <= " . $to_date . " ORDER by id DESC");

while($row = mysql_fetch_array($result)) {
// display results here
}

But I guess this doesn't work because the dates aren't numbers. Thanks for the help! :)

John Saunders

160k26 gold badges239 silver badges393 bronze badges

asked Feb 10, 2012 at 17:31

Joey MoraniJoey Morani

24.2k32 gold badges81 silver badges130 bronze badges

2

Use the BETWEEN keyword:

"SELECT * FROM logs WHERE date BETWEEN '" . $from_date . "' AND  '" . $to_date . "'
ORDER by id DESC"

answered Feb 10, 2012 at 17:34

1

You can cast the fields as dates and then select between from_date and to_date

SELECT * FROM logs WHERE date STR_TO_DATE(date, '%m/%d/%Y') between STR_TO_DATE(from_date, '%m/%d/%Y') and STR_TO_DATE(to_date, '%m/%d/%Y')

answered Feb 10, 2012 at 17:34

BrianBrian

2,18416 silver badges23 bronze badges

0

The answer to your question depends on the data type that is used to store the date field in the logs table.

SQL (MySQL in your case) is fully capable of comparing dates. Usually, the BETWEEN .. AND .. operator is used but that will not work correctly if the type of date is CHAR (or VARCHAR) - in which case you will need to cast the date field to a DATETIME before comparing.

answered Feb 10, 2012 at 17:35

Mike DinescuMike Dinescu

52.5k14 gold badges113 silver badges144 bronze badges

You need to add single quotes to the date values '01/01/12':

$from_date = "01/01/12";
$to_date = "02/11/12";

$result = mysql_query("SELECT * FROM logs WHERE date >= '" . $from_date . "' AND date <= '" . $to_date . "' ORDER by id DESC");

answered Feb 10, 2012 at 17:34

Stelian MateiStelian Matei

11.3k2 gold badges24 silver badges29 bronze badges

Change date parameters into Unix timestamps and then compare them. Here is the code:

$from_date = "2019/01/12";
$to_date = "2019/01/15";

$from_date_unix = strtotime($from_date);
$to_date_unix = strtotime($to_date);    

$result = mysql_query("SELECT * FROM logs WHERE date >= " . $from_date_unix . " AND date <= " . $to_date_unix . " ORDER by id DESC");
while($row = mysql_fetch_array($result)) {
// display results here
}

answered Feb 22, 2019 at 14:03

Submitted by nurhodelta_17 on Monday, January 25, 2021 - 11:47.

This tutorial will show you how to select MySQL rows between two inputted dates. This tutorial does not include a good design but will give you knowledge on the said topic. I've also included 2 MySQLi methods which are Object-oriented and Procedural in the comments. So, feel free to switch between them.

Creating our Database

First, we're going to create a database that contains our data.

1. Open phpMyAdmin.
2. Click databases, create a database and name it as "between".
3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

Inserting Data Into our Database

Next Step in to insert some data into our database. This will serve as our reference when we select our dates.

1. Click the database "between" that we have created earlier.
2. Click SQL and paste the code below.

('nurhodelta', '2020-08-22 07:10:00'),
('lee', '2020-05-22 08:30:00'),
('nurhodelta', '2020-08-22 13:15:00'),
('lee', '2020-03-22 14:00:00'),
('nurhodelta', '2020-05-16 10:30:00'),
('lee', '2020-08-15 20:00:00');

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

//MySQLi Procedural
//$conn = mysqli_connect("localhost","root","","between");
//if (!$conn) {
//  die("Connection failed: " . mysqli_connect_error());
//}
//MySQLi Object-oriented
$conn = new mysqli("localhost","root","","between");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
?>

Creating our Form and Table

Lastly, we create our login table, our form, and our result table on one page. To create the page, open your HTML code editor and paste the code below after the tag. We name this page as "index.php".

Login Table
include('conn.php');
//MySQLi Procedural
//$query=mysqli_query($conn,"select * from `login`");
//while($row=mysqli_fetch_array($query)){
/*	?>
				
					
					
					
				
 
//}
//MySQLi Object-oriented
$query=$conn->query("select * from `login`");
while($row = $query->fetch_array()) {
?>
 
}
?>
UserID
Username
Login Date
 echo $row['logid']; ?>
 echo $row['username']; ?>
 echo $row['login_date']; ?>