Multiple word search in MySQL with PHP

Question

I'm facing a issue trying to search with multiple words from my text-box but I cant fix it. The code below is not working, but it works with search LIKE '%$keywords%'.

Please see and if you can help me with any solution!

 $search = $_POST['search'];
 $search = trim($search);
 $search = preg_replace('/\s+/', ' ', $kerko);
 $keywords = explode(" ", $kerko);

 $sql = $conn->prepare("SELECT * FROM weblinks WHERE MATCH(post) AGAINST('$keywords')");


 $sql->execute();
 while($row = $sql->fetch(PDO::FETCH_ASSOC)){    
 ?>

the page is empty, nothing returns, but with LIKE '%$keywords%' returns normally what LIKE can do — Jacky, Feb 20 '17 at 18:30
Ok, what's the expected output ? Are each keywords required ? — JazZ, Feb 20 '17 at 19:19
Give an example of `$keywords` for which it fails. Then read about the caveats of what you can put in `AGAINST`. — Rick James, Feb 20 '17 at 23:29

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

You'll have to pass a string to the MATCH AGAINST function.

Also, to make sure the post contains every words, like "LIKE" you'll have to add "+" sign at the beginning of the required words.

$search = $_POST['search'];
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $kerko);
$keywords = explode(" ", $kerko); // create array of keywords

// here we build the string to require all keywords
$keywds_str = "+" . implode(" +", $keywords);

And then the SQL query :

$sql = $conn->prepare("SELECT * FROM weblinks WHERE MATCH(post) AGAINST ('".$keywds_str."')");

please note the string concatenation too. In your example, the query had to look for "$keywords" literally.

Last point, enable php error reporting to know what's wrong in your code. A blank page will not help you much.

Hope it helps and good luck.

EDIT :

Since the error message :

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error:

1191 Can't find FULLTEXT index matching the column list' in C:\xampp\htdocs\scraping\result.php:82

Stack trace: #0 C:\xampp\htdocs\scraping\result.php(82): PDOStatement->execute() #1 {main}

thrown in C:\xampp\htdocs\scraping\result.php on line 82

You'll have to index the table column by FULLTEXT and enable the KEYS on that table too :

ALTER TABLE weblinks ENABLE KEYS; 
ALTER TABLE weblinks ADD FULLTEXT(post);

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 20 '17 at 19:05

JazZ

4,469
2
20
40

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list' in C:\xampp\htdocs\scraping\result.php:82 Stack trace: #0 C:\xampp\htdocs\scraping\result.php(82): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\scraping\result.php on line 82 and line 82 is $sql->execute(); – Jacky Feb 21 '17 at 06:30
Ok, when we know the error we can search for a solution. It seems that the searched column was not indexed by `FULLTEXT` (needed to works with `MATCH AGAINST`). See this link on [Can't find FULLTEXT](http://stackoverflow.com/questions/9680472/cant-find-fulltext-index-matching-the-column-list-indexes-is-set) or google it yourself, you'll find a solution. – JazZ Feb 21 '17 at 06:41
Cant fix it, any idea regarding the search please? – Jacky Feb 21 '17 at 14:52
Yes, it says that you can enable the keys on your table : `ALTER TABLE weblinks ENABLE KEYS;` Or the table is not indexed by FULLTEXT. So you'll have to index it : `ALTER TABLE weblinks ADD FULLTEXT(post);` – JazZ Feb 21 '17 at 15:07
Now it works :) thanks man, probably this will be enough to search with multiple words, cant get deep :) – Jacky Feb 21 '17 at 15:15
Glad to hear it. Your welcome. I edit my answer with the content of my last comment. Please share and upvote ! = P – JazZ Feb 21 '17 at 15:18

wogsland · Answer 2 · 2017-02-20T20:59:46.467

2

You're trying to convert an array into a string. explode returns an array. Try instead

$search = $_POST['search'];
$search = trim($search);
$search = preg_replace('/\s+/', ' ', $kerko);
$keywords = explode(" ", $kerko);

$query = "SELECT * FROM weblinks WHERE ";
foreach ($keywords as $keyword) {
    $query .= " post LIKE '%$keyword%' OR";
}
$query = rtrim($query, 'OR');

$sql = $conn->prepare($query);    

$sql->execute();
while($row = $sql->fetch(PDO::FETCH_ASSOC)){    
?>

edited Feb 20 '17 at 20:59

answered Feb 20 '17 at 18:29

wogsland

9,106
19
57
93

still nothing, also I have updated the $kerko with $search..again nothing – Jacky Feb 20 '17 at 18:35
add a space before `post` in @wogslands example: `$query .= " post LIKE '%$keyword%' OR";` Space is missing so query with multiple keywords will end up like `ORpost LIKE...` in the foreach loop. – Michael Krikorev Feb 20 '17 at 19:21
Alternative short version without a foreach loop: `$query = "SELECT * FROM weblinks";` and then: `$query .= empty($keywords) ? '' : " WHERE post LIKE '%". implode("%' OR post LIKE '%", $keywords) ."%'";` – Michael Krikorev Feb 20 '17 at 19:42

Multiple word search in MySQL with PHP

2 Answers2