Need some help with Select LIKE

[dv8er88]

Hi,

I have a product in my DB named 8v-71N.

I am currently using the following to search:
SELECT * FROM product WHERE name like '%#form.search#%'

But when a search is done with any of the following no results are found:
8v71N, 8-v71 N, 8v 71N or 8v-71 N

Is there a better way?

 

[rac2]

Ah those pesky users! If only they would type the product id correctly, life would be so much better.

Two lines of thought.
A. The product id has a pattern which you can describe in code.
1. Add validation to the search form to check for this pattern. Also provide hints on the form and in the validation alert as to what a valid pattern looks like.
2. Transform the search value into the pattern. For example, TRIM spaces. Parse the search value and supply missing delimiters such as the dash in position 3.

B. Provide a dropdown menu instead of a text field. Thus, only valid values are possible. If you have a thousand products, then organize them into a hierarchy of categories and offer dynamic menus to narrow the list to a dozen or so options.

 

[PaulFynch]

You could try this (don't know if it will help):
$query = "SELECT * FROM product WHERE MATCH (name) AGAINST ('{$form_search}' IN BOOLEAN MODE)";

Dunno what language you're using, the above would make a php query...

Hope that helps,
Paul


"If you build it....it won't work the first time.....or the second

 

[r937]

select foo, bar, name
  from product 
 where replace(replace(name,' ',''),'-','')
  like replace(replace('%#form.search#%',' ',''),'-','')

r937.com | rudy.ca