Page 1 of 1

Using mysql full search with multiple search terms

Posted: Tue Dec 08, 2009 8:46 pm
by Hendeca
Hey Developers!

So here's what I'm trying to do:

I have a database of songs with ID3 tag info that I got using getID3. I am making a search function in PHP and the way it's configured is such that each attribute has it's own text box. For example artist has it's own text box as well as song title, album, and genre. Right the search box works in a way that if you type a search query into the artist box and the title box, you'll get the results from both. In my examples, I am using a class called PDOConnector to connect to the database. The current search works. Here's the code so far:

Code: Select all

 
if(isset($_POST['searchsubmit'])) {
    $mysql = new PDOConnector('mysql', 'localhost', 'dbname', 'user', 'pass', 'port');
    $searchParams = 'SELECT * FROM files WHERE';
    foreach($_POST as $key => $value) {
        $valueLength = strlen($value);
        if(($valueLength != 0) && ($key != 'searchsubmit')) {
            $partialMatch = "'%".$value."%'";
            $searchParams .= " ".$key." LIKE ".$partialMatch." ||";
        }
    }
 
    $query = substr($searchParams, 0, -2);
    $query .= ';';
    var_dump($query);
    $st = $mysql->query($query);
    }
}
I also included a picture of what it currently looks like with some returned search results.

Image

This takes each searchbox value and adds it onto the mysql query string. At the end it strips off the extra 2 || characters and adds the final semi-colon. So far the search works, but I'm interested in searching with mysql full-text so that I can get more accurate search results. I have seen full-text queries written as:

Code: Select all

 
SELECT * FROM articles
        WHERE MATCH (title,body)
       AGAINST ('database' IN NATURAL LANGUAGE MODE);
 
If I wanted each search box to search a specific term on a specific row, as is the case with my current code, would I need to make two queries? Or is there a way to work this into one query, as I have done in my code?

Thanks for any help and let me know if any clarifying questions need answering!

Re: Using mysql full search with multiple search terms

Posted: Wed Dec 09, 2009 2:40 pm
by tr0gd0rr
If your ID3 tag data is all in one field, it may work well to use the BOOLEAN MODE and join each of the specified terms with AND. For example, the following query will match only ID3 tags containing $artist and $album.

Code: Select all

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('$artist AND $album' IN BOOLEAN MODE);
If your ID3 tag info is broken into multiple fields (i.e. artist, album, etc.), a LIKE search may be best since each field will be really short. Or you can always do multiple match against statements: WHERE MATCH (...) AGAINST(...) OR MATCH (...) AGAINST (...)

Mysql Reference on MATCH AGAINST