[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Search anypart of field in SQL search result

Hello,
I run www.sateuropa.co.uk, and I'm having trouble modifying our search feature, it's showing results, but only if the input is exactly as written and the first part of the field.
EG if you search 6900 it gives NO RESULTS, if you search the model's exact name TM-6900 it shows the results. I'd like it so that 6900 does show the TM-6900 as this is part of the product name.
Would anyone kindly be able to advise how I need to modify this below to action this?
I'll be so very grateful! Simon, Southampton, UK
if txtType = 1  then  'manufacturer 
	txtSQL =  "SELECT *  FROM products where manufacturer LIKE '" & txtSearchText  & "%' OR Name LIKE '" & txtSearchText  & "%'"

Open in new window

0
SimonDocker
Asked:
SimonDocker
  • 4
  • 3
1 Solution
 
97WideGlideCommented:
txtSQL =  "SELECT *  FROM products where manufacturer LIKE '%" & txtSearchText  & "%' OR Name LIKE '%" & txtSearchText  & "%'"

Just added a few '%'.

HIH
0
 
SimonDockerAuthor Commented:
Can you show me in the code where to add the % please? And how will this resolve it?
Many thanks,
Simon
0
 
97WideGlideCommented:
I'll try.. someone may do a better job.

Select * from products where manufacturer LIKE '6900%';
would give all products where the manufacturer starts with 6900 and then has zero or more characters following it.  
So I think you need another % sign just before 6900 so that products would be shown if 6900 is anywhere in the manufacturer.

Please see the following link for more explanation :

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Specifically, the part which talks about wildcard characters.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
97WideGlideCommented:
Oh, and for comparison, the top line is yours and the bottom line has what I think you should consider changing it to :

The % stands in for zero or more unspecified characters.

"SELECT *  FROM products where manufacturer LIKE '"    & txtSearchText  & "%' OR Name LIKE '"    & txtSearchText  & "%'"
"SELECT *  FROM products where manufacturer LIKE '%" & txtSearchText  & "%' OR Name LIKE '%" & txtSearchText  & "%'"
0
 
SimonDockerAuthor Commented:
Seems to be an improvement, the link doesn't work though. But getting more results.
So when I search 0.1dB LNB I get no results, it should show results from these fields like "0.1dB Single LNB (Diamond edition)"
Also if I search for the  TM-6900 as TM 6900, it still doesn't show results.
Any ideas?
I'm very grateful :)
0
 
97WideGlideCommented:
Hi Simon,

The % stands in for zero or more unspecified characters at that position.

So, if you search for "0.1dB LNB" the code you wrote converts that to '%0.1dB LNB%' which does not match "0.1dB Single LNB", Right ?
Likewise, "TM 6900" does not match "TM-6900";

The type of searching you are mentioning here is somewhat different and not really what you talked about in your original post.

Could you confirm that we've solved the original problem, namely that a search for 6900 returns your expected results ?  

After that, you'll need to tokenize your entered search string and pass it to the SQL engine to get the results you mention here.



ALSO, I just noticed that you are searching on both manufacturer and name.  Keep in mind that by adding the % sign you are searching on substrings existing in both columns.

0
 
SimonDockerAuthor Commented:
OK I see your point.
I'll certainly archive that as a solution.
How can I get around the LNB example you showed me, any chance or dropping it into the code again?
Thanks as always :)
P.S. This is my first time on this website, excuse me not knowing etticate.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now