开发者

Select rows where column text contains value of an array

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     | W开发者_开发问答e 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!


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


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 :)


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 


Something like this maybe ...

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜