How to implement search by keyword in PHP and MySQL

What is Keyword Search?
Setting Up the MySQL Database
Inserting Sample Data
Connecting to the Database in PHP
Creating the Search Form
Writing the Search Script
Improving the Search
Conclusion

What is Keyword Search?

Keyword search refers to the process of finding records in a database that match one or more keywords provided by the user.

Creating an efficient and user-friendly search functionality is one of the vital features for any dynamic website. In this tutorial, we’ll explore how you can implement a keyword search feature in a web application using PHP and MySQL. This tutorial assumes that you have a basic understanding of PHP and SQL.

Setting Up the MySQL Database

First, you’ll need a MySQL database. We’ll use the following SQL statements to create a database and a table:

CREATE DATABASE demoSearch;
USE demoSearch;
CREATE TABLE articles (
    id INT(11) NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (id)
);

Inserting Sample Data

Before implementing the search feature, let’s insert some sample data into our ‘articles’ table:

INSERT INTO articles (title, content) VALUES 
('Understanding Search Algorithms', 'Content related to search algorithms...'),
('Basics of MYSQL Databases', 'Content related to MySQL databases...');

Connecting to the Database in PHP

To connect to your MySQL database from PHP, you can use the PDO or mysqli extension. For the purpose of this tutorial, we’ll use mysqli:

$conn = new mysqli('localhost', 'username', 'password', 'demoSearch');
if ($conn->connect_error) {
    die('Connection failed: ' . $conn->connect_error);
}

The HTML form will be the interface where users can type the keywords to search:

<form action="search.php" method="get">
    <input type="text" name="keyword" />
    <input type="submit" value="Search" />
</form>

Writing the Search Script

In search.php, you first capture the keyword(s) from the search form using the $_GET global array:

$keyword = isset($_GET['keyword']) ? $_GET['keyword'] : '';
$keyword = $conn->real_escape_string($keyword);

Next, construct the search query using SQL’s LIKE operator to find matches in your articles’ title or content. Using parameter binding in mysqli prevents SQL injection:

$sql = "SELECT * FROM articles WHERE title LIKE ? OR content LIKE ?";
$stmt = $conn->prepare($sql);
$searchTerm = "%{$keyword}%";
$stmt->bind_param('ss', $searchTerm, $searchTerm);

Execute the statement, store the results, and fetch the matching articles:

$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
  echo 'Title: ' . $row['title'] . '<br />';
  echo 'Content: ' . $row['content'] . '<hr />';
}

Improving the Search

To enhance our search functionality, we could add an index to the title and content columns of our ‘articles’ table to make the search faster:

ALTER TABLE articles ADD FULLTEXT(title, content);

Then we can perform a natural language search using MATCH and AGAINST:

$sql = "SELECT *, MATCH(title, content) AGAINST(?) AS score FROM articles WHERE MATCH(title, content) AGAINST(?)";

Remember to handle edge cases, such as no results found, in your PHP script to enhance user experience.

Conclusion

In this tutorial, we have learned how to implement a simple keyword search in PHP and MySQL. We’ve covered creating a search form, capturing user input, querying the database safely using prepared statements, and enhancing search with full-text indexes. While simple, this feature can greatly improve the usability and functionality of your website.

Next Article: How to use SQLite in PHP

Previous Article: How to Implement Pagination in PHP and MySQL

Series: Building Dynamic Web Pages with PHP

PHP

PHP