SQL   select must contain multiple values

Posted on 2013-11-25
Medium Priority
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 2000 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 111

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

862 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