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)";
精彩评论