MySQL find Array Values in DB Array field

i have an array, that can contain different values for example: Array ( [0] => Allgemeine Pharmazie [1] => Geriatrische Pharmazie )

The WordPress DB contains in the meta_value the following array: a:4:{i:0;s:20:”Allgemeine Pharmazie”;i:1;s:22:”Geriatrische Pharmazie”;i:2;s:16:”Fachassistent*in”;s:8:”other_16″;s:8:”Tierarzt”;}

I want to get the user_id of the ones that have the array values in the DB.

I tried out the solution from this question PHP/MySql search array with array

$zuqual = $this->userInput["Zuquali"];
$imploded = (implode(",", $zuqual));
print_r($zuqual); //output Array ( [0] => Allgemeine Pharmazie [1] => Geriatrische Pharmazie )

if(!empty($zuqual)){
   $result = $this->wpdb->get_col($this->wpdb->prepare("SELECT user_id FROM wp_usermeta WHERE meta_key='addition_qualification' AND meta_value IN ('".$imploded."')"));
}
var_dump($result);

But i only get empty results, also I think its not the correct query, because i want the user_id if the array elements (Allgemeine Pharmazie and Geriatrische Pharmazie) are found in the meta_value right?

Thanks in advance for your help 🙂

Answer

Because your data is stored as a PHP serialized array you’ll need to either pull the generic data out first and loop over that, or you’ll need to perform a LIKE query. This post goes into things deeper but I’ll show you how to do the LIKE query for WordPress.

WordPress has a dedicated function for properly escaping LIKE parameters, called wpdb::esc_like. After running through this, the SQL should look something like this:

SELECT
    user_id
FROM
    wp_usermeta
WHERE
    meta_key='addition_qualification'
    AND
    (
        meta_value  LIKE %s
        OR
        meta_value  LIKE %s 
    )

Once you call prepare on that SQL it will get transformed into:

SELECT
    user_id
FROM
    wp_usermeta
WHERE
    meta_key='addition_qualification'
    AND
    (
        meta_value  LIKE '%Allgemeine Pharmazie%'
        OR
        meta_value  LIKE '%Geriatrische Pharmazie%'
    )

This isn’t the most ideal solution but it is probably the best for working with this type of data in a WordPress context. Here’s the code that creates the above:

$zuqual = $this->userInput["Zuquali"];

if (!empty($zuqual)) {
    $likeTemplate = ' meta_value  LIKE %s ';
    
    // This will hold the above string repeated once for each item in our search array
    $likes = [];
    
    // This will hold sanitized values to perform LIKE searches, each surrounded by percent signs
    $params = [];
    foreach ($zuqual as $item) {
        $likes[] = $likeTemplate;
        $params[] = '%' . $this->wpdb->esc_like($item) . '%';
    }
    
    // If we have more than one search term, this will join with the OR, otherwise it will be left as-is
    $likeSql = implode(' OR ', $likes);
    
    // Create our query, remembering to surround the nested part with parentheses
    $sql = "SELECT user_id FROM wp_usermeta WHERE meta_key='addition_qualification' AND (" . $likeSql . ")";
    
    // Pass our escaped params in
    $prepared = $this->wpdb->prepare($sql, $params);
    $result = $this->wpdb->get_col($prepared);
}