How to Search with Auto Suggest in PHP MySQL using JQuery

0 16,080

Hi in this article we are going to discuss about how to search with auto suggest in PHP MySQL using JQuery. when type something in search box then related text appear down as auto suggest. auto suggest textbox is very user friendly for web projects in this post you can learn how to implement Google like search textbox in PHP MySQL using JQuery UI we can easily display auto suggest from the database under Textarea.  Let’s start the search with auto suggest in PHP MySQL training.

How to Search with Auto Suggest in PHP MySQL using JQuery

1-Creating Database

  • Open Phpmyadmin in your Browser
  • Click on Database Tab Display on Top side
  • Give the Database name “search”.
  • 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 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 26, 2015 at 07:43 AM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

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: `search`
--

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `fullname`) VALUES
(25, 'Willie Santos'),
(26, 'Kathryn Bernardo'),
(27, 'Ryan Lopez'),
(28, 'Alexis Diona'),
(29, 'Ryan Rems'),
(30, 'Rene Ramos'),
(31, 'Rosalie Deocampo'),
(32, 'Arnold Schwarzenegger'),
(33, 'Mush Bee'),
(34, 'Liza Wow');

/*!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 “search”.
  • 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 database connection using php copy the below code and save it is as “dbcon.php”.

<?php
$host_name = 'localhost';
$user_name = 'root';
$pass_word = '';
$database_name = 'search';

$conn = mysql_connect($host_name, $user_name, $pass_word) or die ('Error connecting to mysql');
mysql_select_db($database_name);
?>

3- Creating Search Form IN HTML

in this step we are going to create a search box in html.

<form autocomplete="off">
	<input type="text" name="fullname" id="name" style="width:500px;padding:10px;" placeholder="Search Anything here..." />
</form>

4 – Styling The Form and Body Area

Next step is make attractive the search box and body area using CSS .

.ac_results {
	padding: 0;
	border: 1px solid #aaa;
	background-color: gray;
	color:#fff;
	overflow: hidden;
	z-index: 99999;
}

.ac_results ul {
	width: 100%;
	list-style-position: outside;
	list-style: none;
	padding: 0;
	margin: 0;
}

.ac_results li {
	margin: 0px;
	padding: 15px;
	cursor: default;
	display: block;
	font: menu;
	font-size: 12px;
	line-height: 16px;
	overflow: hidden;
	font-size:17px;
}

.ac_over {
	background-color: #fff;
	color: #000;
}

input {
	font-size:20px;
	color:white;
	border:5px solid red;
	background-color: #138808;
	box-shadow:inset 3px 3px 20px #FFFFFF;
}

5 – Retrieve Data From Database using PHP

In this step we are going to fetch data in Search box from Database using PHP.

<?php
include("dbcon.php");
$search = strtolower($_GET["q"]);
if (!$search) return;
$sql = "select DISTINCT fullname as fullname from users where fullname LIKE '%$search%' limit 5";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
	$name = $row['fullname'];
	echo "$name\n";
}
?>

6 – Create Auto Suggest using JQuery UI

In this step we are going to use Jquery UI library file to create auto suggestion when type anything in search box.

<script type="text/javascript">
$().ready(function() {
	$("#name").autocomplete("getresult.php", {
		width: 218,
		matchContains: true,
		selectFirst: false
	});
});
</script>

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.

Download Source Code

Leave A Reply

Your email address will not be published.