Hello!
I had used http://hibbard.eu/use-ajax-to-filter-mysql-results-set/ to create filters for MySQL data using AJAX. @James_Hibbard
It is all working perfectly fine for the main table of my project.
But My actual Plan is something like this:
There are three tables namely,
- m_tee_casual
- m_tee_casual_color
- m_tee_casual_size
Now the color and size table contain colors and sizes for a particular prod_id. A single product can have any number of colors and any number of sizes. The prod_id in color and size table is foreign key to prod_id in main table.
All the other attributes present in m_tee_casual are being shown in filters and it works perfectly.
But,
- how can i implement filters for color and size as well?
- how to display multiple colours and multiple size for same product in a single row only?
- I have seen some other website, where a small number in front of filter shows how many products of that particular type are present in the result table. How can i implement this?
Following are the four files used:
SQL Dump
-- phpMyAdmin SQL Dump -- version 4.2.11 -- http://www.phpmyadmin.net -- -- Host: 127.0.0.1 -- Generation Time: Dec 30, 2014 at 05:06 AM -- Server version: 5.6.21 -- PHP Version: 5.6.3
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 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 utf8 */;
-- -- Database: `test` --
-- --------------------------------------------------------
-- -- Table structure for table `m_tee_casual` --
CREATE TABLE IF NOT EXISTS `m_tee_casual` ( `prod_id` smallint(5) NOT NULL, `ac_prod_id` varchar(10) DEFAULT NULL, `description` varchar(20) DEFAULT NULL, `brand` varchar(20) NOT NULL, `cost` decimal(7,2) NOT NULL, `rating` smallint(5) NOT NULL, `neck` varchar(20) NOT NULL, `sleeve` varchar(25) NOT NULL, `fit` varchar(15) NOT NULL, `type` varchar(15) NOT NULL, `pattern` varchar(20) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- -- Dumping data for table `m_tee_casual` --
INSERT INTO `m_tee_casual` (`prod_id`, `ac_prod_id`, `description`, `brand`, `cost`, `rating`, `neck`, `sleeve`, `fit`, `type`, `pattern`) VALUES (1, NULL, NULL, 'jack and jones', '359.00', 125, 'stand collar', 'short sleeve', 'slim fit', 'polo t-shirt', 'solid'), (2, NULL, NULL, 'jack and jones', '456.85', 120, 'scoop neck', 'sleeveless', 'regular fit', 't-shirt', 'check'), (3, NULL, NULL, 'puma', '456.00', 50, 'henley', 'three quarter length', 'regular fit', 'polo t-shirt', 'printed');
-- --------------------------------------------------------
-- -- Table structure for table `m_tee_casual_color` --
CREATE TABLE IF NOT EXISTS `m_tee_casual_color` ( `prod_id` smallint(5) NOT NULL, `color` varchar(10) NOT NULL, `image` mediumblob ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- -- Dumping data for table `m_tee_casual_color` --
INSERT INTO `m_tee_casual_color` (`prod_id`, `color`, `image`) VALUES (1, 'black', NULL), (1, 'blue', NULL), (1, 'grey', NULL), (2, 'blue', NULL), (2, 'red', NULL), (3, 'green', NULL), (3, 'red', NULL);
-- --------------------------------------------------------
-- -- Table structure for table `m_tee_casual_size` --
CREATE TABLE IF NOT EXISTS `m_tee_casual_size` ( `prod_id` smallint(5) NOT NULL, `size` varchar(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- -- Dumping data for table `m_tee_casual_size` --
INSERT INTO `m_tee_casual_size` (`prod_id`, `size`) VALUES (1, 'L'), (1, 'S'), (1, 'XL'), (2, 'S'), (2, 'XL'), (3, 'S'), (3, 'XS');
-- -- Indexes for dumped tables --
-- -- Indexes for table `m_tee_casual` -- ALTER TABLE `m_tee_casual` ADD PRIMARY KEY (`prod_id`), ADD UNIQUE KEY `ac_prod_id` (`ac_prod_id`);
-- -- Indexes for table `m_tee_casual_color` -- ALTER TABLE `m_tee_casual_color` ADD UNIQUE KEY `prod_id` (`prod_id`,`color`);
-- -- Indexes for table `m_tee_casual_size` -- ALTER TABLE `m_tee_casual_size` ADD UNIQUE KEY `prod_id` (`prod_id`,`size`);
-- -- AUTO_INCREMENT for dumped tables --
-- -- AUTO_INCREMENT for table `m_tee_casual` -- ALTER TABLE `m_tee_casual` MODIFY `prod_id` smallint(5) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4; -- -- Constraints for dumped tables --
-- -- Constraints for table `m_tee_casual_color` -- ALTER TABLE `m_tee_casual_color` ADD CONSTRAINT `m_tee_casual_color_ibfk_1` FOREIGN KEY (`prod_id`) REFERENCES `m_tee_casual` (`prod_id`);
-- -- Constraints for table `m_tee_casual_size` -- ALTER TABLE `m_tee_casual_size` ADD CONSTRAINT `m_tee_casual_size_ibfk_1` FOREIGN KEY (`prod_id`) REFERENCES `m_tee_casual` (`prod_id`);
/*!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 */;
config.php
<?php error_reporting(E_ERROR | E_PARSE);
$db_username = 'root'; $db_password = ''; $db_name = 'test'; $db_host = 'localhost'; $mysqli = new mysqli($db_host, $db_username, $db_password,$db_name);
session_start();
mysql_connect('localhost', 'root', '') or trigger_error("Unable to connect to the database: " . mysql_error()); mysql_select_db('test') or trigger_error("Unable to switch to the database: " . mysql_error());
// default the error variable to empty. $_SESSION['error'] = ""; ?>
ajax_check.php (main file)
<?php include_once('config.php'); $table_name="m_tee_casual"; ?>
<!DOCTYPE HTML> <html> <head> <meta charset="utf-8"> <style> body { padding: 10px; } </style>
</head> <body> <table id="Products"> <thead> <tr> <th>Product ID</th> <th>Actual Product Id</th> <th>Description</th> <th>Brand</th> <th>Cost</th> <th>Rating</th> <th>Neck</th> <th>Sleeve</th> <th>Fit</th> <th>Type</th> <th>Pattern</th> </tr> </thead> <tbody> </tbody> </table>
<div id="test"> </div>
<div id="filter"> <h2>Filter options</h2> <?php
$sql=$mysqli->query("SHOW COLUMNS from $table_name where Field NOT LIKE 'prod_id' AND Field NOT LIKE 'rating' AND Field NOT LIKE 'description' AND Field NOT LIKE 'ac_prod_id' AND Field NOT LIKE 'cost'"); while($obj=$sql->fetch_object()) { //$filter_id=$obj->Field; echo "<b>{$obj->Field}</b>:<br/>"; $filters = $mysqli->query("SELECT distinct {$obj->Field} FROM $table_name"); while($obj1 = $filters->fetch_object()) { //$filter_name=$obj1->{$obj->Field}; echo "<input type='checkbox' id='{$obj->Field}' name='{$obj1->{$obj->Field}}'>{$obj1->{$obj->Field}}<br/>"; }
echo "<br/>";
} ?>
</div>
<script src="http://code.jquery.com/jquery-latest.js"></script> <script> var opts; var main_opts;
function makeTable(data){ var tbl_body = ""; $.each(data, function() { var tbl_row = ""; $.each(this, function(k , v) { tbl_row += "<td>"+v+"</td>"; }) tbl_body += "<tr>"+tbl_row+"</tr>"; }) return tbl_body; }
function getFilterOptions(){ opts = []; main_opts = []; $checkboxes.each(function(){ if(this.checked){ main_opts.push(this.id); opts.push(this.name); } }); }
function update(){ $.ajax({ type: "POST", url: "submit.php", dataType : 'json', cache: false, data: {filterOpts: opts,filterMainOpts: main_opts,tableName: "m_tee_casual"}, success: function(data){ $('#Products tbody').html(makeTable(data)); } }); }
var $checkboxes = $("input:checkbox"); $checkboxes.on("change", function(){ getFilterOptions(); update(); });
update(); </script> </body> </html>
submit.php
<?php include_once('config.php'); global $mysqli;
$select = 'SELECT *'; $from = " From ".$_POST['tableName']; $where = ' WHERE TRUE'; $orderby = ' ORDER BY rating desc'; $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array(''); $main_opts = isset($_POST['filterMainOpts'])? $_POST['filterMainOpts'] : array(''); $sql = "SHOW COLUMNS ".$from." where Field NOT LIKE 'prod_id' AND Field NOT LIKE 'rating' AND Field NOT LIKE 'description' AND Field NOT LIKE 'ac_prod_id' AND Field NOT LIKE 'cost'";
$colname=$mysqli->query($sql);
while($obj = $colname->fetch_object()) { if (in_array("$obj->Field", $main_opts)) { $where .=" AND ( "; $field=$obj->Field; $filters = $mysqli->query("SELECT distinct $field".$from); while($objfilter = $filters->fetch_object()) { if (in_array("{$objfilter->$field}", $opts)) { $where .=" $obj->Field like '{$objfilter->$field}' OR "; } } $where .=" FALSE) "; } }
$sql = $select . $from . $from_colsize . $where. $orderby; $statement = $mysqli->query($sql); $results = $statement->fetch_all(MYSQLI_ASSOC); $json = json_encode($results); echo ($json); ?>
Thanks a lot in advance! 
Also, This is my first post on this website! Please excuse me for any mistake in the post!