I have a database which contains 5 fields, which are: name, address, idcard, town, street
I then have a PHP query which accepts one parameter. In this parameter, you can enter whatever fields you like and the results must still be accurate.
For example, if I enter john doe skate street in the search box you will get all the records whose name is John Doe and they live in the street known as Skate Street.
Also, please note that the name is stored in one field in the order SURNAME NAME (I cannot change this because this is a must so please don't tell me that its better to change it... I know that :)). Obviously, I would like that the name can be entered in any order, be it NAME SURNAME or SURNAME NAME.
My idea is to first attach the first two entered parameters as the name. I will tell my client that the first 2 parameters must be the name and surname or surname and name. Following the name, he may then enter any of the order attributes in whatever order he likes, i.e.
These will be accepted:
name surname idcard town street
surname name idcard town street
name surname town idcard street
surname name address street idcard
These will not be accepted:
idcard town name surname street
town name surname idcard
for the simple reason that we will complicate the query too much (because of the name/surname reason, and them being in the same field)
If there is a way how the latter is possible without a lot of complications, I would like to hear it as well.
I would appreciate some help on this matter
Many thanks in advance
The kind of search you seem to be after is not really the sweet spot for RDBMSs such as MySQL. And specifying the allowable search formats is not generally good for usability, unless it's a rather specific domain.
Keeping it very generic, the query to search three fields for "Persons Name Fake Street" could be something like this:
SELECT * FROM Users
WHERE (FirstName LIKE "%Persons%"
OR LastName LIKE "%Persons%"
OR Address LIKE "%Persons%")
AND (FirstName LIKE "%Name%"
OR LastName LIKE "%Name%"
OR Address LIKE "%Name%")
AND (FirstName LIKE "%Fake%"
OR LastName LIKE "%Fake%"
OR Address LIKE "%Fake%")
AND (FirstName LIKE "%Street%"
OR LastName LIKE "%Street%"
OR Address LIKE "%Street%")
This should find any member who has details matching those given. But isn't very elegant and will only get worse with longer queries and more fields. It's also incredibly inefficient and will struggle quickly as the table gets longer - it's unable to use indexes. It also doesn't help you get the "best" matches at the top (if there are numerous results)
A better solution might be to use MySQLs Full Text indexing by creating a separate table that can be searched using the full text index to find the relevant Users. I don't know much about this solution.
Another option may be to use an external indexing tool such as Lucene. While it adds more complexity, it allows extra functionality such as weighting of fields. So name could be seen as more important than address for example. This can also order the results in order of relevance.
The correct solution depends on your requirements (as always) but those are some ideas that might be worth investigating.