Asked  7 Months ago    Answers:  5   Viewed   60 times

I've never really heard a straight answer on this one, I just need to FULLTEXT search a couple columns with multiple words "Firstname Lastname"

$sql = mysql_query("SELECT * FROM 
                    patient_db 
                    WHERE MATCH ( Name, id_number )
                    AGAINST ('% $term %' IN BOOLEAN MODE);");

But it fails to run the query if I enter more than one word here.

 Answers

53
$sql = mysql_query("SELECT * FROM 
         patient_db WHERE 
         MATCH ( Name, id_number ) 
         AGAINST ('+first_word +second_word +third_word' IN BOOLEAN MODE);");

and if you want to do exact search:

$sql = mysql_query("SELECT * 
                  FROM patient_db 
                  WHERE MATCH ( Name, id_number ) 
                  AGAINST ('"exact phrase"' IN BOOLEAN MODE);");
Wednesday, March 31, 2021
 
Xavio
answered 7 Months ago
32

There are two ways to do this. The first is the rather obvious approach. Let's say you have all the words that need to appear in an array called $necessaryWords:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE 1';

foreach ($necessaryWords as $word)
    $sql .= ' AND concat(subject,body) LIKE "%' . $word . '%"'; //Quotes around string

However, using %foo% is rather slow, as no indexes can be used, so this query might cause performance issues with huge tables and/or a high number of necessary words.

The other approach would be a FULLTEXT index on subject and body. You could the use the fulltext MATCH IN BOOLEAN MODE like this:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE MATCH(subject,body) AGAINST("';

foreach ($necessaryWords as $word)
    $sql .= ' +' . $word;
$sql .= '")';

Note that your table must use MyISAM in order to use FULLTEXT indexes. UPDATE: As of MySQL 5.6, InnoDB supports FULLTEXT indexes as well. I guess this could be the better choice performance wise. Further documentation on the fulltext in boolean mode can be found in the manual.

Friday, May 28, 2021
 
aurelijusv
answered 5 Months ago
60

Maybe I am mistaken, but if you search this string +divorce+refinance you get a weird result. If you want to search both words, your should search for +divorce +refinance (with a space between).

I tested it and it returns only one row:

Divorce: Paying Off Spouse = Rate/Term Refinance
Saturday, May 29, 2021
 
jerrygarciuh
answered 5 Months ago
90

You are missing a $ symbol. I think

$var=@&_GET['q'];

should probably be

$var=@$_GET['q'];

unless you really want a reference, in which case it should be this: (the error suppression is not needed at this point if you want a reference, but you should check $var is set before trying to access it)

$var=& $_GET['q'];

I would be tempted to write it a bit more like this.

if (!isset($_GET['q'])) {
    echo"<p>We don't seem to have a search parameter!</p>";
    exit;
}

$trimmed = trim($_GET['q']);

if($trimmed=="") {
    echo"<p>Please enter a name.</p>";
    exit;
}
Saturday, May 29, 2021
 
Tucker
answered 5 Months ago
65

It appears that InnoDB tables do not allow searches over several fulltext indexes in the same MATCH() condition.

Here your fields do not all belong to the same table, therefore they are covered by different indexes. Notice the same limitation applies if you had a table like this:

CREATE TABLE t (
  f1 VARCHAR(20),
  f2 VARCHAR(20),
  FULLTEXT(f1), FULLTEXT(f2)
) ENGINE=InnoDB;

SELECT * FROM t
WHERE MATCH(f1, f2) AGAINST ('something in f2'); -- likely to return no row

It looks like a fulltext search may only search on the first fulltext index it encounters but this is only something I deduct from this experience, please do not take this for granted.

The bottomline is that you should split your search so as to use one single fulltext index per MATCH() clause:

SELECT * FROM auction, user, gallery, ...
WHERE
    MATCH(auction.field1, auction.field2) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(auction.field3) AGAINST ('search query' IN BOOLEAN MODE) OR
    MATCH(user.field1, user.field2, user.field3) AGAINST...

This is an illustration of a possible query if you had two distinct indexes on auction and one one on user. You need to adapt it to your actual structure (please post your tables' descriptions if you need more guidance).

Notice this only applies to InnoDB tables. Interestingly, MyISAM tables do not seem to show the same limitation.


Update: it turns out this was a bug in the InnoDB engine, fixed in 5.6.13/5.7.2. The above example now rightfully fails with "Can't find FULLTEXT index matching the column list". Indeed, there is no index on (f1, f2), but one on (f1) and another one on (f2). As the changelog advises:

Unlike MyISAM, InnoDB does not support boolean full-text searches on nonindexed columns, but this restriction was not enforced, resulting in queries that returned incorrect results.

It is noteworthy that while such queries return a correct result set with MyISAM, they run slower than one might expect, as they silently ignore existing fulltext indexes.

Thursday, September 30, 2021
 
Litty
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :