Using mysql full search with multiple search terms
Posted: Tue Dec 08, 2009 8:46 pm
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:
I also included a picture of what it currently looks like with some returned search results.

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:
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!
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);
}
}
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);
Thanks for any help and let me know if any clarifying questions need answering!