3

I have a select statement where if a string contains any of the words below, it'll select it, but it doesn't select it in order of the words contain. For example, if the string in the database is "three two one", and not "one two three", it'll select it anyway. I want to make it, so that it prioritize in the order of the string first, before trying to get those that's not in that order. Is that possible?

$text1 = "one";
$text2 = "two";
$text3 = "three";

SELECT text,
((text LIKE '%$text1%') + (text LIKE '%$text2%') + (text LIKE '%$text3%')) as `matches` 
FROM tableName 
HAVING `matches` > 0
ORDER BY `matches` DESC, rand() LIMIT 1

More examples: So, if there are 2 strings in the database.

One is 

"one two three"

and the other is

"three two one"

I would like the one with "one two three" to be selected first, before going on to the "three two one" if there aren't any "one two three";

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
jessica
  • 1,667
  • 1
  • 17
  • 35
  • You could try some sort of rating using INSTR() values. You also need to clarify things such as this: should "one two" have a better rating than "three one two"? – Uueerdo Sep 15 '15 at 23:23
  • @Uueerdo Please try answering it. – jessica Sep 15 '15 at 23:24
  • @Uueerdo To your answer, they should have the same rating. Of course, it's better if "one two" has a better rating than "three one two", but I think making it the same rating would be a lot easier if you can possibly come up with something like that. Please help if you can. – jessica Sep 15 '15 at 23:32
  • it's possible, but what if the strategy employed goes right off your question, and then you say, oh, they can search on 14 words. So I hesitate to even answer this – Drew Sep 16 '15 at 00:09
  • @Drew What do you mean? The example I provided has 3 words, but I actually need for it to search 10. – jessica Sep 16 '15 at 00:26
  • @Drew If you even have the slightest chance of providing a correct answer, please try. Any answers would be a good answer for me at this point, and even if it doesn't work. I'll upvote it, if no one else answers. – jessica Sep 16 '15 at 00:27
  • I will give you an answer. but having read 5k questions on the stack, I suspect the question is ultimately something different, which you just said (10 words for search). I will slap something together or someone will, fret not – Drew Sep 16 '15 at 00:37
  • But you need to read up on mysql `Full Text Search`, and let me know if that is what you are really needing, because that wheel is about as round as it is going to get. And what you are suggesting is a tablescan. Not indexes pretty much 99% of the time – Drew Sep 16 '15 at 00:39
  • Full Text Search answer of [mine](http://stackoverflow.com/a/30677347). It is far from authoritative **by a country mile**. It is a nice feature bloated on top of mysql that shouldn't even have it in my opinion, which is why I am over doing work in `solr` – Drew Sep 16 '15 at 00:42
  • @Drew, using the multiple word matching, I searched for 'he', and 'pen', and while one the the results has only the word 'pen', in it, the other results has the words 'pen' and 'he'. However, there are some results with and without the word 'he', and without the word 'pen' that shows up with 0 relevance anyway. – jessica Sep 16 '15 at 00:50
  • the critical points about `Full Text Search` is that it need a lot of data, and small words are **STOP** words, and it needs to be trained by configurations. So it is a pathetic example because I didn't want to put a 40k row table on the stack. It is a freebie from Oracle, it facilitates the needs of simplistic, relatively easy setups, and the results are suspect. Then people move on to solr, lucene, elasticsearch, fastsearch, etc, technologies that have Full Text Search as a core competency, a core competency that mysql does not. – Drew Sep 16 '15 at 00:52
  • Maybe get the results without sorting them in mySQL, but subsequently sort them in PHP by order of strpos() of each of the input strings in matches? Probably a bit hideous for large sets of data though... – markdwhite Sep 16 '15 at 00:55
  • It seems to be somewhat appropriate then, and it seems like it could work with my example above, if you could come up with something similar with my example? My database's table are expected to have a lot of the data. I just tried out the third "real weight" search, and it seems to work fine. – jessica Sep 16 '15 at 00:55
  • The weighted one is my favorite with real weight. It needs a lot of data, not 15 rows. And don't search on the word `the`. As for a write-up, I guess I did over there in that link – Drew Sep 16 '15 at 00:56
  • @Drew I have a lot of data in my table, so there's no need to worry about that. – jessica Sep 16 '15 at 00:56
  • read up on it then. create perhaps a copied table, mess with it being myISAM, look at FULLTEXT indexes. All that. People write chapters on it. Best to go there. If you love my answer over there, then give it a thumbs up over there :) – Drew Sep 16 '15 at 00:58
  • @Drew Sure things. I'll upvote it. – jessica Sep 16 '15 at 00:59
  • only if it helped. :) Good luck. – Drew Sep 16 '15 at 01:00
  • @Drew Um. Sorry to bother you again, but does the id has to be those weird symbols? Can the id be numbers instead? Because my id are numbers, and when I search it using that function in one of my big tables with the words "air" and "kids", and it returned a "Can't find FULLTEXT index matching the column list" message. The words air and kids are in same row in the table. – jessica Sep 16 '15 at 01:07
  • yes the `id` can be whatever, note over there I was working directly off the OP's Question there, so those were his stuff. – Drew Sep 16 '15 at 01:09
  • @Drew, yeah I know. I replaced the table column names and table name, and everything with my table name and column names. – jessica Sep 16 '15 at 01:11
  • As for the last part of your comment, ... the schema for my link was what it was, your schema is different. Marry the two. Create a new table with a full text index, like I did with `FULLTEXT(prodname)`. I mentioned a few comments up about playing with it in a safe other table to start with – Drew Sep 16 '15 at 01:11
  • So create the new sandbox table to play with, and do an `insert into select` pattern to bring the data over, **after** the sandbox has the same concept of column types like TEXT, the FULLTEXT index, and myISAM on the table – Drew Sep 16 '15 at 01:15
  • @Drew Thanks. I got it working. Just one more question. Is there a difference between '+harpoon +article', and 'harpoon article', without the plus symbols? Thanks. – jessica Sep 16 '15 at 21:06
  • ah so you didn't go with like statements afterall :) Found out it didn't perform so fast now did it. The plus and minus signs are there for a visual of I want, I don't want. So far, you want to include only it seems – Drew Sep 16 '15 at 21:12
  • @Drew So the + symbols are unnecessary? – jessica Sep 16 '15 at 21:18
  • yes they are for your situation definitely – Drew Sep 16 '15 at 21:19
  • @Drew Should I add, "-the" to it then? Since we don't want to include "the", right? Since it's everywhere, and not a good search word? – jessica Sep 16 '15 at 21:23
  • Definitely read the manual stuff on all that. Manual meaning authoritative references, wherever they are, often great blogs, etc. Those are **stop** words. So **no**. Users entering `the` won't matter then. It won't bring up the `the` articles – Drew Sep 16 '15 at 21:29
  • @Drew Ahhh. So that's what stop words are. It doesn't register in the database even if users try to search using them. That's good to know. – jessica Sep 16 '15 at 21:34
  • improved that other answer a bit for stop words section – Drew Sep 16 '15 at 22:03

1 Answers1

2

You can specify the priority in the order by:

SELECT text,
       ((text LIKE '%$text1%') + (text LIKE '%$text2%') + (text LIKE '%$text3%')) as `matches` 
FROM tableName 
HAVING `matches` > 0
ORDER BY `matches` DESC, 
         (case when text like '%$text1%$text2%text3%' then 1
               when text like '%$text3%$text2%text1%' then 2
               . . .
          else 999 end),
         rand()
LIMIT 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Well, actually there being only 3 words is only an example. My actual database search needs much more words, and typing different cases for all the different word combination would just not be pragmatic, as there are almost an infinite amount of ways the words can be combined. In this case, I think full text search will be easier to accomplish. – jessica Sep 16 '15 at 11:31