JQuery AutoComplete MultiSelect in PHP and MySQL

Home » PHP & MySQL » PHP » JQuery AutoComplete MultiSelect in PHP and MySQL
10 Oct, 2018 Categories: PHP


Database

Create a new MySQL database named demo and execute the SQL code below:

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 27, 2017 at 03:53 PM
-- Server version: 5.7.14
-- PHP Version: 7.0.4

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 utf8mb4 */;

--
-- Database: `demo`
--

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `quantity` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`id`, `name`, `price`, `quantity`) VALUES
(21, 'mobile 1', '11', 22),
(20, 'mobile 2', '333', 777),
(18, 'computer 1', '22', 33),
(17, 'computer 2', '11', 22),
(24, 'computer 3', '11', 22),
(25, 'laptop 1', '100', 22),
(26, 'laptop 2', '200', 66),
(27, 'laptop 3', '11', 22),
(28, 'laptop 4', '11', 22);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;
/*!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 */;




Database Connection

Create PHP file named connect.php. Use PDO connect to demo database with default account:


Username: root
Password:

<?php
	$con = new PDO("mysql:host=localhost;dbname=demo", 'root', '');
?>

JSON Data

Create new PHP file named search.php and find the product that contains the keyword you want to find and transform the result into json

<?php
require_once 'database.php';
$stmt = $conn->prepare('select * from product where name like :name');
$stmt->bindValue('name', '%'.$_GET['term'].'%');
$stmt->execute();
$products = array();
while(($product = $stmt->fetch(PDO::FETCH_OBJ))) {
	array_push($products, $product->name);
}
echo json_encode($products);
?>

Search Page

Create new PHP file named index.php. This file will use JQuery UI create autocomplete

<?php require 'database.php'; ?>
<!DOCTYPE html>
<html class="no-js">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <title>Autocomplete Multiselect</title>
    <link href="css/jquerysctipttop.css" rel="stylesheet" type="text/css">
    <link rel="stylesheet" href="css/normalize.min.css" type="text/css">
    <link rel="stylesheet" type="text/css" href="css/ui-lightness/jquery-ui.css" />
    <link rel="stylesheet" href="css/main.css" type="text/css">
    <script src="js/vendor/modernizr-2.6.2.min.js" type="text/javascript"></script>
</head>
<body>
    <div class="wrapper" style="margin-top:150px;">
    	<form method="post">
        	<input id="myAutocomplete" type="text" />
        	<input type="submit" value="Search" name="buttonSearch">
        </form>
        <?php
        	$products = array();
        	if(isset($_POST['buttonSearch'])) {
        		if(isset($_POST['keywords'])) {
	        		foreach($_POST['keywords'] as $keyword) {
	        			$stmt = $conn->prepare('select * from product where name like :name');
						$stmt->bindValue('name', '%'.$keyword.'%');
						$stmt->execute();
	        			while(($product = $stmt->fetch(PDO::FETCH_OBJ))) {
							array_push($products, $product);
						}
	        		}
        		}
        	}
        ?>
        <?php if(count($products) != 0) { ?>
        	<table border="1">
        		<tr>
        			<th>Id</th>
        			<th>Name</th>
        			<th>Price</th>
        			<th>Quantity</th>
        		</tr>
        		<?php foreach ($products as $product) { ?>
        			<tr>
        				<td><?php echo $product->id; ?></td>
        				<td><?php echo $product->name; ?></td>
        				<td><?php echo $product->price; ?></td>
        				<td><?php echo $product->quantity; ?></td>
        			</tr>
        		<?php } ?>
        	</table>
        <?php } ?>
    </div>
    <script src="js/jquery.min.js"></script>
    <script src="js/jquery-ui.min.js"></script>
    <script src="js/jquery.autocomplete.multiselect.js"></script>
    <script type="text/javascript">
        $(function(){

        	function split(val) {
                return val.split(/,\s*/);
            }

            function extractLast(term) {
                return split(term).pop();
            }

            $('#myAutocomplete').autocomplete({
            	source: function (request, response) {
	                $.getJSON('search.php', {
	                    term: extractLast(request.term)
	                }, response);
	            },
	            multiselect: true
            });
        })
    </script>
</body>
</html>




Demo

References

I recommend you refer to the books below to learn more about the knowledge in this article:


Tags

Related Posts

 PHP CRUD with MySQL

October 10, 2018

Top