Using mysql full search with multiple search terms

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Hendeca
Forum Commoner
Posts: 29
Joined: Tue Nov 18, 2008 1:27 pm

Using mysql full search with multiple search terms

Post 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!
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: Using mysql full search with multiple search terms

Post 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
Post Reply