Solved

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

Posted on 2012-03-22
7
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
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 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 look for a specific file type in a local or remote server directory using PHP.

730 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