asked on

Wordpress User Data Serialezed data

I have a wordpress website and I am trying to get to a piece of User Meta Data that is serialized.
The Meta Value looks like this:
a:2:{s:15:"s2member_level1";b:1;s:23:"access_s2member_ccap_cn";b:1;}

This is what I am looking for: access_s2member_ccap_cn
it can also be : access_s2member_ccap_en, access_s2member_ccap_ja or access_s2member_ccap_en

How do I write a query that says yes, access_s2member_ccap_cn is there. Or Yes, access_s2member_ccap_en is there?
Here is what I have so far.

$sql_l = ("SELECT meta_value FROM wp_dd6xmx2by0_usermeta WHERE meta_key = 'wp_dd6xmx2by0_capabilities' AND user_id = '$user_id'");
        $language = $wpdb->get_results($sql_l, OBJECT);
// EXTRACT THE Language
        foreach ($language as $ls) {
            $lang = $ls->meta_value;
            $lang = unserialize($lang);
            return $lang;
        }

Open in new window

The above query returns something like this:
a:2:{s:15:"s2member_level1";b:1;s:23:"access_s2member_ccap_cn";b:1;}

Member_2_3684445

I think it is easier to use regex for this.

Member_2_3684445

^access_s2member_ccap(en|ja){1,2}$

Member_2_3684445

^access_s2member_ccap_(cn|en|ja)$

Reading your text again ;-)

Use the regexall function

Member_2_3684445

Btw if the search is somewhere in the bigger string remove the ^ (start of string) sign.

(Mobile isnt helping me here sorry for all the spam)

Robert Granlund

ASKER

regexall ? How do I write that?

Member_2_3684445

http://php.net/manual/en/function.preg-match-all.php

Use this to evaluate the sql result before you return the value

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Granlund

ASKER

@ray, is there a way to see if "access_s2member_ccap_cn" is there?
if serialized string contains access_s2member_ccap_cn then $var = "Yes"

Terry Woods

If you only want the query to return a result when the meta_value contains the given string, you could put the regex in the SQL:

$sql_l = ("SELECT meta_value
 FROM wp_dd6xmx2by0_usermeta 
WHERE meta_key = 'wp_dd6xmx2by0_capabilities' 
AND user_id = '$user_id'
AND meta_value REGEXP 'access_s2member_ccap_(cn|en|ja)'
");

Open in new window

Documentation for MariaDB REGEXP is here: https://mariadb.com/kb/en/mariadb/regexp/

Robert Granlund

ASKER

If the query contains it, then I want to create a variable. If the Serialized return contains the string I mentioned then I want to create a variable.

Ray Paseur

You can use the LIKE clause to match on part of a column's data.
https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html
https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

So a query might look something like this... It depends on what other data might be in the meta_value column for $user_id

SELECT meta_value
FROM wp_dd6xmx2by0_usermeta 
WHERE meta_key = 'wp_dd6xmx2by0_capabilities' 
AND user_id = '$user_id'
AND meta_value LIKE '%s2member_ccap_cn' 
LIMIT 1

Open in new window

Member_2_3684445

<?php
$var  = 'a:2:{s:15:"s2member_level1";b:1;s:23:"access_s2member_ccap_cn";b:1;}'."\n";
$var .= 'a:2:{s:15:"s2member_level1";b:1;s:23:"access_s2member_ccap_en";b:1;}'."\n";
$var .= 'a:2:{s:15:"s2member_level1";b:1;s:23:"access_s2member_ccap_ja";b:1;}';

if(preg_match_all('/.{.+"(access_s2member_ccap_(cn|en|ja))/', $var, $matches)){
	print('found: ');
	var_dump($matches['1']);	
}

Open in new window

Outputs:

found: array(3) { [0]=> string(23) "access_s2member_ccap_cn" [1]=> string(23) "access_s2member_ccap_en" [2]=> string(23) "access_s2member_ccap_ja" }

Open in new window

This will evaluate the serialized string in one parse. The problem is that the string itself needs to be normalized (in the database) else you might get unexpected results (see Rays answer earlier about extending the db). For instance always testing the first match given the string with multiple matches.

I hope you will be able to tweak on this. You can use this site to play around with the code: http://phptester.net/