In web development, the ability to search through a database efficiently is a crucial functionality. Whether you’re building a simple blog or a complex e-commerce platform, implementing a search feature can greatly enhance user experience. In this tutorial you will learn how to perform a search in PHP MySQL, from setting up the database connection to executing search queries and displaying results.
Setting Up the Database Connection:
Firstly, ensure you have access to a MySQL database. You’ll need the database hostname, username, password, and database name to establish a connection. Here’s how you can set up the connection in PHP:
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>Replace “localhost”, “your_username”, “your_password”, and “your_database” with your actual MySQL credentials.
Creating the Search Form:
Next, create a search form where users can input their search queries. This form will submit the search query to a PHP script for processing. Here’s a simple example:
<form method="post" action="search.php">
<input type="text" name="search" placeholder="Search...">
<input type="submit" value="Search">
</form>Handling the Search Query:
Now, let’s handle the search query submitted by the user. In the PHP script (“search.php”), retrieve the search term from the form and execute a MySQL query to fetch relevant results:
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Get the search term from the form
$search = $_POST['search'];
// SQL query to search for records that match the search term
$sql = "SELECT * FROM your_table WHERE column_name LIKE '%$search%'";
// Execute the query
$result = $conn->query($sql);
// Check if any results were found
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
}
} else {
echo "No results found";
}
}
?>Replace “your_table” with the name of your table and “column_name” with the name of the column you want to search in.
In this code we have used LIKE operator, this operator in SQL is used to search for a specified pattern in a column. It’s commonly used when you want to retrieve rows based on partial string matches. Here’s a tutorial on how to use the LIKE operator in SQL:
The basic syntax of the LIKE operator is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;The LIKE operator supports two wildcard characters:
- % (percent sign): Represents zero, one, or multiple characters.
- _ (underscore): Represents a single character.
1. Matching a Specific Pattern: The following query will retrieve all rows from the employees table where the first_name column is exactly ‘John’.
SELECT *
FROM employees
WHERE first_name LIKE 'John';2. Matching a Pattern with Wildcards: The following query will retrieve all rows from the products table where the product_name column contains the word ‘apple’ anywhere within the string.
SELECT *
FROM products
WHERE product_name LIKE '%apple%';3. Matching a Single Character: The following query will retrieve all rows from the customers table where the phone_number column starts with ‘5551’ followed by any single character and then zero or more characters.
SELECT *
FROM customers
WHERE phone_number LIKE '5551_3%';The Full-Text Search:
Full-text search is a technique that enables searching through textual data stored in a database. Unlike simple searches using the LIKE operator, full-text search provides more advanced capabilities, including stemming, relevance ranking, and support for natural language queries. This makes it ideal for applications where users need to search for words or phrases within large volumes of text.
Configure Full-Text Indexing:
Before you can perform a full-text search, you need to ensure that the columns you want to search are indexed appropriately. You can add a full-text index to your table columns using the FULLTEXT index type.
For example, let’s say you have a table named articles with a column content that you want to enable full-text search on:
ALTER TABLE articles ADD FULLTEXT(content);Perform Full-Text Search Query:
Once you’ve configured the full-text index, you can execute full-text search queries using the MATCH() and AGAINST() operators in your SQL queries.
Here’s an example PHP script (fulltext_search.php) to perform a full-text search:
<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Get the search term from the form
$search = $_POST['search'];
// SQL query to perform full-text search
$sql = "SELECT * FROM articles WHERE MATCH(content) AGAINST('$search' IN BOOLEAN MODE)";
// Execute the query
$result = $conn->query($sql);
// Check if any results were found
if ($result->num_rows > 0) {
// Output data of each row
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Content: " . $row["content"] . "<br>";
}
} else {
echo "No results found";
}
}In the above code you can see IN BOOLEAN MODE, this modifier is used in conjunction with the MATCH() and AGAINST() operators to perform full-text searches with Boolean logic. When you use AGAINST() with IN BOOLEAN MODE, MySQL treats the search string as a Boolean expression rather than a simple string of words.
Here’s a breakdown of how Boolean mode works:
Boolean Operators:
- AND: Returns results that contain all the specified words.
- OR: Returns results that contain at least one of the specified words.
- NOT: Excludes results that contain the specified word.
Query Expansion: MySQL automatically expands search terms to include related words. For example, searching for “run” might also include results for “running,” “runs,” etc.
Special Characters: Certain characters have special meanings in Boolean mode:
- + (plus): Indicates that a word is required to be present in every result.
- – (minus): Indicates that a word must not be present in any result.
- > (greater-than): Increases the weight of a word in the search results.
- < (less-than): Decreases the weight of a word in the search results.
Quotation Marks: Enclosing a phrase in double quotes (“”) ensures that the words within the quotes are treated as a single phrase and are searched together.