Solved

SQL   select must contain multiple values

Posted on 2013-11-25
9
285 Views
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".

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){
			$i++;
			if ($i == 1)
				$query .= "title LIKE '%$each%' ";
			else
				$query .= "OR title LIKE '%$each%' ";
		}

Open in new window

0
Comment
Question by:Luey
  • 4
  • 4
9 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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.
0
 

Author Comment

by:Luey
Comment Utility
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?
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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?
0
 

Author Comment

by:Luey
Comment Utility
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Luey
Comment Utility
Also I have to have the out of order combinations because the user may search it several different ways.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
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.
0
 

Author Comment

by:Luey
Comment Utility
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.
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
Comment Utility
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)
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now