php/mysql. Is there mysql function to detect if at least 2 certain words exists in at least one mysql row-field

Question 1

For example, I have php array $php_arr_to_check = array("green", "red", "blue");

And have many mysql rows like this

Id  |  TextToCheckIn 
______________________
1   |  green brown blue
2   |  black yellow white red
3   |  yellow green black red grey white

Want as if to loop $php_arr_to_check through each row in column TextToCheckIn and return Id where, for example, at least 2 words from $php_arr_to_check exists in TextToCheckIn.

Is it possible? Does any mysql function exist?

Here is example with php (what I want to get)

$php_arr_to_check = array("green", "red", "blue");
echo '<pre>', print_r($php_arr_to_check, true), '</pre> php_arr_to_check <br/>';

$mysql_rows = array(
array("green", "brown", "blue"),
array("black", "yellow", "white", "red"),
array("yellow", "green", "black", "red", "grey", "white")
);
echo '<pre>', print_r($mysql_rows, true), '</pre> mysql_rows <br/>';

foreach( $mysql_rows as $one_row ){
//echo count(array_intersect($php_arr_to_check, $one_row)). ' count array_intersect __ <br/>';
if( count(array_intersect($php_arr_to_check, $one_row)) > 1 ){
$arr_with_count_matches[] = count(array_intersect($php_arr_to_check, $one_row));
$maches_exist = true;
}
}

echo '<pre>', print_r($arr_with_count_matches, true), '</pre> arr_with_count_matches <br/>';

Want to get the same result comparing php array to mysql column.

Any ideas how to do that (without LIKE)? May be with MATCH AGAINST somehow?

Before i used below, but got as if false positives....

SELECT 
Id FROM table 
WHERE MATCH (TextToCheckIn) AGAINST (?) LIMIT 1

Will try

SELECT 
Id FROM table 
WHERE MATCH (SUBSTRING_INDEX(TextToCheckIn, ' ', 3)) AGAINST (?) LIMIT 1

As understand I will compare first 3 words in TextToCheckIn against php array. But do not understand what i will get. Reading here http://www3.physnet.uni-hamburg.de/physnet/mysql/manual_Fulltext_Search.html

For every row in a table it returns relevance - a similarity measure between the text in that row (in the columns that are part of the collection) and the query.

Seems it is exactly what is necessary... need to check

Question 2

I think you want to use Boolean Full-Text Search

If you match without operators + - against such as green red blue all rows are returned, where a record contains at least one word: green or red or blue.

IN BOOLEAN MODE and without operators each matched word will score 1. So if there's a record matching two out of the three words it would score 2.

To get the rows with at least 2 score:

SELECT *,
MATCH (`TextToCheckIn`) AGAINST ('green red blue' IN BOOLEAN MODE) `score`
FROM `my_tab` WHERE 
MATCH (`TextToCheckIn`) AGAINST ('green red blue' IN BOOLEAN MODE)
HAVING `score` >= 2
ORDER BY `score` DESC

In Natural Language mode scoring works completely different. Think it's primarily based on BM25.

On large datasets boolean fulltext search (using a fulltext index) usually outperforms REGEXP or LIKE by far if matching words somewhere in the text. Would only use like/regexp for matching from the initial such as REGEXP '^word' or LIKE 'word%' - if an index can be utilized.

Question 3

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Question 4

You can simply use LIKE statement described in this manual (yes, even on serialized objects).

I'd suggest you to create an entity class for roles and join it with the user entity by ManyToMany association.

Question 5

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Question 6

Just found out:

You can run with -Dprism.verbose=true which will print what graphics pipeline it uses. "sw" or "j2d" for software and "d3d" or "es2" for hardware accelerated.

php/mysql. Is there mysql function to detect if at least 2 certain words exists in at least one mysql row-field

Answers

php,mysql