PHP/MySql search one value in an array

Question

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);

The last singe quote from the word drenthe, in the $provincie_array should not be there. — Andrei P.
– Andrei P., Commented Mar 26, 2015 at 13:50
True, typo because the list is longer. However problem is still there :-) — Interactive
– Interactive, Commented Mar 26, 2015 at 13:54
@MySQLRockstar The starting and ending quotes are in the IN function — Interactive
– Interactive, Commented Mar 26, 2015 at 13:54
Don't use single quotes at all. $provincie_array = "friesland,groningen,drenthe"; — manoj
– manoj, Commented Mar 26, 2015 at 13:56

TrueSkillZ · Accepted Answer · 2015-03-26 15:04:16Z

3

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]

edited Mar 26, 2015 at 15:04

answered Mar 26, 2015 at 13:55

TrueSkillZ

3171 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Interactive Over a year ago

No it doesn't. No matter where I click I always see the same person

TrueSkillZ Over a year ago

It does work. Tested in local enviroment: s2.postimg.org/h0269r42x/Screen_Shot_20150326160332.png check you code :) maybe it's the same person because you're checking only 1 returned line and not foreach'ing them all?

Interactive Over a year ago

You didn't read my question right. You have every provence in a different row while I have multiple provences in a single table row. And I use a for loop to get them all :-)

TrueSkillZ Over a year ago

You're right, I missed that. The solution would be to write all provinces in the meta_value like: [friesland][groningen] and so on. Each value in brackets like that. then you would do the new query I wrote in my asnwer.

Interactive Over a year ago

I'll try that. Do I need to seperate the [] with a ,

|

Ahmed Ziani · Accepted Answer · 2015-03-26 13:56:59Z

0

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)."')";

answered Mar 26, 2015 at 13:56

Ahmed Ziani

1,2343 gold badges14 silver badges26 bronze badges

1 Comment

showdev Over a year ago

A little explanation would be helpful. What was the problem and what have you done to solve it?

Federico · Accepted Answer · 2015-03-26 14:48:54Z

0

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.")";

edited Mar 26, 2015 at 14:48

answered Mar 26, 2015 at 13:58

Federico

1,2519 silver badges14 bronze badges

2 Comments

Interactive Over a year ago

No luck. Still gives me one person no matter the provence. Hoever I didn't change the datbase what exactly the same is as the $provincie_array

TrueSkillZ Over a year ago

There would be a huge problem is the provinces have similiar beggings or endigns. Lets say you have 'drenthe' and 'drentheng': if you search for 'drenthe' you will find both 'drenthe' and 'drentheng'. I don't think this is a problem for @Interactive , but this method has a huge bug. You could see my answer on how to make it without a bug like this.

Collectives™ on Stack Overflow

PHP/MySql search one value in an array

3 Answers 3

14 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related