Last updated on March 7th, 2022 at 03:56 pm
Enabling content search is always very important for a large website especially when you have lot of pages. I am going to explain this in both regular mysqli procedural approach and also using prepared statements. This script also count number of rows returned from the search.
The script below will search a MYSQL database using php and then display the result in very simple HTML table format.
First you need to have a database. In the example below I have a database with the named scripts and table name is script_name
script_name contains 1 column, that has the name of the script for example PHP, jQuery, Javascript, Python, Perl etc.,
Let us start with a simple HTML form with an input field to search. Call it search.html
<form method="get" action="search.php" id="searchform"> <input type="text" name="search"><input type="submit" name="submit" value="Search Now"> </form>
Once the form is created we need to start writing the server side script which will connect to MySQL and display the result.
I am going to explain this in 2 parts
1] Using regular MySQLI (procedural approach)
2] Using MySQLi Prepared Statements (object oriented approach)
Using MySQLi Function
Initial step is to connect to the database, Name it connect.php
<?php
$host = "localhost";
$username = "";
$password = "";
$database = "scripts";
//Make your connection to database
$con = mysqli_connect($host,$username,$password);
//Check your connection
if (!$con) {
die("Could not connect: " . mysqli_error());
}
//Select your database
$db_selected = mysqli_select_db($con,$database);
//Check your connection
if (!$con) {
die("Could not connect: " . mysqli_error());
}
else
{
echo "Connected <p>";
}
?>
The second step is to write a PHP code along with the MySQL query to search the database table which will then populate details and generate an HTML page with results.
Also don’t forget to include the above connection details. Create it as search.php
<?php
include('connect.php');
$table = "script_name";
$num=1;
if(isset($_GET['search'])){
$search=$_GET['search'];
$search_details="SELECT name FROM $table WHERE name LIKE '%$search%'";
$query_search = mysqli_query($con,$search_details);
$numrows=mysqli_num_rows($query_search);
if($numrows < 1)
{
echo "<font color='red'>$search Returned No Results</font>";
exit();
}
else
{
echo "Found $search, ($numrows results)";
}
echo "<table id='users'>
<tr>
<th>NO#</th><th>Details</th></tr>";
while($row = mysqli_fetch_array($query_search)) {
echo "<tr class='alt'>";
echo "<td>" . $num . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "</tr>";
$num++;
}
echo "</table>";
}
else{
echo "You Have Not Searched For Anything";
}
mysql_close($con);
?>
In the script above we are getting the keyword to be searched as a GET parameter and search the mysql table as shown
$search=$_GET['search'];
$search_details="SELECT name FROM $table WHERE name LIKE '%$search%'";
If you would like the exact match then use this query
$search_details="SELECT name FROM $table WHERE name = '$search'";
Using MySQLi Prepared Statements
Lets create a connection page similar to the one we created above but this time we are going to use object oriented style. Name the file con_details_prep.php
<?php
$host = "localhost";
$username = "";
$password = "";
$database = "scripts";
//Make your connection to database
$mysqli = new mysqli($host,$username,$password,$database);
if($mysqli->connect_error) {
exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");
?>
Now let us create a php file with the search logic. Create it as search_prep.php or any other name of your choice make sure HTML form action url is modified accordingly.
<?php
include('con_details_prep.php');
$table = "script_name";
$num=1;
if(isset($_GET['search'])){
$search_raw=$_GET['search'];
$search = "%$search_raw%";
$search_details="SELECT name FROM $table WHERE name LIKE '%$search%'";
$query_search = mysqli_query($con,$search_details);
$stmt = $mysqli->prepare("SELECT name FROM $table WHERE name LIKE ?");
$stmt->bind_param("s", $search);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows < 1)
{
echo "<font color='red'>$search Returned No Results</font>";
exit();
}
else
{
$numrows = $result->num_rows;
echo "Found $search_raw, ($numrows results)";
}
echo "<table id='users' border=1><tr>";
while($row = $result->fetch_assoc()) {
echo "<tr class='alt'>";
echo "<td>" . $num . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "</tr>";
$num++;}
echo "</tr></table>";
$stmt->close();
}
else
{
echo "Search Something";
}
?>
Since we are using LIKE statement in the query we have to implement a little trick here to get it working
$search_raw=$_GET['search'];
$search = "%$search_raw%";
$search_details="SELECT name FROM $table WHERE name LIKE '%$search%'";
admin