PHP / MySQL matching a search term

Anon

Hi,

I've a very basic search feature built into a site that simply searches the names held in a specific table. I am using the following SELECT command where $search is the query passed over from the input box:

SELECT * FROM biografias WHERE tipo = 1 AND nombre LIKE '%$search%'"

This works reasonably well apart from one flaw. If I search for calder (surname) I get a return, if I search for alexander (first name) I get a return, but if I search for the complete name alexander calder I get no returns.

In the small DB provided the artist names are entered into a single field and in reverse, eg. Calder, Alexander and I assume that it is this that is causing the problem when I search for the name in it's natural order (a search on calder, alexander gives a result).

My needs here are quite basic, but clearly allowing the user to enter the name in natural order is a must. Can anyone suggest a workaround that can make the DB recognise / select the name without changing the actual DB entry?

Thanks in advance,

Gary Crighton

Anon

you could create the query by using a loop, searching for each search term separately. I'm no php expert, but this is my idea. It may not work exactly as you want, and will require plenty of modifications.

$searchTerms = "pizza cat doggy";
// split up the search terms
$searchArray = split(" ", $searchTerms);

$x = 0;
$query = "SELECT * FROM table WHERE ";
while($term = $searchArray[$x]) {
// add each term to the query
$query = $query . "field LIKE '%$term%' ";
// add AND unless it's the last term
if($x < count($searchArray)) {
$query = $query . "AND ";
}
$x++;
}

$result = mysql_query($query);

Anon

Hi Sevex,

Thanks for the response. My eventual solution was more or less along the lines you suggest (albeit much simplified):

$search_words = explode(' ', $search);

$result = mysql_query ("SELECT * FROM biografias WHERE tipo = 1 AND nombre LIKE '%$search_words[0]%' AND nombre LIKE '%$search_words[1]%' AND nombre LIKE '%$search_words[2]%'");

I allow for three possible array names as many spanish names take three parts.

Works a treat!

Thanks again for taking the time,

Gary