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

SimonDockerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.