Query Data From Database Using Date Range in PHP/MySQL

Submitted by argie on Thursday, February 13, 2014 - 11:14.

This tutorial will teach you on how to query data base on two dates using PHP/MySQL between query. This tutorial is helpful if your are creating your system that needs sales report base on the range of date. To further understand this tutorial follow the steps bellow.

Creating Our Database

First we are going to create our database which stores our data. To create a database: 1. Open phpmyadmin 2. Then create database and name it as "tutorial". 3. After creating a database name, click the SQL and paste the following code.

CREATE TABLE IF NOT EXISTS `birthday` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Creating our Database Connection

Next step is to create a database connection and save it as "connect.php". In this Step, we will write our connection script in PDO format.

<?php
/* Database config */
$db_host                = 'localhost';
$db_user                = 'root';
$db_pass                = '';
$db_database    = 'tutorial'; 
 
/* End config */
 
$db = new PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
?>

Creating Our Display

The code bellow will include our js file because i use tigra calendar for the popup dates in two textbox. This also includes our BETWEEN query that we are going to use to retrieve the data from database using the two dates input. copy the code bellow and save it as "index.php".

<link rel="stylesheet" type="text/css" href="tcal.css" />
<script type="text/javascript" src="tcal.js"></script>
<form action="index.php" method="get">
From : <input type="text" name="d1" class="tcal" value="" /> To: <input type="text" name="d2" class="tcal" value="" /> <input type="submit" value="Search">
</form>
<table id="resultTable" data-responsive="table" style="text-align: left; width: 400px;" border="1" cellspacing="0" cellpadding="4">
<thead>
        <tr>
                <th> Birtday </th>
                <th> Name </th>
                <th> Gender </th>
        </tr>
</thead>
<tbody>
<?php
include('connect.php');
if (isset($_GET["d1"])) { $d1  = $_GET["d1"]; } else { $d1=0; }; 
if (isset($_GET["d2"])) { $d2  = $_GET["d2"]; } else { $d2=0; }; 
$result = $db->prepare("SELECT * FROM birthday WHERE date BETWEEN :a AND :b");
$result->bindParam(':a', $d1);
$result->bindParam(':b', $d2);
$result->execute();
for($i=0; $row = $result->fetch(); $i++){
?>
<tr class="record">
<td><?php echo $row['date']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['gender']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>

Hope this code will help you. The js file can be found the zip attachment.

<link rel="stylesheet" type="text/css" href="tcal.css" />
<script type="text/javascript" src="tcal.js"></script>
<form action="index.php" method="get">
From : <input type="text" name="d1" class="tcal" value="" /> To: <input type="text" name="d2" class="tcal" value="" /> <input type="submit" value="Search">
</form>
<table id="resultTable" data-responsive="table" style="text-align: left; width: 400px;" border="1" cellspacing="0" cellpadding="4">
<thead>
        <tr>
                <th> CASTIG EMH </th>
                        </tr>
</thead>
<tbody>
<?php
include('connect.php');
if (isset($_GET["d1"])) { $d1  = $_GET["d1"]; } else { $d1=0; }; 
if (isset($_GET["d2"])) { $d2  = $_GET["d2"]; } else { $d2=0; }; 
$result = $db->prepare("SELECT *,SUM(Metoda_De_Plata) FROM EMH_rezervari WHERE Data_Si_Ora_Rezervarii BETWEEN :a AND :b");
$result->bindParam(':a', $d1);
$result->bindParam(':b', $d2);
$result->execute();
for($i=0; $row = $result->fetch(); $i++){
?>
<tr class="record">
<td><?php echo $row['SUM(Metoda_De_Plata)']; ?></td>
 
</tr>
<?php
}
?>
</tbody>
</table>