MYSQL Search question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mendenha
Forum Newbie
Posts: 19
Joined: Tue Jan 12, 2010 2:24 pm

MYSQL Search question

Post by mendenha »

First off let me just say that I'm rather new at MYSQL and PHP.

My question is, what would be the best way to search a single column in a DB that contains multiple words separated by spaces?

My current SQL statement looks something like this

Code: Select all

$q = mysql_query("Select * from tbl_restaurantinfo Where txtsearch Like '%". $txtsearch. "%' Order By name");
txtsearch is a column name in my DB and $txtsearch is the variable for the search box.

Is there a better way to go about this?

Right now I'm getting way to many results when I search.

For example, if I search for the word Deli I will receive 25 different rows and only 3 of those rows actually have the word Deli in them. It's almost like it's searching for each individual character and not the entire word.

Any help would be appreciated.
mendenha
Forum Newbie
Posts: 19
Joined: Tue Jan 12, 2010 2:24 pm

Re: MYSQL Search question

Post by mendenha »

Well not I feel stupid :banghead:

The reason I was getting so many results was because I also have the word Delivery in the txtsearch column. Since Deli and Delivery all start off with the same characters I was getting all the delivery options too.

But the question still remains, am I going about this search thing the correct way?
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: MYSQL Search question

Post by emmbec »

If your $txtsearch contains a single word, you are doing fine.
In a simple way(because there are quite a lot more) If the $txtsearch variable contains something like:

"Hello cruel world"

you will need to split first the words into an array and then each element of the array put it in your query:

txtsearch like '%". $array[0]. "%' OR txtsearch like '%". $array[1]. "%' OR ...

You need to use a loop to go through each element of the array to make it easy.

Hope this helps.
Post Reply