[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

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
?
510 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 48

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

656 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