0

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

Mickaël Leger
  • 3,426
  • 2
  • 17
  • 36
emma
  • 761
  • 5
  • 20
  • As per https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html, "The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.". Have you tried `array($_POST['search'].'*')` ? – St3an Jul 17 '18 at 09:57
  • N.B. : I edited my comment multiple times, now it's good :-) – St3an Jul 17 '18 at 10:01
  • Hey 'cantStopChangingMyName' thanks for your comment first of all! Yes, i tried `'*' . $_POST['search']` but the problem in this case is that it doesn't return anything unless i enter a full keyword. If i use this `$_POST['search'] . '*'` i can return the whole result but only by using the first part of a keyword :(. And I want to be able to return results with the last part of a keyword aswell – emma Jul 17 '18 at 10:03
  • Well then you simply can’t rely on MySQL’s fulltext search, because it just doesn’t work this way. It is explicitly implemented so that you can use `foo*` to find words starting with `foo`, but the opposite simply hasn’t been implemented. (Likely mainly due to performance considerations.) See also https://stackoverflow.com/questions/20679696/wildcard-search-in-mysql-full-text-search – CBroe Jul 17 '18 at 10:35
  • Possible duplicate of [How do you get leading wildcard full-text searches to work in SQL Server?](https://stackoverflow.com/questions/3400/how-do-you-get-leading-wildcard-full-text-searches-to-work-in-sql-server) – CBroe Jul 17 '18 at 10:37
  • The duplicate has some workarounds, maybe you can adapt one of those. – CBroe Jul 17 '18 at 10:38
  • Does this answer your question? [Wildcard search in MySQL full-text search](https://stackoverflow.com/questions/20679696/wildcard-search-in-mysql-full-text-search) – flaviut Dec 29 '22 at 23:17

2 Answers2

1

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'] . '%'
]);
Emanuel Ones
  • 289
  • 3
  • 9
0

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

St3an
  • 726
  • 1
  • 6
  • 21