simple search function

I'm trying to create a simple search function using mysql and php. My question is how would i go about comparing a users string against values in an table and return multiple instances. For example if I have this table:

tableName=myTable
--------------------------------------------------------------------------------------------
|    pName 	        |	pKeyW		|	pDesc 		| 	pLink 	   |
--------------------------------------------------------------------------------------------
| Grizzly Bear       	|  grizzly bear		| short description of	| http://url       |
|		        |  grizzly	        | grizzly bear		|		   |
|		        |  bear		        |			|		   |
|		        |  dangerous		|			|		   |
--------------------------------------------------------------------------------------------

Code (markup):

Well several things really:

1. Can i have multiple text values in pKeyW and each of them be their own instance such as "grizzly bear", "grizzly","bear","dangerous" or are the values just seen as one big text string such as "grizzly bear grizzly bear dangerous"?

2. How in sql can I create a query that will compare a variable against multiple instances. For example if a variable is set equal to "bear" how would i compare it to the multiple items in the pKeyW attribute?

3.Would the following sql statement suffice for this?

SELECT pName pDesc pLink FROM myTABLE WHERE pKeyW LIKE 'bear'

Code (markup):

Again however this brings us back to question 1 from above. I believe that would work, if there's a way to have multiple instances of data in one record....


Any advice is greatly appreciated. I've been working on figuring out the layout for this simple search function for a few days now and it's starting to bring me down...

 

Your current query sting works fine but is looking for an exact match of the phrase, as in this instance you are only using a single word (bear) it will bring back results.

If however your search was for "brown bear" or "big grizzly bear" then you are not going to get any hits back because of your keywords do not contain those exact phrases.

As a very simplistic search it works and as it is exact phrases your matching it is good to have the multiple values.

A mid level search would be to break down the phrase into individual words and then use either OR or AND statements to test for each word individually. If you used OR and searched for "Big Brown Bear" then it would come back with a hit as of the bear keyword. In this case there is no point in having multiple values.

A higher level search allows people to use search additions like + for must have and - for must not and more importantly "" for exact phrases, combine this with the one above and you then need to have mutliple values again as people may search for Brown Bear (will get a hit) or "Brown Bear" (wont get a hit)

Because there are no weightings the mid level with OR statements is an OK solution but some may consider your search fairly inaccurate as if they search for Grizzly Bear they will get every Bear returned and depending on your ordering could look dirty.

 

Thank you for the reply. All of that information helped and I started writing a new query. This is what i came up with using the LIKE command:

SELECT * FROM myTable WHERE pName LIKE '%" . bear . "%'"

Code (markup):

However now with this statement I'm getting an error message:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search.php on line 11
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myTable WHERE pName LIKE '%bear%'' at line 1

Code (markup):

I couldn't seem to find anything wrong with that query...any ideas why i'm getting this error?

 

does anyone have any ideas as to why this query is failing? I've tried a couple different versions and i keep getting this error

 

I'm no mysql expert by any means. But you have 3 double quote symbols ("). Shouldn't there always be an even number of matching quotes?

 

Thank you for pointing that out. Whenever i copied the code to the site i missed a quotation. Should look like this:

"SELECT * FROM myTable WHERE pName LIKE '%" . bear . "%'"

Code (markup):

THIS query is what is giving me problems.