Searching a database table using MySQL LIKE and PHP prepared statements – Christopher Appleton

To finish off a PHP Wiki I’ve been developing, this week I wrote a very simple database content search, which leverages prepared statements and SQL LIKE.

As is tradition at this point, a nice Paint diagram to get down my thoughts and work through what it is I was trying to achieve: a search bar form, which redirects to the search page with URL parameter ?search= and appended search string, then search the database table’s chosen columns, displaying the results.

Search bar

A little ironically, the last part of the code I wrote was the search bar itself because it’s very straight forward. Wherever a search bar is wanted throughout the website, this basic Bootstrap 5 styled form with Font Awesome icon is all that is required:

<form method="POST" action="/search/process.php">
	<div class="input-group mb-1">
		<input type="text" name="search" required class="form-control" placeholder="Search!">
		<button type="submit" name="submit" class="btn btn-dark">
			<i class="fa-solid fa-magnifying-glass fa-fw" title="Search"></i>
		</button>
	</div>
</form>

This could be easily be stripped back and restyled using any CSS and no icons etc. It’s quite useful setting the input as required as this causes a tooltip error to appear when the search button is pressed if nothing has been entered.

Search process.php

First create, in the root directory, a new directory called search. Within that there will be two files, index.php and process.php. These files can be placed elsewhere, but the action and other path files will need to be updated accordingly.

This small function, upon completing the search bar form, takes the text input and appends it to the URL when directing the user to the search index page.

<?php
if(isset($_POST['submit'])) {
	$search = $_POST['search'];
	header("Location: /search?search=" . $search);
} else {
	header("Location: /");
}
?>

Again, this could be simplified quite considerably by removing the isset and else, but I prefer to put in a couple of extra checks here and there should fall-back or error messaging be wanted later. All that is required is:

<?php header("Location: /search?search=" . $_POST['search']; ?>

Search index.php

I have split this file into two parts here for clarity so we can look at the PHP and HTML separately.

First, the PHP at the top of the file needs to:

GET the search parameter from the URL, convert the string to lowercase and append % to either end of the string
Set up a prepared statement, concat-ting a lowercase set of chosen columns to search
Bind the search parameter to the prepared statement and execute
If there are results, open and close a div.row at the start and finish of results respectively
For each result, create a div.column containing the desired table outputs

The HTML at the bottom of the file need to:

Set up a container and row and other page formatting
Display a search bar in case the user wants to search again
And, most importantly, display the results of the search

PHP

Let’s take this bit by bit; first, we check whether there is a parameter of search, appended to the URL by the process.php, because if there isn’t one then there is no reason to continue.

if (isset($_GET['search'])) {

If there is a search parameter, we need to GET the value of it, convert the string to lowercase and both prepend and append % to it.

$search = '%' . strtolower($_GET['search']) . '%';

Making the search string lowercase helps make comparisons easier, else it would say that E and e are different (which they are), as the SELECT query will also lowercase the database table contents that are being searched. Adding the % to either end is to make the later LIKE find any value which has the search string in any position. The W3Schools page on the SQL LIKE Operator lists other options for this.

The next step is to prepare the SELECT statement. For this minimised example (as my use case joined three tables and several columns from each), we are selecting the two columns title and url from the table table where a lowercase concatenation of the title and content columns are searched for ?. A concatenation is where the columns within the string operator are joined together to form one, and the question mark is going to be bound to a variable as a string literal in a moment.

$stmt = $conn->prepare("SELECT title, url FROM table WHERE lower(CONCAT(title, content)) LIKE ? ORDER BY title");

This can be made more or less complicated by selecting or searching additional columns.

Next is to bind the ? to the $search variable and execute() the statement.

$stmt->bind_param("s", $search);
$stmt->execute();
$result = $stmt->get_result();

If there are results, where the search string matches anywhere within the title or content table columns, a $searchResults variable which the results will appended to is started. The heading and row could be hard coded within the HTML but I wanted to have it only appear if there is something to show.

if ($result->num_rows > 0) {
	$searchResults = '<hr><h2>Results</h2><div class="row row-cols-1 row-cols-md-2 g-4">';

Next is a foreach loop to cycle through the results and create the results their own card link:

foreach($result as $row) {
	$title = $row['title'];
	$url = $row['url'];

	$searchResults .= '<div class="col"><a class="card h-100 text-dark text-decoration-none" href="/' . $url . '"><div class="card-body"><p class="card-title m-0"><i class="fa-solid fa-file-lines fa-fw me-1" aria-hidden="true"></i>' . $title . '</p></div></a></div>';
}

Styling aside, this could be stripped right back to:

foreach($result as $row) {
	$searchResults .= '<a href="' . $row['url'] . '">' . $row['title'] . '</a>';
}

To finish up, close the div.row with a final append to $searchResults and, if desired, set an else for there not being a search parameter in the first place. This could be a message echoed saying there are no results or a redirect etc.

		$searchResults .= '</div>';
	}
} else {
	echo '<p>There are no results, sorry!</p>';
}

?>

The full commented PHP code:

<?php

if (isset($_GET['search'])) {
	// Gets the ?search= from the URL
	// Makes the search parameter lowercase, to compare against the table columns which too will be lower
	// Also prepends and appends % to find any values which have the search parameter in any position
	$search = '%' . strtolower($_GET['search']) . '%';

	// First we check a concat of title and url against the search query, using a prepared statement
	$stmt = $conn->prepare("SELECT title, url FROM table WHERE lower(CONCAT(title, content)) LIKE ? ORDER BY title");
	$stmt->bind_param("s", $search);
	$stmt->execute();
	$result = $stmt->get_result();

	// Only action anything if there are results
	if ($result->num_rows > 0) {
		// Setting up the variable that will be echoed out at the end for search results
		$searchResults = '<hr><h2>Results</h2><div class="row row-cols-1 row-cols-md-2 g-4">';

		// For each result of the search, simply append $search to display
		foreach($result as $row) {
			$title = $row['title'];
			$url = $row['url'];

			$searchResults .= '<div class="col"><a class="card h-100 text-dark text-decoration-none" href="/' . $url . '"><div class="card-body"><p class="card-title m-0"><i class="fa-solid fa-file-lines fa-fw me-1" aria-hidden="true"></i>' . $title . '</p></div></a></div>';
		}

		// Closing the row
		$searchResults .= '</div>';
	}
} else {
	// If people have come to the search page with no parameter, such as via direct link to /search, they end up down here
	// You can just let them enjoy the peace and serenity of no results or redirect them elsewhere
}

?>

HTML

Completely open to layout changes, the only necessary part is to echo the search results to display them.

<?php echo $searchResults; ?>

I’ve decided to include the search bar form here too, should the user want to re-search again and again. The value can be pre-set using GET again, which is a nice touch to show what has been searched and allows the search to be edited instead of written from scratch each time.

value="<?php if(isset($_GET['search'])){echo $_GET['search']; } ?>"

The full HTML code

<section class="container-xxl py-5" style="min-height: 100vh;">
	<div class="row">
		<div class="col-md-3">
			<form method="POST" action="/search/process.php">
				<div class="input-group mb-1">
					<input type="text" name="search" required value="<?php if(isset($_GET['search'])){echo $_GET['search']; } ?>" class="form-control" placeholder="Search!">
					<button type="submit" name="submit" class="btn btn-dark">
						<i class="fa-solid fa-magnifying-glass fa-fw" title="Search"></i>
					</button>
				</div>
			</form>
		</div>

		<div class="col-md-9">
			<h1>Search</h1>
			<p>The following table columns contain your search within their title or content!</p>

			<?php echo $searchResults; ?>
		</div>
	</div>
</section>

What does this look like in practice?

The Shelf Wiki homepage, with some of the navigation expanded on the left hand column. Above this is the search bar.

The search results page of Shelf Wiki, for the search parameter “wiki”. This is visible in the search bar on the left and the results for this search are on the right.

As you can see on the search results page, not all the titles contain the search parameter. These must therefore be results where the string was found in the content column instead, so clicking these links will take the user to the page containing the content.

Also note, it found results for “Wiki”, with a capital, even though the search was lowercase “wiki”. This is because both the string and the queried concatenated columns were lowercase.

My actual use case

A little more complicated, my use case searched three database tables and multiple concatenated columns from each, displaying them in three rows – one for each table. This is why I chose to begin the $searchResults variable with a title and row only if there were results because otherwise there would potentially be two headings with no results under them and results under the third.

These are the search results from Shelf Wiki, with the search results shown on the right hand side under three separate headings, one for each table that was searched.

I did try to do this by using JOIN on the three tables searched, but actually in the end I split it out into three separate prepared SELECT queries. This allowed me to display them neater and also vary what columns are being searched for each. Also, if there are no Chapter results, for example, then the Chapters heading does not appear because it is reliant on there being results.

Useful resources

Here’s some links to the pages I used to help guide the creation of this search: