Solved

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

Posted on 2012-03-22
7
500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 16

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 16

Accepted Solution

by:
Chris Harte earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now