CODEWITHSUNDEEP
phpmysqlpdo
emma
emma

Reputation: 759

PDO fulltext search - get results even if just part of a keyword from a string is in a db column

I am trying to search two columns of a database using this little pdo statement:

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

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

Let's say our string is:

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

(I have this book title and author stored in a db which is using MyISAM as storage engine)

If I search for let's say Har(the first part of Harry keyword) I am able to return the result that I'm looking for but if I type rry(the last part of Harry keyword) I can't get the result that I'm looking for.

And also is there a way to display my desired result if the keyword is misspelled like Harru instead of Harry(using as little php as possible)?

Thank you for your time! :D

Upvotes: 0

Views: 135

Answers (2)

St3an
St3an

Reputation: 806

I'm not aware of performance concerns about that, but have you tried to use regular expressions ?

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

will get both words : Harry, Hurry, Harr, etc... and you can elaborate

Eventually check https://dev.mysql.com/doc/refman/5.6/en/regexp.html

Upvotes: 0

Emanuel Ones
Emanuel Ones

Reputation: 289

Instead of MATCHing it AGAINST you can simply use LIKE(if you want to search in more than one column at once you'll have to bind that $_POST['search'] to more than one parameters but it'll do the job.

Here is your new statement:

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

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

Upvotes: 1

Related Questions