Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Use SQL wild card keywords when searching a table column that may have more than word entered

Posted on 2012-03-22
7
Medium Priority
?
512 Views
Last Modified: 2012-08-14
Hey guys, I am trying to create a search bar for my site, I have a products column that has the name of the products, my search query is working but I need to enter the full product name to find it, here is my query:

$query = "SELECT * FROM PRODUCTS WHERE CATALOGUEID = (SELECT CATALOGUEID FROM PRODUCT_CATALOGUE WHERE PRODUCT_NAME LIKE '%" . $term. "%') AND category1 = 1  ";

Open in new window


The variable $term is the keyword passed to the query, I will sanitise the query once I get it working. If I want to search for a product called Cyclone Jacket, I need to enter "Cyclone Jacket" into the search bar, is it possible to set up the wildcard keyword to search for simply "Jacket"?

It is highly unlikely someone will be searching for a product by its full name but rather words such as jacket, shirt, trousers etc...

If anyone could help me figure this out that would be great!

Many thanks!
0
Comment
Question by:deucalion0
7 Comments
 
LVL 10

Expert Comment

by:Umar Topia
ID: 37751943
The query which you had written was almost fine. However, I had changed it lil bit:

$query = "SELECT * FROM PRODUCTS WHERE CATALOGUEID in (SELECT CATALOGUEID FROM PRODUCT_CATALOGUE WHERE PRODUCT_NAME LIKE '%" . $term. "%') AND category1 = 1  ";
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37751945
My article on Complex Text Filters covers this, but was written with Access in mind.  You would need to take a look at the function described there and convert it to a SQL Server UDF.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 37752205
Have you looked into SQL Server Full Text Indexing?  This would give you the ability to use the FREETEXT command in SQL Server that will probably meet your needs.  

If you haven't done any research on Full Text Indexing, you'll have do do a little bit of reading, but it's worth it.  Results are incredibly faster and more reliable then using LIKE '%' statements in your WHERE clause.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Chris Harte
ID: 37752834
This might make a difference. Do not concatenate the statement, let the php server parse it itself. (ie no full stops around the variable)

$query = "SELECT * FROM PRODUCTS 
		  WHERE CATALOGUEID = 
		  	(SELECT CATALOGUEID 
		  	 FROM PRODUCT_CATALOGUE 
		  	 WHERE PRODUCT_NAME LIKE '%$term%') 
		  AND category1 = 1  ";

Open in new window

0
 

Author Comment

by:deucalion0
ID: 37752972
Thanks for all your input guys. I have read all your posts and am trying my best to make the most of your advice. Munterman I tried your suggestion but it returned an error when searching for the word vest, instead of the whole name which is Ultracool Aero Vest:

vestError in query preparation/execution. Array ( [0] => Array ( [0] => 21000 [SQLSTATE] => 21000 [1] => 512 [code] => 512 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. ) [1] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 16945 [code] => 16945 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The cursor was not declared. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]The cursor was not declared. ) )

Open in new window



Thanks for your help with this guys, I am determined to figure this out!
0
 
LVL 17

Accepted Solution

by:
Chris Harte earned 2000 total points
ID: 37753073
Subquery error. Replace the = with IN

$query = "SELECT * FROM PRODUCTS 
		  WHERE CATALOGUEID IN
		  	(SELECT CATALOGUEID 
		  	 FROM PRODUCT_CATALOGUE 
		  	 WHERE PRODUCT_NAME LIKE '%$term%') 
		  AND category1 = 1  ";

Open in new window

0
 

Author Comment

by:deucalion0
ID: 37753216
Thanks MunterMan, that is awesome! It works now! I swear I tried that but got nothing but errors but now with IN instead of = it works, I have definitely learnt a few things from this!

I appreciate all your help guys!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question