Viewed   105 times

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....

Id FROM table 

Will try

Id FROM table 

As understand I will compare first 3 words in TextToCheckIn against php array. But do not understand what i will get. Reading here

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



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:

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

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.

Wednesday, October 19, 2022

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.

Saturday, November 5, 2022

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.

Tuesday, November 15, 2022

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 
Wednesday, August 17, 2022

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.

Saturday, December 17, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :