Select rows where column text contains value of an array

Question

I'm building a search function in php/mysql and I'm looking for the right MySql function. My table sort of looks like this:

id    | text
-------------------------------------- 
1     | I like pony's.
2     | Do you like fish?
3     | We like fishes!

I want to search the column 'text' for one of the exact values of an array, for example:

$search_array = array('fish','dogs','cat','panda');

I'm looking for the right MySql function to return only the second row (with the current array). The array can contain hundreds of values.

I have 6000+ rows, growing everyday with +/- 400. I've tried REGEXP but with a large array, it took about 10 seconds before it returned the corresponding rows.

Please help, I'm fighting with this for almost 3 full days now... Thanks in advance!

what is that data and what's purpose of such search? there is probably another way to do it — Your Common Sense, Mar 26 '11 at 13:14
You might find the following question helpful: http://stackoverflow.com/questions/1568068/mysql-select-like-must-match-whole-words-only-to-the-variable — Emanuil Rusev, Mar 26 '11 at 13:34
well, the text column actually contains tweets. I'd like to create some sort of categories: each category would be an array of keywords. If the tweets contain one of the keywords, then they should be selected. Does that make it clear? — Ben Rogmans, Mar 26 '11 at 13:36

score 1 · Answer 1 · answered Mar 26 '11 at 13:17

1

If the search array is constant, or changes infrequently, I recommend having another two tables, 'tags' and 'tags-text'.

For example, the row with id 2 in your example contains fish, since fish is in our 'tags' table a new record will be placed in a 'tags-text' table. When you are searching with your array, you can search if one of the array components is in the 'tags-text' table, and join the 'text' table and return the text and id and do whatever you need.

Structure of other tables:

'tags' table

id    | tags
-------------------------------------- 
1     | fish
2     | dogs
3     | cats

'tags-text' table

text-id  | tags-id
-------------------------------------- 
2        | 1

Does this help/make sense

answered Mar 26 '11 at 13:17

Andrew Jackman

13,781
7
35
44

+1 for following not the question as it phrased but rather common sense – Your Common Sense Mar 26 '11 at 13:18
I won't be surprised if his data turned to be comma-separated collections – Your Common Sense Mar 26 '11 at 13:23
That's true: comma seperated or seperated by | – Ben Rogmans Mar 26 '11 at 13:38
@bEN What is comma-separated in your data? Didn't you say it's tweets? Anyway this answer is closest to your needs one – Your Common Sense Mar 26 '11 at 13:41
Yep it's tweets. I do have another table with keywords, which is comma-seperated. Sorry for the unclearness. – Ben Rogmans Mar 26 '11 at 13:45

score 0 · Answer 2 · answered Mar 27 '11 at 18:16

Ok I think I've found the easiest solution: let PHP create the mysql query and solve it with WHERE LIKE.

$search_array = array('fish','dogs','cat','panda');

$string = '';

foreach($search_array as $term) {
    $string = $string."text LIKE '%".$term."%' AND ";
}

The result of the foreach loop is:

"text LIKE '%fish%' AND LIKE '%dogs%' AND LIKE '%cat%' AND LIKE '%panda%' AND "

Now lets remove the tail of that string and write the query:

$string = substr($string, 0, -5); // removing " AND " at the end of the string

$query = "SELECT * FROM table WHERE $string";
$results = mysql_query($query);

Thanks for the other answers anyway :)

score -1 · Answer 3 · answered Mar 26 '11 at 13:18

-1

Ok, maybe you should try mixing mysql and php a bit. Here is the pseudo-code

select 100-1000 rows at one time from db
   use strpos to check each element in your array against the text column
       if element found 
           store it 
           if 2 elements found break the loop
       else 
           continue

answered Mar 26 '11 at 13:18

johnlemon

20,761
42
119
178

he only want two rows, if the array is big the loop will only have to be processes a few times – johnlemon Mar 26 '11 at 13:22
he should have said ( second row.. ) not row 2 – johnlemon Mar 26 '11 at 13:28

score -1 · Answer 4 · answered Mar 26 '11 at 13:20

-1

Something like this maybe ...

$search_term = implode(",",$search_array);
SELECT * FROM your_table WHERE text IN ($search_term)";

answered Mar 26 '11 at 13:20

sdot257

10,046
26
88
122

If you use IN, then 'text' should precisely match one of the array's values right? – Ben Rogmans Mar 26 '11 at 13:39
Yea I believe you're right, how about this. `select * from your_table where text REGEXP 'fish|dogs|cat';` You'll need to manipulate your array accordingly so that it inserts into the SQL statement correctly. – sdot257 Mar 26 '11 at 13:43
Hm hm, that's what I tried at first. Somehow that took seconds to search as the regexp string became longer.. – Ben Rogmans Mar 26 '11 at 13:47
Did you see the link someone posted in their comment? The one under your original post, maybe that could help? – sdot257 Mar 26 '11 at 13:50
Yep, the selected answer there is full text search. That's what I'm using now actually but it doesn't fit my needs: i orders results by relevancy while I'm looking for an exact match - or not.. – Ben Rogmans Mar 26 '11 at 13:59

Select rows where column text contains value of an array

4 Answers4