Solved

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

Posted on 2012-03-22
7
503 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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 …

840 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