I have a table called users which contains the columns firstname and lastname.
I am struggling on how to structure the WHERE clause to return results on matched first name, last name, first name plus a space and the last name, and last name plus a comma and the first name. For instance, "John", "Doe", "John Doe", and "Doe, John". It should also work for partial patches (i.e. "John D"). I am thinking of something like the following (substitute ? with the search phrase).
SELECT * FROM people WHERE
firstname LIKE ?
OR CONCAT_WS(" ",firstname,lastname) LIKE ?
OR lastname LIKE ?
OR CONCAT_WS(", ",lastname,firstname) LIKE ?
There is a little flaw with this approach as searching on "John Doe" will also return "John Enders", "John Flaggens", and "John Goodmen", so I will need to add some conditional to just return results when both first and last name match when both are given.
There is also a big flaw with this approach as I have functions in my WHERE clause which prevent the use of indexes and result in significantly reduce performance.
Can I effectively do this using just SQL without using functions in my WHERE clause, or should I user server code (ie PHP) to parse the given search phrase for spaces and commas and create a dynamic SQL query based on the results?
You ask if you can efficiently do this within MySQL, given your schema design without using FULLTEXT search the simple answer is no, you can't do this efficiently.
You could create some wacky query using LIKE / STRCMP and / or string functions. If you were to take this approach it will be much better to write some application logic to create the query inline rather than trying to write one query that can handle everything. Either way it’s not likely be truly efficient
MySQL 5.6 has the ability to perform FULLTEXT searches within INNODB. If you’re using a lower version you can only do this with MyISAM tables – there are many reasons why MyISAM may not be a good idea.
Another approach is to look at a real search solution such as Lucene, Sphinx or Xapian
Have you tried regular expressions http://dev.mysql.com/doc/refman/5.1/en/regexp.html