Solved

SQL   select must contain multiple values

Posted on 2013-11-25
9
287 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
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.
0
 

Author Comment

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

Expert Comment

by:Gary
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Luey
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.
0
 

Author Comment

by:Luey
ID: 39675780
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
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.
0
 

Author Comment

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

Accepted Solution

by:
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)
0
 
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

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 …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

777 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