in this post, we are going to learn how to filter data by date using PHP MySQL. We use the filter to get data easily by date. PHP is a server-side scripting language which is loaded with lots of benefits. This language is compatible with different types of platforms and is an open source of language. This language is easily understandable and is free. in this post, we use PHP to filter data by date.
1-Creating Database
- Open PHPMyAdmin in your Browser
- Click on Database Tab Display on Topside
- Give the Database name “filter”.
- After Creating Database Open it.
- Click on SQL Tab on Top area
- Copy the Below Source Code and paste it.
- Then Click on Go.
-- phpMyAdmin SQL Dump -- version 4.8.2 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Oct 21, 2018 at 09:07 PM -- Server version: 10.1.34-MariaDB -- PHP Version: 5.6.37 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `filter` -- -- -------------------------------------------------------- -- -- Table structure for table `product` -- CREATE TABLE `product` ( `product_id` int(11) NOT NULL, `product_name` varchar(50) NOT NULL, `supplier` varchar(50) NOT NULL, `qty` int(11) NOT NULL, `date_released` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `product` -- INSERT INTO `product` (`product_id`, `product_name`, `supplier`, `qty`, `date_released`) VALUES (1, 'Bag', 'Lenova', 50, '2018-09-25'), (2, 'Mobile ', 'MI', 5, '2018-10-22'), (3, 'Band', 'Sumsang', 50, '2018-10-02'), (4, 'Earphone', 'JBL', 100, '2018-10-13'), (5, 'Keyboard', 'HP', 40, '2018-10-15'), (6, 'Mouse', 'Dell', 60, '2018-10-15'); -- -- Indexes for dumped tables -- -- -- Indexes for table `product` -- ALTER TABLE `product` ADD PRIMARY KEY (`product_id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `product` -- ALTER TABLE `product` MODIFY `product_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
OR Import DB File
After Downloading the source code extract it in your root folder.
- Open PHPMyAdmin in your Browser
- Click on Database Tab Display on Top side
- Give the Database name “filter”.
- After Creating Database Open it.
- Click on Import Tab on Top area
- You can Find Db file in Downloaded source code Select it.
- Then Click on Go.
2- Creating Database Connection
After import Database File then next step is creating a database connection using php copy the below code and save it is as “conn.php”.
<?php
$conn = mysqli_connect('localhost', 'root', '', 'filter');
if(!$conn){
die("Error: Failed to connect to database");
}
?>
3 – Create Add Item form and Interface
in this step we are going to create add item form and interface. copy the below code and save it as index.php.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
<title>How To Filter Data By Date using PHP MySQL</title>
<link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
<style>
body{
margin-top:65px;
}
.well{
-webkit-box-shadow: -5px 3px 28px 1px rgba(179,179,179,1);
-moz-box-shadow: -5px 3px 28px 1px rgba(179,179,179,1);
box-shadow: -5px 3px 28px 1px rgba(179,179,179,1);
}
</style>
</head>
<body>
<div class="col-md-3"></div>
<div class="col-md-6 well">
<h3 class="text-success">How To Filter Data By Date using PHP MySQL</h3>
<hr style="border-top:1px solid #ccc;"/>
<button type="button" class="btn btn-success" data-toggle="modal" data-target="#form_modal"><span class="glyphicon glyphicon-plus"></span> Add Item </button>
<br /><br />
<form method="POST" class="form-inline" action="">
<label>Select a Day</label>
<input type="date" class="form-control" name="date" required="required"/>
<button class="btn btn-primary" name="filter"><span class="glyphicon glyphicon-search"></span> Filter</button>
<a href="index.php" class="btn btn-danger"><span class="glyphicon glyphicon-refresh"></span></a>
</form>
<br />
<table class="table table-bordered table-hover">
<thead class="alert alert-success">
<tr>
<th>Item Name</th>
<th>Provider</th>
<th>Qty</th>
<th>Date Released</th>
</tr>
</thead>
<tbody style="background-color:#fff;">
<?php include 'get_date.php'?>
</tbody>
</table>
</div>
<div class="modal fade" id="form_modal" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<form method="POST" action="save_item.php">
<div class="modal-header">
<h4 class="modal-title">Add Item</h4>
</div>
<div class="modal-body">
<div class="col-md-2"></div>
<div class="col-md-8">
<div class="form-group">
<label>Item Name</label>
<input type="text" class="form-control" name="product_name" required="required"/>
</div>
<div class="form-group">
<label>Provider</label>
<input type="text" class="form-control" name="supplier" required="required"/>
</div>
<div class="form-group">
<label>Quantity</label>
<input type="number" class="form-control" name="qty" required="required"/>
</div>
<div class="form-group">
<label>Date Released</label>
<input type="date" class="form-control" name="date_released" required="required"/>
</div>
</div>
</div>
<div style="clear:both;"></div>
<div class="modal-footer">
<button class="btn btn-primary" name="save"><span class="glyphicon glyphicon-save"></span> Save</button>
<button type="button" class="btn btn-danger" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Close</button>
</div>
</form>
</div>
</div>
</div>
<script src="js/jquery-3.2.1.min.js"></script>
<script src="js/bootstrap.js"></script>
</body>
</html>
4 – Add Item Into MySQL Database using PHP
in this step we are going to insert data into mysql database using php. you can simply copy the below source code and save it as save_item.php.
<?php
require_once 'conn.php';
if(ISSET($_POST['save'])){
$product_name = $_POST['product_name'];
$supplier = $_POST['supplier'];
$qty = $_POST['qty'];
$date_released = $_POST['date_released'];
echo $date_released;
mysqli_query($conn, "INSERT INTO `product` VALUES('', '$product_name', '$supplier', '$qty', '$date_released')");
header('location: index.php');
}
?>
5 – Filter & Fetch Data in index Page From MySQL using PHP
in this step we are going to filter and fetch data from MySQL using php in index page. copy and paste below code and save it as get_date.php in your project folder.
<?php
require 'conn.php';
if(ISSET($_POST['filter'])){
$date = $_POST['date'];
$query = mysqli_query($conn, "SELECT * FROM `product` WHERE `date_released` = '$date' ORDER BY `product_name`") or die(mysqli_error());
while($fetch = mysqli_fetch_array($query)){
?>
<tr>
<td><?php echo $fetch['product_name']?></td>
<td><?php echo $fetch['supplier']?></td>
<td><?php echo $fetch['qty']?></td>
<td><?php echo date("F d, Y", strtotime($fetch['date_released']))?></td>
</tr>
<?php
}
}else{
?>
<?php
$query = mysqli_query($conn, "SELECT * FROM `product` ORDER BY `date_released`") or die(mysqli_error());
while($fetch = mysqli_fetch_array($query)){
?>
<tr>
<td><?php echo $fetch['product_name']?></td>
<td><?php echo $fetch['supplier']?></td>
<td><?php echo $fetch['qty']?></td>
<td><?php echo date("F d, Y", strtotime($fetch['date_released']))?></td>
</tr>
<?php
}
?>
<?php
}
?>
If you facing any type of problem with this source code then you can Download the Complete source code in zip Formate by clicking the below button Download Now otherwise you can send Comment.