English
English
In modern web applications, databases are a critical component. When you need to filter specific information from large amounts of data, full-text search becomes a very useful feature. PHP combined with PDO (PHP Data Objects) offers a simple yet powerful way to perform full-text search in databases. This article explains how to implement full-text search with PHP and PDO, with example code demonstrating the process.
Before using PDO, ensure the database connection is properly configured. This can be done by passing the correct database driver, host, database name, username, and password to the PDO constructor to establish the connection.
The core of full-text search is using the SQL MATCH ... AGAINST statement. It allows searching for records in specified columns that match a particular search expression. The following example demonstrates how to perform a full-text search query using PDO:
$searchTerm = "apple";
<p>// Create and execute full-text search query<br>
$query = "SELECT * FROM products WHERE MATCH(product_name) AGAINST(:searchTerm IN BOOLEAN MODE)";<br>
$stmt = $pdo->prepare($query);<br>
$stmt->bindParam(':searchTerm', $searchTerm, PDO::PARAM_STR);<br>
$stmt->execute();<br>
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);</p>
<p>// Output search results<br>
foreach ($results as $result) {<br>
echo $result['product_name'] . "<br>";<br>
}<br>
In the code, we define the search keyword variable $searchTerm. The SQL query uses bindParam to bind this variable. MATCH(product_name) specifies the column to search, and the AGAINST clause passes the search condition. After execution, the results are output one by one.
The example uses IN BOOLEAN MODE, meaning the search is performed in boolean mode. This mode allows the use of + to indicate required keywords, - to exclude keywords, and the * wildcard for partial matching, making full-text search more flexible and powerful.
Besides full-text search, PDO supports insert, update, delete, and other database operations. Below are examples showing how to perform these operations:
// Insert data
$query = "INSERT INTO products (product_name, price) VALUES (:productName, :price)";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':productName', $productName, PDO::PARAM_STR);
$stmt->bindParam(':price', $price, PDO::PARAM_INT);
$stmt->execute();
<p>// Update data<br>
$query = "UPDATE products SET price = :newPrice WHERE product_name = :productName";<br>
$stmt = $pdo->prepare($query);<br>
$stmt->bindParam(':newPrice', $newPrice, PDO::PARAM_INT);<br>
$stmt->bindParam(':productName', $productName, PDO::PARAM_STR);<br>
$stmt->execute();</p>
<p>// Delete data<br>
$query = "DELETE FROM products WHERE product_name = :productName";<br>
$stmt = $pdo->prepare($query);<br>
$stmt->bindParam(':productName', $productName, PDO::PARAM_STR);<br>
$stmt->execute();<br>
These examples show that PDO provides a unified and straightforward interface for database access, compatible with various database types without the need to switch libraries.
This article provided a detailed guide on how to use PHP and PDO to perform full-text search in databases. We introduced how to build full-text search queries, explained the usage of boolean mode, and provided complete code examples. Hopefully, this will help developers better understand and apply full-text search techniques to improve the efficiency and accuracy of database queries.
