Find a string in MySQL table through a loop in multiple arrays

I am using WordPress, and I need to create a function to check for typos in a custom table, by comparing the single values with a comparison table. The values to be checked are animal species names, and they stored as follows in table A

id | qualifying_species
----------------------
1  | Dugong dugon, Delphinus delphis
2  | Balaneoptera physalus, Tursiops truncatus, Stenella coeruleoalba

etc.

These values must be checked for typos by matching them with table B which contains a simple list of species name as a reference

id | species_name
----------------------
1  | Dugong dugon 
2  | Delphinus delphis
3  | Balaneoptera physalus
4  | Tursiops truncatus
5  | Stenella coeruleoalba

Here’s the code I prepared

function test(){
global $wpdb;
$query_species = $wpdb->get_results("SELECT qualifying_species FROM A", ARRAY_A);

                    foreach($query_species as $row_species)
                    {
                        $string = implode (";", $row_species);
                        $qualifying_species = explode(";", $string);

                        //echo '<pre>';
                        //print_r($qualifying_species);
                        //echo '</pre>';

                        foreach ($qualifying_species as $key => $value) {
                            //I get here the single species name
                            echo $value . '<br>';
                                //I compare the single name with the species list table
                                $wpdb->get_results("SELECT COUNT(species_name) as num_rows FROM B WHERE species_name = '$value'");
                                    //if the species is written correctly, it will be counted as 1 with num_rows
                                    //if the species is written wrongly, no match will be found and num_rows = 0 
                                    echo $wpdb->num_rows . '<br>';
                        }
                    } 
}

The echo was to check the results of the function. The Mysql query works when I do it on PHPMyAdmin, but it seems that something is wrong with the PHP loop that I wrote. For each $value echoed I have a result of 1 echoed with $wpdb->num_rows even if $value presents typos or doesn’t exist in table B

What am I doing wrong?

Answer

Possible solutoin for MySQL 5.7.

Create a procedure (must be performed only once):

CREATE PROCEDURE check_table_data ()
BEGIN
DECLARE sp_name VARCHAR(255);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT species_name FROM tableB;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN cur;
CREATE TEMPORARY TABLE t_tableA SELECT * FROM tableA;
FETCH cur INTO sp_name;
REPEAT
    UPDATE t_tableA SET qualifying_species = REPLACE(qualifying_species, sp_name, '');
    FETCH cur INTO sp_name;
UNTIL done END REPEAT;
CLOSE cur;
SELECT id, qualifying_species wrong_species FROM t_tableA WHERE REPLACE(qualifying_species, ',', '') != '';
DROP TABLE t_tableA;
END

Now, when you need to check your data for unknown species and misprintings you simply execute one tiny query

CALL check_table_data;

which will return id for a row which have a species value not found in tableB, and this species itself.

fiddle

The code assumes that there is no species_name value which is a substring of another species_name value.

---

The procedure do the next: it makes the data copy then removes existent species from the values. If some species is wrong (is absent, contains misprint) it won’t be removed. After all species processed the procedure selects all rows which are not empty (contain non-removed species).