Welcome Readers, search functionality is the basic and main function that every website must have. Every website provides that functionality for user convenience in accessing the website. Now, we’re also going to learn how to include it in our site.
Creating Database
So, we first need to create a table in a Database to have some data and we will then try to find it. run the following SQL query to create the table. The table contains id, name, email and company name as the columns.
CREATE TABLE IF NOT EXISTS `employee` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(100) NOT NULL, `company` varchar(100) NOT NULL )
Now, add some rows in table as follows.
INSERT INTO `employee` (`id`, `name`, `email`, `company`) VALUES (1, 'Mike', 'mike@softglobe.net', 'Softglobe'), (2, 'James', 'james@gmail.com', 'Google'), (3, 'Mark', 'mark123@microsoft.com', 'Microsoft'), (4, 'Albert', 'albert29@softglobe.net', 'Softglobe'), (5, 'Smith', 'smith@nokia.com', 'Nokia');
Creating front UI
Now our database is ready. we’ll now create an html page where we can display a text box and put the string to find. Lets create file database-search-form.html with following code. It simply provides the front end UI.
<html> <head> <title>Search functionality in dynamic website using php, MySQL | Technopoints</title> <link rel="stylesheet" href="css/w3.css"> </head> <body> <center> <div class="w3-panel w3-pink"> <h1 class="w3-opacity"> <b>Search Functionality using php, MySQL</b></h1> </div> <form action="php-database-search.php" method="post" class="w3-container"> <p> <label>Search Box</label> <input class="w3-input w3-animate-input" type="text" name="search" placeholder="Search name or company... " required="" style="width:30%"></p> <button class="w3-btn w3-blue" type="submit" value="Submit">Search</button> <p> <div class="w3-container w3-blue"> <h4 style="text-shadow:1px 1px 0 #444">A tutorial on <a href="https://technopoints.co.in" target="new">Technopoints</a></h4></p> </div> </form> </center> </body> </html>
We’ve provided the UI using css. You can get it by downloading the source code below.
Go to above created file. You will get the following screen.
The button will not work for now because we’ve not attached the back end. proceed further to add it.
Adding Search Functionality
Create file php-database-search.php and put following piece of code in it. It will act as a back end engine for our project.
<html>
<head>
<title>Search functionality in dynamic website using php, MySQL | Technopoints</title>
<link rel="stylesheet" href="css/w3.css">
</head>
<body>
<center>
<div class="w3-panel w3-pink">
<h1 class="w3-opacity">
<b>Search Functionality using php, MySQL</b></h1>
</div>
<?php
//load database connection
$host = "localhost";
$user = "root";
$password = "";
$database_name = "search";
$pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));
// Search from MySQL database table
$search=$_POST['search'];
$query = $pdo->prepare("select * from employee where name LIKE '%$search%' OR company LIKE '%$search%' LIMIT 0 , 10");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
// Display search result
if (!$query->rowCount() == 0) {
echo "Search found :<br/>";
echo "<table class='w3-table-all'>";
echo "<tr class='w3-red'><td>Employee name</td><td>Email</td><td>Company</td></tr>";
while ($results = $query->fetch()) {
echo "<tr><td>";
echo $results['name'];
echo "</td><td>";
echo $results['email'];
echo "</td><td>";
echo $results['company'];
echo "</td></tr>";
}
echo "</table>";
} else {
echo 'No results found!';
}
?>
<p>
<div class="w3-container w3-blue">
<h4 style="text-shadow:1px 1px 0 #444">A tutorial on <a href="https://technopoints.co.in" target="new">Technopoints</a></h4></p>
</div>
</body>
</html>
That’s it! You are now good to go. Now, try to find by entering the employee name or company name. The matching results will be shown in table.
If you have any doubts, feel free to ask them in comment box below. You can download the source code directly from the following link.
5 thoughts on “Search functionality in dynamic website using php, MySQL”