Perform a fulltext search using PDO to retrieve results based on partial matches of keywords within a database column

Question

Perform a fulltext search using PDO to retrieve results based on partial matches of keywords within a database column

I'm attempting to search two columns in a database with this small PDO statement:

$search = $pdo->prepare("
    SELECT * FROM books WHERE MATCH (title, author) AGAINST (? IN BOOLEAN MODE);
");

$search->execute(array('*' . $_POST['search'] . '*'));

For example, if our string is:

Harry Potter and the Philosopher's Stone by J.K Rowling

(This book title and author are stored in a db using MyISAM as storage engine)

If I search for Har (the first part of the Harry keyword), I can find the result I want. However, typing rry (the last part of the Harry keyword) does not return the desired result.

Furthermore, is there a way to display my desired result even when the keyword is misspelled, such as Harru instead of Harry (with minimal use of PHP)?

Thank you for your assistance! :D

php mysql pdo

Answer 1

Answer №1

If you don't want to use MATCH against you, a simple solution is to use LIKE. If you need to search in multiple columns at once, bind the $_POST['search'] to more than one parameter for it to work effectively.

Try this updated code:

$search = $pdo->prepare("
    SELECT * FROM books WHERE title LIKE :title OR author LIKE :author;
");

$search->execute([
    'title' => '%' . $_POST['search'] . '%',
    'author' => '%' . $_POST['search'] . '%'
]);

Answer 2

If you don't want to use MATCH against you, a simple solution is to use LIKE. If you need to search in multiple columns at once, bind the $_POST['search'] to more than one parameter for it to work effectively.

Try this updated code:

$search = $pdo->prepare("
    SELECT * FROM books WHERE title LIKE :title OR author LIKE :author;
");

$search->execute([
    'title' => '%' . $_POST['search'] . '%',
    'author' => '%' . $_POST['search'] . '%'
]);

Answer 3

Answer №2

Regarding performance concerns, I recommend trying out regular expressions as a solution.

SELECT * from books
WHERE title REGEXP "[[:<:]].*rr.*[[:>:]]"
OR author REGEXP "[[:<:]].*rr.*[[:>:]]";

This code snippet will capture words like Harry, Hurry, Harr, etc., giving you room to further refine your search.

If needed, refer to https://dev.mysql.com/doc/refman/5.6/en/regexp.html for more information.

Answer 4

Regarding performance concerns, I recommend trying out regular expressions as a solution.

SELECT * from books
WHERE title REGEXP "[[:<:]].*rr.*[[:>:]]"
OR author REGEXP "[[:<:]].*rr.*[[:>:]]";

This code snippet will capture words like Harry, Hurry, Harr, etc., giving you room to further refine your search.

If needed, refer to https://dev.mysql.com/doc/refman/5.6/en/regexp.html for more information.

Perform a fulltext search using PDO to retrieve results based on partial matches of keywords within a database column

Answer №1

Answer №2

Similar questions

What steps should be taken to mitigate the impact of a SQL-Injection attack?

Can the $_SESSION[balance] be updated automatically?

Run an ajax function when a variable is populated with data

Move and place, editing tools

Utilizing the WordPress the_content filter in the front-page.php template

Getting the raw exception message in Laravel without any HTML formatting can be accomplished by accessing the `

Developing a procedure to manipulate multiple variables

Tips for removing special characters from HTML?

Using Node, Express, and MYSQL to Fetch JSON Data from a MYSQL Database

How can I add content to the body of a modal in Bootstrap 3?

Utilizing PHP functions to eliminate characters beyond a specified phrase with Strip, Trim, and Explode

Transfer information via ajax to yii controller

Enhancing Website Interactivity with PHP, AJAX, and

Is it possible to refer to the same database table in Laravel?

Tips for creating personalized SEO-friendly URLs using .htaccess or PHP

What is the best way to retrieve the parameter of ng2-file-upload using endback?

Is Laravel sanctum token persisting even after being removed in certain routes on a live server?

Determine unique dates from DateTime values in MySQL

Implementing ajax functionality to dynamically insert various user inputs upon a button click

Create an .htaccess RewriteRule that can generate a URL with two parameters in any order