Compare two sql tables

Question

I have two tables: one with text strings and one with words.

How to take each row / row in table A and see how many of the “words” from table B each row in table A contains - and get this result:

TABLE A
id: 1 contains 2 words (car, red) from table B

id: 2 contains 2 words (house, hill) from table B

id: 3 contains 0 words from table BB id: 4 contains 2 words (small, boot) from table B

Answer 1

<?php
// Get $TableA from TableA and $TableB from Table B (std code)

$result = array();
foreach ($TableA as $id => $string) {
    $result[$id] = array();
    $words = explode(' ', $string);
    foreach ($words as $word) {
        if (in_array($word, $TableB)) {
            $result[$id][] = $word;  
        }
    }
}

// - display $result - left as exercise!

Answer 2

$mysqli = mysqli_connect("localhost", "dbusername", "dbpassword", "dbname");
$tableA = $mysqli -> query( 'SELECT * FROM TABLE_A' );
$tableB = $mysqli -> query( 'SELECT * FROM TABLE_B' );   
$str = '';
$substr = '';
$count = 0;
for( $i = 0; $i <= sizeof( $tableA ); $i++ )
{
    $string = $tableA[$i]['string'];
    $str .= 'id: ' . $i . 'contains ';
    for( $m = 0; $m <= sizeof( $tableB ); $m++ )
    {
        $words = $tableB[$m]['words'];
        if( strpos( $string, $words ) !== false )
        {
            $count += 1;
            $substr .= $words . ', ';
        }
        $substr = rtrim( $words, ', ' );
        $str .= $count . 'words (' . $substr . ') from TABLE B <br>';
     }
}
echo $str;

Answer 3

SQL- CROSS JOIN , LIKE . SUM , , GROUP_CONCAT:

SELECT t1.id, SUM(CASE WHEN t1.string LIKE CONCAT('%', t2.words, '%') 
                       THEN 1 
                       ELSE 0 
                  END) AS WordCount,
              GROUP_CONCAT(CASE WHEN t1.string LIKE CONCAT('%', t2.words, '%') 
                                THEN t2.words 
                                ELSE NULL 
                           END) AS Words
FROM Table1  t1
CROSS JOIN Table2 t2
GROUP BY t1.id

. .

WARNING: CROSS JOINcan degrade the performance of large tables, since a Cartesian product (all possible combinations) runs between both sets, which can run quickly in millions.