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?
Your right when you say that a search of "%calder, alexander%" works, that is because the "," is part of the text string. MySQL does not know that the text is firstname, lastname; it treats it as a string and "," is part of the string. I know you don't want to redo the database design, but the field should be split into 2 seperate columns firstname, lastname and the frontend should have an input field for both.
I don't know what your application is written in, but could you do a replace on $search? Replace ' ' (spaces) with '%', then your search would be "%calder%alexander%". But again this would not take care of a search like "%alexander%calder%".
The format of the name field is how the client provided it, and as they do not wish to rebuild their office DB and as they will be passing me updates I had to find a way to work with what I was given. But you can rest assured that the client has been told time and time again that the names should be in seperate fields in the DB!!!
I'm using PHP and in the end have achieved acceptable results with the following:
$search_words = explode(' ', $search);
$result = mysql_query ("SELECT * FROM biografias WHERE tipo = 1 AND nombre LIKE '%$search_words%' AND nombre LIKE '%$search_words%' AND nombre LIKE '%$search_words%'");
Using "explode" to strip out space (should have the comma in their too - doh!) and then search on each element of the array created. It's a small DB so in this case it's acceptable.