1

I have a problem to find a specific word in my database giving some keywords.

This is my database:

mydb

I would like a query wich returns only the word bike from this keywords: wheels pedals chain.

I tried with this code:

SELECT * 
FROM mydb 
WHERE keywords LIKE '%wheels%' AND keywords LIKE '%pedals%' AND keywords LIKE '%chain%'

but the query doesn't give any result because the keyword "chain" doesn't exist in mydb.

I tried also this other query: 

SELECT * 
FROM mydb 
WHERE keywords LIKE '%wheels%' OR keywords LIKE '%pedals%' OR keywords LIKE '%chain%'

The query returns all the words (car, train, bike).

My scope is to try to guess a word like "bike" by searching words that define the bike, so if I insert a keyword in the search string that is not listed in the db should still come up with the result "bike". For example if I type keywords "wheels" "pedals" and a keyword that is not include in my db like "chain" it should return "bike".

helmbert
  • 35,797
  • 13
  • 82
  • 95
Oliver1990
  • 29
  • 4

4 Answers4

2

You can do this. You just need to count how many of the keywords are matched and sort your table by that count.

Matching one of the keywords like this: (keywords LIKE '%wheels%') will return a boolean, represented in MySQL by 1 (true) or 0 (false). So just add together all of your keyword matches like this:

(keywords LIKE '%wheels%') + (keywords LIKE '%pedals') + (keywords LIKE '%chain%')

And then sort by that sum. If you sort in descending order and select the first row, it should be the best match.

SELECT mydb.*, 
    ((keywords LIKE '%wheels%') + 
    (keywords LIKE '%pedals') + 
    (keywords LIKE '%chain%')) AS best_match
FROM mydb
ORDER BY best_match DESC
LIMIT 1;

It is true that this query would be easier to write and much quicker if you had a related keywords table rather than multiple keywords in one column.

Don't Panic
  • 41,125
  • 10
  • 61
  • 80
  • Hi, thank you for your help! this solution sems to work fine. How can I exclude all the records with valid = 0 ? I tryed to add AND operator as below but didn't work: SELECT searchengine.*, ((keywords LIKE '%wheels%') ) AS best_match FROM searchengine ORDER BY best_match DESC LIMIT 1 AND valid = 1; – Oliver1990 Dec 18 '15 at 21:11
  • You need to add a WHERE clause (`WHERE valid=1`), after `FROM mydb` and before `ORDER BY...`. – Don't Panic Dec 18 '15 at 21:14
  • I included the clause : SELECT searchengine.*, ((keywords LIKE '%wheels%')) AS best_match FROM searchengine WHERE valid = 1 ORDER BY best_match DESC LIMIT 1 . It works but if I search for a keyword that is not included in mydb the query returns with all the records with valid = 1. The reason for the field valid is to exlude from the search keywords with valid=0 – Oliver1990 Dec 18 '15 at 21:51
0

ok, so your first query returns nothing because there is no record in your database that contains all of the words you have specified.

The second query returns all records because all of them contain "wheels".

Try your first query but without looking for "chain", that should return only bike.

ewanc
  • 1,284
  • 1
  • 12
  • 23
  • the problem is that bike should be the result of some keyword that describes the bike and only few keywords are define in database but from the php interface you could type keywords that could not be in the db. – Oliver1990 Dec 18 '15 at 13:20
  • ok, so you want it to return the closest match? So if you put in 5 keywords, 2 match bike and 1 matches car, you want it to return bike only. Is that right? – ewanc Dec 18 '15 at 13:23
  • Can you change the layout of your database or does the table in the screenshot have to remain the same? – ewanc Dec 18 '15 at 13:53
  • theoretically not, but anyway what do you suggest? – Oliver1990 Dec 18 '15 at 14:21
  • Well, this is not a simple problem, definitely not one that can be solved with a single select statement. You may be able to make things slightly easier for yourself by splitting the keywords out into a separate table. If you consider the steps involved in the solution rather than trying to find a one liner that works you might be able to make more progress. Eg, for each row count the number of keyword matches, order by count, select first row. Just an example, probably not the best way to do it – ewanc Dec 18 '15 at 14:26
  • See this link for a possible way to do what you are looking for. As mentioned in the comments on @Issibra's answer, this is quite complicated and it may be easier to only do simple queries in SQL and have your PHP code do the work of choosing the appropriate record. http://stackoverflow.com/questions/31512506/sql-query-to-find-rows-with-the-most-matching-keywords – ewanc Dec 18 '15 at 15:26
0

You could simple return the bike by this query

SELECT * FROM `stackoverflow` WHERE `title` LIKE '%bike%'
John Roca
  • 1,204
  • 1
  • 14
  • 27
0

try this 

SELECT *  FROM mydb  
    WHERE keywords LIKE '%wheels%pedals%'
Issibra
  • 79
  • 1
  • 10
  • Thank you but the query will not return a result if I type a word that is not included to my db. – Oliver1990 Dec 18 '15 at 13:11
  • what you mean by word that is not included to your db ?; what is the type of 'keywords' is it a list , String or what exactly – Issibra Dec 18 '15 at 14:07
  • for example the tags for "bike" are: two wheels pedals, while for "car" are: motor four wheels steering seats. inserting from the web interface the keywords: pedals, wheels should return bike. The keywords are strings. – Oliver1990 Dec 18 '15 at 14:48
  • so in the interface u have to set two keywords , so it's a List of String that every element have to be either 'pedals' or wheels', that's it ?? – Issibra Dec 18 '15 at 14:54
  • @issibra see the comments on my answer below and the new edited title. Basically the problem is to find the record in the DB that matches the most keywords entered (via the UI). – ewanc Dec 18 '15 at 14:58
  • thanks @ewanc , I understand this , but theorically we can't do only with sql u have juste to iterate around the keywords List and for each keyword u have to execut the sql request , then u count the number of results of the sql request for each type, and return the type that have the greater number – Issibra Dec 18 '15 at 15:07
  • It's possible with only SQL, but it's probably going to need a stored procedure and it's likely to be quite complicated. I agree that it would be far simpler to do the processing in the php and just do minimal work in sql. I'm not sure if that's what the question requires though. – ewanc Dec 18 '15 at 15:11