MySQL search

Anon

Why can I search for the first name or last name and be successful yet cannot find a full name.

Eg. database contains authors_first="Monty" and authors_last="Python". If I search on "Monty" or "Python" I will find it but if I search on "Monty Python" I can't get a result.

switch ($type) {
case "author":
$sql = "SELECT authors_id as id, CONCAT( authors_first,' ',authors_last) as name FROM authors
WHERE authors_first LIKE '%$query%'
OR authors_last LIKE '%$query%'
ORDER BY authors_first";
$url = "authors.php3?authors_id=";
$title = "Author";
break;

Appreciate any help. This code is years old and though to have worked in the past.

[deleted]

" This code is years old and though to have worked in the past."

It can never have worked.

LIKE matches substrings.
In your current setup, you are searching for firstname and lastnames that CONTAIN "Monty Python", if the firstname is just 'monty' and the lastname is just 'python' then neither of them contain 'monty python' so you cannot get a match, ever.

You muast split up the name into parts 'monty' and 'python' and compare the firstlname field against all parts of the name, and then compare the lastname against all the parts and see how many of them match.

Anon

Thanks Vincent,
I suspected something of the sort. I'm pretty new at this MySQL/PHP stuff. Do you recommend exploding the search into an array and testing each component against both the first name and the last name fields?

I have tried swapping the "LIKE" operator with the "IN" operator, since the test is to read the record fields and them test them against the search arg. However I have not been able to make this work. Am I missing something?

WHERE authors_first IN '%$query%'
OR authors_last IN '%$query%'

[deleted]

IN is only for numbers

You could try reversing the logic.
instead of looking for "monty python" in 'monty' and 'python', try looking for 'python' and 'python' in 'monty python'

WHERE name LIKE concat('%', firstname,'%') AND name LIKE concat('%', lastname,'%')

Anon

Thanks Vincent, the following works really well. When I suspected that we had a problem I created a seach log. With the mods you have suggested a large number of the "not founds" are getting hits.

this is how the code now looks:

$sql = "SELECT authors_id as id, CONCAT( authors_first,' ',authors_last) as name FROM authors
WHERE '%$query%' LIKE concat('%', authors_first,'%') AND '%$query%' LIKE concat('%', authors_last,'%')
OR authors_first LIKE '%$query%' OR authors_last LIKE '%$query%'
ORDER BY authors_first";