CODEWITHSUNDEEP
mysqlfull-text-searchmatch-against
user6358161
user6358161

Reputation: 79

MATCH AGAINST not fetching multi column

I have a very simple FULLTEXT search with prepared statements. All my columns are correctly indexed as FULLTEXT.

The search and fetching works correctly if I try to MATCH one column, however, when I used 2 columns at once, I cannot fetch the results although they are expected. 1) I believe my error is in the fetch part of my code, but I cannot understand why that would be an issue with multi-column MATCH if it works with 1 column. 2) I know that fulltext search does not return results if MORE than 50% of the table records match with the search. How do you handle such a case then?

$newParameter='%'.$sanitized.'%';
$query = "SELECT DISTINCT name, description FROM recipes 
          WHERE MATCH (name, description) AGAINST (?)";

if($stmt = $this->mysqli->prepare($query))
  {
    $stmt->bind_param('s', $newParameter);
    $stmt->execute();

    /* bind results to prepared statement */
    $stmt->store_result();
    $stmt->bind_result($name, $description);

    /* fetch values */
    while ($stmt->fetch()) {
    /*  
     *  ERROR HERE:
     *  NOT ENTERING HERE WITH MULTICIOLUM MATCHING.
     */
      echo $name . ", " . $description . "<br>";
    }

  } else {
    echo "Empty query";
  }

NOTICE: this code returns what it should if I MATCH (name) or MATCH (description) alone. TY for helping.

Upvotes: 0

Views: 192

Answers (1)

user6358161
user6358161

Reputation: 79

SOLVED: the issue was with the indexing.

Specifically, my columns were indexed, one by one, with ADD FULLTEXT(column_name).

However, for this to work, the column must be indexed in a list using CREATE FULLTEXT INDEX idx ON table_name(columns_names);

See https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

and also, use MySQLWorkbench to test your queries and get information on the errors that may not be printed by a PHP query.

Upvotes: 1

Related Questions