SQL   select must contain multiple values

Posted on 2013-11-25
Last Modified: 2013-12-11
I am trying to write a select statement that multiple values must be in a tables column before it returns a row.  That may not make sense but maybe this will.  My table's column is named "title".

example title 1
example title 2
example title 3

I am trying to build a search for my table.  If someone enters "1" in my search form I only want the results to be "example title 1".  If someone enters "example title 1", i also want the only result to be "example title 1".  If someone entered "example" in the search I want the results to be all of the records containing "example".  Then to make it really complicated if someone entered "1 example title", I would want the result to be "example title 1".

I have this code but it does not work.  If i enter "example title 1", the results are all records because they all contain example and title.  

                $input = $_GET['input'];
		$terms = explode(" ", $input);
		$query = "SELECT * FROM search WHERE ";
		foreach ($terms as $each){
			if ($i == 1)
				$query .= "title LIKE '%$each%' ";
				$query .= "OR title LIKE '%$each%' ";

Open in new window

Question by:Luey
  • 4
  • 4
LVL 58

Expert Comment

ID: 39675578
If the table is MyISAM you could do a full text search against each word.
But there is no logic in the world that is going to be able to say you only want example title 1 if the user enters example 1 title or 1 example title etc
Unless you do a select for every variant and if nothing found then do an sql for every variant with one less word, and then do it again for 1 less word and so on - hoping you find a match.
Maybe workable if the column only contained a few words.

Author Comment

ID: 39675664
Then could I leave the sql the way i have it now then use php to loop through all the results and make sure they contain all the words in the search by putting them in an array or something?
LVL 58

Expert Comment

ID: 39675681
But then you are back to where you started again.  What if none of the rows contain all the words, then you will have to loop again and see if any rows contain some combination of the words.
Why do you need/want only 1 row if there is a complete match against all words in one row in the db?
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.


Author Comment

ID: 39675778
because the user is searching for products.  If they include the brand name when searching then all the products for that brand comes back because my client puts the brand name in the title of their products.

Author Comment

ID: 39675780
Also I have to have the out of order combinations because the user may search it several different ways.
LVL 58

Expert Comment

ID: 39675806
If I search for products I half expect to get multiple products returned where I can choose the correct one - its how every shopping cart system works.

Author Comment

ID: 39676202
Well if you are looking for Ariat Men's Heritage Roughstock Boots and you type in the words Ariat Roughstock Boots (the brand name is Ariat) in your search the way my sql is now it will bring back every Ariat boot we have in the inventory.  Not the results we are looking for which is only the ones in the Roughstock line.
LVL 58

Accepted Solution

Gary earned 500 total points
ID: 39676372
Then instead of an OR use AND, so all words have to appear in the name (also refer Full Text search)
If the query returns zero results then execute an OR search

If there was a brand column you could do a search against this first, followed by a manufacturer search.

Another option is to do an autocomplete search - force the user to select a valid entry by returning results based on keywords rather than a specific product name.  This is a more common way of doing it these days. This is based on an AND search not an OR, but with just a few keywords - Ariat Roughstock Boots - you could probably end up with an autocomplete of just a few entries (well depending on how many products that would match)
LVL 109

Expert Comment

by:Ray Paseur
ID: 39676444
The "right" answer is likely to be a little more work than you want to do, but it's what the professionals would do because this is about making it as easy as possible for people to find what they want and spend their money.  You will want to create a sound-alike table.  The table will have the key for the product and one keyword for each product.  The table will also have sound-alike matches for the keywords.  Make a soundex() and metaphone() from each keyword and put them into the table, too.  StrRev() each keyword and put it into its own row.  Make a soundex() and metaphone() from each of the reversed keywords and store those, too.

Then when a request comes in, you can perform the same soundex, metaphone, reversal, reverse soundex and reverse metaphone on each word in the request string.  When you query the sound-alike table you will get many results.  Choose the product keys that have the most matched values and use those as the auto-suggest values.

it's been many years since I wrote something like this and I don't have a teaching example any more.  But we used it with the TSA to help them match phonetic names in Arabic that did not transliterate to English.  They were having trouble finding true positives on the no-fly list.  This technique reduced the error counts to next to nothing.  I know it works, even if it is a bit of code. You might package it as a class that searches the data base and returns an object with the top four most likely matches and a score for each from 0 to 10, where 10 is a perfect match and a 0 is complete no-hit.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
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 count occurrences of each item in an array.

828 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