CODEWITHSUNDEEP
phpmysqlarrayssearch
Interactive
Interactive

Reputation: 1560

PHP/MySql search one value in an array

I'm trying to get values from my usermeta table depending on a certain provence. I live in the Netherlands. We have 12 provences here and I want to select all users who live in a certain provence. So I added a field to the user database that is named 'provincies'. A button with a value <a href="results.php?provincie=provencename" tells me who to select based upon the value from the url.

This isn't difficult. The problem is that users can have multiple provences.
So the meta_key provincie fields hold the meta_value friesland','groningen','drenthe'

So now I need to search if the added value from the url is in the database meta_value.

$provincie_array = "friesland','groningen','drenthe"; 
$query = "SELECT * 
          FROM $wpdb->usermeta 
          WHERE meta_key='provincie' 
          AND meta_value IN ('".$provincie_array."')";

I think this is right. However PHP and MySql beg to differ.

Can anybody see what I'm missing here?

-EDIT-

$provincie = $_GET['provincie'];
                global $wpdb;
                //$query = "SELECT * FROM $wpdb->usermeta WHERE meta_key='provincie' AND meta_value IN ('".$provincie_array."')";

$provincie_array = array('friesland','groningen','drenthe','noordholland','flevoland','overijssel','zuidholland','utrecht','gelderland','zeeland','noordbrabant','limburg'); 
$provincie_check = '';
foreach ($provincie_array as $value) {
    $provincie_check[]="`meta_value` LIKE '%[{$value}]%'";
}
$query = "SELECT * 
          FROM $wpdb->usermeta 
          WHERE meta_key='provincie' 
          AND ( ".implode(' OR ',$provincie_check)." )";
          $personen = $wpdb->get_results($query);

Upvotes: 1

Views: 2193

Answers (3)

TrueSkillZ
TrueSkillZ

Reputation: 307

This will work... :)

For @Interactive: You set $find_provincie = $_GET['provincie']; and that's it.

$find_provincie = 'groningen'; //look for 1
$find_provincie = array('friesland','groningen'); //look for multiple


if(is_array($find_provincie)){
    $provincie_check = '';
    foreach ($find_provincie as $value) {
        $provincie_check[]="`meta_value` LIKE '%[{$value}]%'";
    }
    $provincie_check=implode(' OR ',$provincie_check);
}else{
    $provincie_check = "`meta_value` LIKE '%[{$find_provincie}]%'";
}

$query = "SELECT * FROM usermeta WHERE meta_key='provincie' AND ( ".$provincie_check." )";

Tested and working if you store each element in brackets like: [groningen][drenthe]

Upvotes: 3

Federico
Federico

Reputation: 1251

I think your database sstructure is not Ideal. If you store multiple values in a field you need to search a string.

Try Using " like " statements (not the best, not the fastest) but ok in your case unless you decide to change the database structure.

your query should look like this:

$query = "SELECT *
          FROM $wpdb->usermeta
          WHERE meta_key='provincie'
          AND (
                meta_value LIKE '%friesland%'
                OR meta_value LIKE '%groningen%'
                OR meta_value LIKE '%drenthe%'
          )";

So in php you should do something like this:

$provincie_array = array('friesland','groningen','drenthe');

$querypart = "meta_value LIKE '%".implode("%' OR meta_value LIKE '%", $provincie_array)."%'";

$query = "SELECT *
          FROM $wpdb->usermeta
          WHERE
            meta_key='provincie'
            AND (".$querypart.")";

Upvotes: 0

Ahmed Ziani
Ahmed Ziani

Reputation: 1226

try with this :

$provincie_array = array("friesland","groningen","drenthe"); 
$query = "SELECT * 
      FROM $wpdb->usermeta 
      WHERE meta_key='provincie' 
      AND meta_value IN ('".implode(",", $provincie_array)."')";

Upvotes: 0

Related Questions