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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
Technology Partners: 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 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 110

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Php logic to add to date card 9 38
php error 27 55
How do you retrieve results from a PDO Array using this format? 5 42
EditableGrid how to fetch rows from MySql in php 14 44
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This article discusses how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

761 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