Filter search PREVIEW
Introduction
Hello guys, today we are going to learn how to make an advanced filter search using PHP and MySQL.
So let’s suppose that we have a website that sells safety items of many categories like extinguishers, outfits, equipments etc …
And each category can contain a variety of products, for example, under «extinguishers» we can find «extinguisher ALPHA» and «extinguisher BETA» .
But extinguishers can differ in pressure and volume : those are SPECIFICATIONS.
Let’s imagine a situation where a customer is only intersseted in «extinguishers» so he won’t be looking at outfits nor other equipments , and as previously mentionned, the products of this category have two SPECIFICATIONS the volume and the pressure so the filter looks like :
Please note that «23 L» , «421 Kg » are the VALUES of the SPECIFICATIONS .
This might seem confusing, but don’t worry, as soon as we schematize that in entity association model and in relation model everything will look simple,
then we can create our database .
Entity-relationship model
This is our ER model , so as you can see , every category has many products , for example « extinguishers » has two variants «extinguisher ALPHA»
and «extinguisher BETA» .
Every category has many specifications, but one specification can describe more than one kind of product, regardless to the category, each product must certainly have a price, so price is a common attribute to all categories .
The same for the products : « extinguisher ALPHA » can have 0 to many specifications for example «extinguisher ALPHA» has pressure 20 bar , «extinguisher BETA» has volume 80 L .
Now let’s create our relational model .
Relational model
Now we are ready to create our database .
I’am using wamp server.
So all you need to do is create a database . then you can use one of the following 2 methods .
Method 1: download the file db.sql and then import it to your database .
Method 2: copy this SQL code and paste it .
-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le : sam. 13 juil. 2019 à 12:22
-- Version du serveur : 5.7.24
-- Version de PHP : 7.2.14
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 */;
--
--
-- --------------------------------------------------------
--
-- Structure de la table `categorie`
--
CREATE TABLE IF NOT EXISTS `category` (
`categoryName` varchar(20) NOT NULL,
PRIMARY KEY (`categoryName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `categorie`
--
INSERT INTO `category` (`categoryName`) VALUES
('extinguisher'),
('machines'),
('work clothes');
-- --------------------------------------------------------
--
-- Structure de la table `categoriespecification`
--
CREATE TABLE IF NOT EXISTS `category-specification` (
`categoryName` varchar(20) NOT NULL,
`idSpecification` int(11) NOT NULL,
PRIMARY KEY (`categoryName`,`idSpecification`),
KEY `idSpecification` (`idSpecification`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `categoriespecification`
--
INSERT INTO `category-specification` (`categoryName`, `idSpecification`) VALUES
('extinguisher ', 1),
('extinguisher ', 2),
('extinguisher ', 3),
('extinguisher', 4),
('extinguisher', 6),
('extinguisher', 7),
('machines', 4);
-- --------------------------------------------------------
--
-- Structure de la table `produit`
--
CREATE TABLE IF NOT EXISTS `product` (
`productName` varchar(20) NOT NULL,
`categoryName` varchar(20) NOT NULL,
PRIMARY KEY (`productName`),
KEY `categoryName` (`categoryName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `produit`
--
INSERT INTO `product` (`productName`, `categoryName`) VALUES
('extinguisher ALPHA', 'extinguisher'),
('extinguisher BETA', 'extinguisher'),
('machine X', 'machines'),
('machine Y', 'machines');
-- --------------------------------------------------------
--
-- Structure de la table `produitspecification`
--
CREATE TABLE IF NOT EXISTS `product-specification` (
`productName` varchar(20) NOT NULL,
`idSpecification` int(11) NOT NULL,
KEY `productName` (`productName`),
KEY `idSpecification` (`idSpecification`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `produitspecification`
--
INSERT INTO `product-specification` (`productName`, `idSpecification`) VALUES
('machine Y', 5),
('machine X', 4),
('extinguisher ALPHA', 1),
('extinguisher ALPHA', 3),
('extinguisher BETA', 4),
('extinguisher BETA', 6);
-- --------------------------------------------------------
--
-- Structure de la table `specification`
--
CREATE TABLE IF NOT EXISTS `specification` (
`idSpecification` int(11) NOT NULL AUTO_INCREMENT,
`specificationName` varchar(20) NOT NULL,
`value` varchar(200) NOT NULL,
PRIMARY KEY (`idSpecification`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
--
-- Déchargement des données de la table `specification`
--
INSERT INTO `specification` (`idSpecification`, `specificationName`, `value`) VALUES
(1, 'Pressure', '20'),
(2, 'Pressure', '23'),
(3, 'Volume', '23'),
(4, 'Weight', '421'),
(5, 'Weight', '500'),
(6, 'IA', 'with IA'),
(7, 'IA', 'without IA');
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 */;
Congratulations ! you’re done with the tough part of the tutorial .
All that is left to do is write some PHP code to make our filter ready !
Files hierarchy
methods.php will contain the methods that will ensure the filter to work properly
config.php will contain the necessary methods that will allow our program to access the database
index.php is the first page that contains all the categories, the user will choose one of them
and then be redirected to productPage.php where he will be able to filter the products .
index.php
<!DOCTYPE html>
<html>
<head>
<title>Page Title</title>
</head>
<body>
<?php
include "php/methods.php";
$Categorie=new Methods();
$liste=$Categorie->getCategorie(); // get all categories from the database .
foreach ($liste as $row ) {
?>
<form method="POST" action="productPage.php" id="<?php echo $row['categoryName']; ?>" >
<a href="javascript:{}" onclick="document.getElementById('<?php echo $row['categoryName']; ?>').submit();" ><?php echo $row['categoryName']; ?></a>
<input type="hidden" name="categorie" value="<?php echo $row['categoryName']; ?>" >
</form>
<?php } ?>
</body>
</html>
So this index.php is ready !
We have used the form to send the selected category to the next page « productPage.php » .
productPage.php
<?php if (isset($_POST['categorie'])) { // test if a category has been sent to this page .
include "php/methods.php";
$Categorie=new Methods(); ?>
<!-- Display the filter -->
<form method="POST" action="productPage.php">
<?php
$liste=$Categorie->GETSpecificationCatActuelle($_POST['categorie']); // get all specifications from the database
foreach ($liste as $row ) {
echo "*".$row['x']."<br>"; //display name specification
$liste2=$Categorie->GETValeurOfCurrentSpeci($row['x']); // get the values foreach speacification
$name =$row['x']."[]" ;
foreach ($liste2 as $row2 )
{
$value=$row2['y'] ; echo $value ; // display the value of the specification
?>
<input type="checkbox" name="<?php echo $name; ?>" value="<?php echo $value; ?>">
<?php }
echo "<br>";
}
?>
<input type="submit" name="filter" >
<input type="hidden" name="categorie" value="<?PHP echo $_POST['categorie'];?>">
</form>
<!-- END of Display the filter -->
<!-- Filter precessing -->
<?php
if(isset($_POST['filter']))
{
// save specification names in an array named "Tab"
$Tab = array();
foreach ($_POST as $NameSpeci => $value) {
$Tab[]=$NameSpeci;
}
array_pop($Tab);
array_pop($Tab);
$phraseComplete="";
//save checked values of checkboxes in an array named "TabValue"
for( $i= 0 ; $i < sizeof($Tab) ; $i++ )
{
$TabValue=array();
$nom=$Tab[$i];
foreach($_POST[$nom] as $valueChecked) {$TabValue[]=$valueChecked;}
$liste="(";
for( $j= 0 ; $j < sizeof($TabValue) ; $j++ ){
if ($j+1 ==sizeof($TabValue))
{
$liste.="'".$TabValue[$j]."'" . ")";
}
else{ $liste.="'".$TabValue[$j]."'" ."," ; }
}
// prepare the query
$phrase= "`specificationName`="."'".$Tab[$i]."'"." and `value` in ".$liste ;
if ($i+1==sizeof($Tab) ) {
$phraseComplete.=$phrase;
}
else{
$phraseComplete.=$phrase . " or";
}
}
// test the query
$Test=$Categorie->requetteTest($phraseComplete,$_POST['categorie']);
if ($Test== 1){
$ProduitFiltre=$Categorie->requette($phraseComplete,$_POST['categorie']);
$nbRows=$Categorie->requetteReturnedRows($phraseComplete,$_POST['categorie']);
if ($nbRows>0){// there is products retuned after filtering
foreach ($ProduitFiltre as $rowX )
{ echo "<br>";
echo "<br>";
echo "productName :".$rowX['productName'];
}
}
else { // there is no products retuned after filtering
echo "No products for this filter .";}
}
elseif ($Test== 0) {
//echo "Display all products |cause problem query ";
echo $phraseComplete;
$liste=$Categorie->getProducts($_POST['categorie']);
foreach ($liste as $rowP ) {
echo "<br>";
echo "<br>";
echo "productName :".$rowP['productName'];
}
}
}
else{
//echo " Display all products |cause problem no button pressed ";
$liste=$Categorie->getProducts($_POST['categorie']);
foreach ($liste as $rowP ) {
echo "<br>";
echo "<br>";
echo "productName :".$rowP['productName'];
}
}
}
?>
<!-- END OF Filter precessing -->
methods.php
<?PHP
include "config.php";
class Methods {
function getCategorie(){
$sql="SElECT * From category";
$db = config::getConnexion();
try{
$liste=$db->query($sql);
return $liste;
}
catch (Exception $e){
die('Erreur: '.$e->getMessage());
}
}
function getProducts($cat){
$sql="SElECT * From product where categoryName='$cat'";
$db = config::getConnexion();
try{
$liste=$db->query($sql);
return $liste;
}
catch (Exception $e){
die('Erreur: '.$e->getMessage());
}
}
function getSpecificationCatActuelle($catAct){
$sql="SELECT DISTINCT(`specificationName`) as x FROM specification WHERE idSpecification in (SELECT idSpecification from `category-specification` where categoryName='$catAct')";
$db = config::getConnexion();
try{
$liste=$db->query($sql);
return $liste;
}
catch (Exception $e){
die('Erreur: '.$e->getMessage());
}
}
function getValeurOfCurrentSpeci($currentSpc){
$sql="SELECT DISTINCT(`value`) as y from specification where specificationName='$currentSpc'";
$db = config::getConnexion();
try{
$liste=$db->query($sql);
return $liste;
}
catch (Exception $e){
die('Erreur: '.$e->getMessage());
}
}
function requette($phrase,$nomC){
$sql="SELECT productName from product WHERE( `productName`in( SELECT productName FROM `product-specification` where `idSpecification`in (select idSpecification from specification where (";
$sql.=$phrase;
$sql.=")) ) and `categoryName`='$nomC')";
//echo "***".$sql."****";
$db = config::getConnexion();
try{
$liste=$db->query($sql);
//$type=gettype($liste);
//echo "!!!!".$type."!!!!";
return $liste;
}
catch (Exception $e){
die('Erreur: '.$e->getMessage());
}
}
function requetteTest($phrase,$nomC){
$sql="SELECT productName from product WHERE( `productName`in( SELECT productName FROM `product-specification` where `idSpecification`in (select idSpecification from specification where (";
$sql.=$phrase;
$sql.=")) ) and `categoryName`='$nomC')";
$mysqli = new mysqli("localhost", "root","", "db");
if ($mysqli->query($sql) == TRUE) {
return 1;
}
else
{
return 0;
}
}
function requetteReturnedRows($phrase,$nomC){
$link = mysqli_connect("localhost", "root", "");
mysqli_select_db($link,"db");
$sql="SELECT productName from product WHERE( `productName`in( SELECT productName FROM `product-specification` where `idSpecification`in (select idSpecification from specification where (";
$sql.=$phrase;
$sql.=")) ) and `categoryName`='$nomC')";
$result = mysqli_query($link,$sql);
$num_rows = mysqli_num_rows($result);
return $num_rows;
}
}
?>
config.php
<?php
class config {
private static $instance = NULL;
public static function getConnexion() {
if (!isset(self::$instance)) {
try{
self::$instance = new PDO('mysql:host=localhost;dbname=db', 'root', '');
self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(Exception $e){
die('Erreur: '.$e->getMessage());
}
}
return self::$instance;
}
}
?>
you can download the project files here .
Conclusion
I hope you enjoyed this filter search tutorial , if you need any help please let me know 🙂 .
Create filter seach in PHP and MySQL 