Superior search with PHP and MySQL

This tutorial assist to create superior search performance with PHP and Mysql. We’ll create a PHP type that takes enter and search into the MySQL desk.

Superior search supplies extra choices to the tip consumer to filter the search consequence.

Steps to implement superior search PHP mysqli

• Create a MySQL database and populate it with information
• Create a search type utilizing PHP.
• Join with the MySQL database.
• Add safety to the MySQL question by placing mysqli_real_escape_string.

Create A Database and MySQL desk

Let’s create a 'check' database within the MySQL server. Create an worker desk within the MySQL database.

--
-- Database: `check`
--

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

--
-- Desk construction for desk `staff`
--

CREATE TABLE `staff` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int(11) NOT NULL,
  `wage` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping information for desk `staff`
--

INSERT INTO `staff` (`id`, `title`, `age`, `wage`, `created_at`, `updated_at`) VALUES
(1, 'Tiger Nixon', 61, 320800, NULL, NULL),
(2, 'Garrett Winters', 63, 170750, NULL, NULL),
(3, 'Ashton Cox', 66, 86000, NULL, NULL),
(4, 'Cedric Kelly', 22, 433060, NULL, NULL),
(5, 'Airi Satou', 33, 162700, NULL, NULL),
(6, 'Brielle Williamson', 61, 372000, NULL, NULL),
(7, 'Herrod Chandler', 59, 137500, NULL, NULL),
(8, 'Rhona Davidson', 55, 327900, NULL, NULL),
(9, 'Colleen Hurst', 39, 205500, NULL, NULL),
(10, 'Sonya Frost', 23, 103600, NULL, NULL),
(11, 'Jena Gaines', 30, 90560, NULL, NULL),
(12, 'Quinn Flynn', 22, 342000, NULL, NULL),
(13, 'Charde Marshall', 36, 470600, NULL, NULL),
(14, 'Haley Kennedy', 43, 313500, NULL, NULL),
(15, 'Tatyana Fitzpatrick', 19, 385750, NULL, NULL),
(16, 'Michael Silva', 66, 198500, NULL, NULL),
(17, 'Paul Byrd', 64, 725000, NULL, NULL),
(18, 'Gloria Little', 59, 237500, NULL, NULL),
(19, 'Bradley Greer', 41, 132000, NULL, NULL),
(20, 'Dai Rios', 35, 217500, NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for desk `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for desk `staff`
--
ALTER TABLE `staff`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=58;
COMMIT;

HTML Type

We’ll create an HTML type to take inputs from the tip consumer and submit type information. I’ve created the title, age, and wage inputs fields to get enter from customers.

<type class="advanced-search-form" methodology="submit" motion="index.php">
    <div class="form-group">
		<label for="">Worker Identify</label>
		<enter kind="title" title="search[name]" worth="<?php echo $title; ?>">
	</div>
	<div class="form-group">
		<label for="">Age</label>
		<enter kind="age" title="search[age]" worth="<?php echo $age; ?>">
	</div>
	<div class="form-group">
		<label for="">Wage</label>
		<enter kind="wage" title="search[salary]" worth="<?php echo $wage; ?>">
	</div>
	<div class="form-group">
		<enter kind="submit" worth="Search">
	</div>
</type>

PHP code to submit information

Let’s create a PHP script that receives code from HTML type inputs and types MySQL question with the superior search situation. The code is:

$conn = mysqli_connect("localhost", "root", "", "check");
$queryCondition = " WHERE ";
if(!empty($_POST["search"])) {
foreach($_POST["search"] as $okay=>$v){
	if(!empty($v)) {
	    $v = mysqli_real_escape_string($v)
		if(!empty($queryCondition)) {
			$queryCondition .= " AND ";
		} else {
			$queryCondition .= " WHERE ";
		}
		
		$queryCondition .= $v ." like '%$v%'";
	}
}
$sql = "SELECT * FROM staff " . $queryCondition;
$consequence = mysqli_query($conn,$sql);