1

I am trying to understand what is the best way to construct a query string with php when there are multiple columns I want it to look for.

For example the database has id, name, email, date.

And I have a search input field on a page which when submitted, I want it to search based on the input field against the above mentioned columns. Best what is the best way/practice to do that?

I have the following so far but it seems like it is a "dumb" search.

"SELECT * FROM Table WHERE id LIKE '$search%' || name LIKE '$search%' || email LIKE '$search%' || date LIKE '$search%'";

Well this sort of works but I feel there has to be a better way and a more appropriate smarter method.

Thanks...

Improve this question
5
  • if they can select email or name then only search on the filed selected, if you always want to search all fields I see no problem with the above. Commented Dec 17, 2011 at 5:03
  • @Dagon - do you mean separate 4 different search input fields to search for all 4 columns exclusively? Commented Dec 17, 2011 at 5:06
  • That query will not use indexes on any of those conditions, meaning it will be very slow if the table is large. In terms of functionality though, without using a full text search or an external searching program, it is correct. Commented Dec 17, 2011 at 5:07
  • @rick, how about one select to pick which to search on, but i don't know your requirements. this may well be the best option for you, but there is really not enough information to decide that (for us). as Corbin says this may be slow, but only if its a very large table. Commented Dec 17, 2011 at 5:13
  • @Dagon - Yes I suppose I could create a dropdown to select which column to search for but obviously it is not as clean. While my table isn't big now but one day it will be as it generates new rows on a daily basis. Perhaps I need to research more on how to optimize searches for large data tables. Commented Dec 17, 2011 at 5:16

2 Answers 2

Reset to default
1

You cannot make mysql use index properly when you are querying multiple columns and not using AND (you are using OR)

There is always need to optimize your query strategy, always people think the data is small,
why bother to spend effort to optimize ... but the truth is you don't know when your data will grow

For your query, if you want to stick to LIKE,
then you need to build 4 indexes on the 4 columns, 

alter table Table add index on(id);
alter table Table add index on(name);
alter table Table add index on(email);
alter table Table add index on(date);

And change the query to :-

SELECT * FROM Table WHERE id LIKE '$search%'
union distinct 
SELECT * FROM Table where name LIKE '$search%'
union distinct 
SELECT * FROM Table where email LIKE '$search%'
union distinct 
SELECT * FROM Table where date LIKE '$search%'

What is Union ?

Off-topic issue :-

  • you did not escape for user input, it could lead to SQL injection
  • is meaningless to search on ID when the input could be anything
  • same go for date
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

from my research according to the MYSQL docs, it says if only 1 wildcard is used (%), it will use the index. However whenever I try using 2, I don't get the expected search results.
This is incorrect, this format %something is not make use on index, this something% will allow index. of course, %something% will not make use on index too.
right that is what I meant, where I was using $search% in my post. But why does it when i use %$search%, the result is different? So when to use what?
depends on your requirement, $search% meaning PHP abc can be matched (leading value with PHP) if you look for PHP. While %$search% can match haha PHP hoho, it sort of like matching as along the PHP is exist
Ok so I guess in my application % wildcard at the end is correct because I have already separated the firstname and lastname so therefore it should generally be only 1 word. Thanks for the explanation. So Using your proposed code, would that be faster and less taxing if the table happens to have a large amount of data versus the way I had it?
0

I found myself asking the same question a few years back. For the kind of functionality you are looking for, the most efficient way is full text search. Of course it come at the cost of space. I did not find any other way of implementing this efficiently. sorry i do not know if fts is supported in mysql.

Improve this answer

1 Comment

mysql does full text searches, but its hard to tell if thats worth it in this situation. dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.