Match any portion for searches

ricoche

Hello,

Instead of "match", I am looking for a "match any portion" way to do searches with mysql.

We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.

Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already. We can use things such as "LIKE 4986%" for example, but most users do not know such mysql commands. We are simply looking to create a form that has the following options with radio boxes.

Match this exact Part No. or Manufacturer
Match any portion of Part No. or Manufacturer

In this case, the second option would give us exactly the result we need. So far, I have not seen any search form for php and mysql that will work.

Any ideas? Do I need to switch to MSSQL?

Thank you very much. This is a huge problem for us.

Jim

liquorvicar

Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already. We can use things such as "LIKE 4986%" for example, but most users do not know such mysql commands. We are simply looking to create a form that has the following options with radio boxes.

why do you say LIKE doesn't work?

how about something like this (pseudo code):

$search_string = sanitise_input($_POST['search_string);
$search_type = ($_POST['search_type']=='exact' ? 'exact' : 'partial');
if ($search_type=='exact') {
   $sql = "SELECT * FROM table WHERE field='$search_string'";
} else {
   $sql = "SELECT * FROM table WHERE field LIKE '%$search_string%'";
}

that way your users din't have to worry about SQL coding/wildcard characters.
the partial option will match any occurance of the search string in a text/varchar field.

hence 4984 will match
4984.6000
12498434
6000.4984

andy

ricoche

Hello,

Thank you for your reply.

I am basically saying LIKE will not work because you have what I want to do backwards. I have been posting this around and I have noticed many people are misunderstanding what I am saying.

We want to be able to put in 4984.600 and choose "match any portion" and have it find 4984600 in our database.

How can I get a person who types specifically 4984.600 to come up as 4984600?

hence 4984 will match
4984.6000
12498434
6000.4984

Or using your hence example above, I am looking for the following:

hence 4984.600 will match
4984600

Do you think this possible using php and mysql?

Thanks.

ricoche

Hi there,

Here you can see in action what I am talking about.

First, go to this website http://www.bell-electrical.com. On the left side there is an "Inventory Search" section. Enter Part no. 4984.600 ( You don't have to enter a manufacturer, or check any radio boxes. ).

The website will now redirect to another website and display your result. It will contain the part number 4984600 without the decimal. That particular website is using an ASP script with an MSSQL backend. This is why I say it works with MSSQL. Whether it is the ASP script or something else using sql, I am not exactly sure, but they are doing something to make this work.

I am basically looking to implement something like this using php and mysql, but am unsure if it's possible. I have noticed that most big php scripts including vbulletin for example don't have this function either.

The particular company that is using this search system, is not known for custom programming. Rather they usually use basic asp and mssql systems. This also leads me to believe that it's probably not all that complex.

Any ideas on how they might be achieving this would be great. I have also heard that in MS Access there is actually some parameter that you can use to switch from a "Match" search to a "Match Portion" search. I am wondering if there is something like this in MSSQL as well.

Anyway, hopefully this reply will help everyone understand what I am trying to accomplish.

Thanks again for all the replies.

liquorvicar

why not store your part numbers in a normalised form (i.e. digits only, no punctuation)? you can then just do a straight LIKE search on that field.

ricoche

Hi there,

Problem solved.

I had to do two things to fix this issue.

Create a second unique part field and eliminated all of the special punctuation that I didn't need.
Modify the search code to strip any unwanted punctuation, which in this case was the period.

Now I can do the type of search outlined in my first post. Not to mention it makes for a much more powerful search system.

Thanks for all the responses. They were all valuable clues to help me get to the bottom of this puzzle.

Jim 🙂