Trouble with using serialized data...

[Jim R]

Ok...working with serialized data is not fun.  This is how WordPress does it.  I had something working a little, but it wasn't getting all the information I needed.  The query below appears to retrieve all the information I need.

 

What I'm trying to do is determine what type of Subscription (s2member_level) a User gets and where they live (county).  I eventually want to show the total number of each group, as well as the Usernames (only 18 total right now).

 

The below code SHOULD look like this:

 

Region 1:  ## members

Region 2:  ## members

Region 3:  ## members

Region 4:  ## members

Region 5:  ## members

 

 

 

But...right now it just shows

 

Region  :  18 members    (no number after the region)

 

 

$custom = 'SELECT * FROM wp_usermeta um1 
INNER JOIN wp_usermeta um2
JOIN wp_users u
ON um1.user_id=um2.user_id
WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%"
AND um1.user_id = u.ID
GROUP BY um1.user_id';

$c_results = mysql_query($custom);

$region = array();

while($line = mysql_fetch_assoc($c_results)) {

$meta_value = unserialize($line['um1.meta_value']);

$region[$meta_value['county']]++;


};

foreach ($region as $key => $value) {

echo "Region $key: $value members<br>";
}

 

 

 

This is just a sample of the data retrieved for four Users.  I used the Inner Join through help on here.  Each row shows two entries from the same table.  The code above appears to be counting the valid results properly, but it's not dividing them up.  So it appears to work down to the "foreach" part.

 

umeta_id user_id meta_key meta_value umeta_id user_id meta_key meta_value

15624 1073 wp_s2member_custom_fields a:1:{s:6:"county";s:1:"1";} 15617 1073 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";}

16041 1094 wp_s2member_custom_fields a:1:{s:6:"county";s:1:"3";} 16034 1094 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";}

16491 1117 wp_s2member_custom_fields a:1:{s:6:"county";s:1:"2";} 16484 1117 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";}

16671 1126 wp_s2member_custom_fields a:1:{s:6:"county";s:1:"3";} 16664 1126 wp_capabilities a:1:{s:15:"s2member_level2";s:1:"1";}

[Jim R]

Because of the Inner Join, it creates two meta_value columns in the data retrieved.  Is that affecting anything? 

[Jim R]

I did a var_dump, and it produced: 

array(1) { [""]=> int(18) }

 

Not surprising, but I'm still needing help.

[PFMaBiSmAd]

Use var_dump($line); inside your while(){} loop so that you can see exactly what your query is returning (it is only the column names.)

[Jim R]

array(1) { [""]=> int(1) } array(1) { [""]=> int(2) } array(1) { [""]=> int(3) } array(1) { [""]=> int(4) } array(1) { [""]=> int(5) } array(1) { [""]=> int(6) } array(1) { [""]=> int(7) } array(1) { [""]=> int( } array(1) { [""]=> int(9) } array(1) { [""]=> int(10) } array(1) { [""]=> int(11) } array(1) { [""]=> int(12) } array(1) { [""]=> int(13) } array(1) { [""]=> int(14) } array(1) { [""]=> int(15) } array(1) { [""]=> int(16) } array(1) { [""]=> int(17) } array(1) { [""]=> int(18) } Region : 18 members

[Psycho]

Maybe I'm just totally lost, but I don't understand why you are joining the wp_usermeta table on itself. Even more perplexing is how you are joining the wp_users table

JOIN wp_users u ON um1.user_id = um2.user_id

 

It is being joined based upon a relationship between the two instances of the wp_usermeta table and there is no criteria of the wp_users table. Can you please describe the relevant fields from the wp_usermeta and wp_users tables?

 

EDIT: OK, after looking at the code a little more I *think* there is a simple solution. I don't see any reason to JOIN the wp_usermeta table on itself or even to JOIN the wp_users table. Without knowing the DB structure this is sort of a guess, but I think you were just over complicating it. Give this a try:

 

$query = "SELECT meta_value
          FROM wp_usermeta
          WHERE meta_key = 'wp_s2member_custom_fields'
            AND meta_value LIKE '%s2member_level%'";
$result = mysql_query($query);
$regions = array();

while($line = mysql_fetch_assoc($result))
{
    $meta_value = unserialize($line['meta_value']);
    if(!isset($region[$meta_value['county']])) {
        $regions[$meta_value['county']] = 1;
    } else {
        $regions[$meta_value['county']]++;
    }
};

foreach ($regions as $region => $value)
{
    echo "Region $region: $value members<br>";
}

[PFMaBiSmAd]

I seriously doubt that is a var_dump of the $line variable. Looks like a var_dump of $region.

 

 

[Jim R]

I seriously doubt that is a var_dump of the $line variable. Looks like a var_dump of $region.

 

@PFM,

 

Yeah, I misread that.  I already had a var_dump set up, but for $region.  Let's just say it's outputting what it's supposed to.  When I built the query in my database, it outputted what I need.  I'll post it down below.

 

@psycho,

 

I need the user name and email address from wp_users, and your query doesn't match anything.  It's not going to.  No row will have the meta_key and meta_value together.  They will be separate rows, linked by user_id, hence the Inner Join. 

 

 

 

Here is the var_dump($line) 

 

array(16) { ["umeta_id"]=> string(3) "606" ["user_id"]=> string(2) "36" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(2) "36" ["user_login"]=> string(9) "rcsimmons" ["user_pass"]=> string(34) "$P$BgNrG6Q2qhUE7KF72YFTTfLOOTIuvJ." ["user_nicename"]=> string(9) "rcsimmons" ["user_email"]=> string(17) "rcsimmons@bsu.edu" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2009-08-04 16:35:54" ["user_activation_key"]=> string(20) "eQqoh5ptInLT9YTNL4j2" ["user_status"]=> string(1) "0" ["display_name"]=> string(9) "rcsimmons" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(3) "747" ["user_id"]=> string(2) "42" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(2) "42" ["user_login"]=> string(7) "batesec" ["user_pass"]=> string(34) "$P$BdtxXmIxPKFcyATJ0u957.vLBGXDU4." ["user_nicename"]=> string(7) "batesec" ["user_email"]=> string(18) "cbates30@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2009-08-04 17:07:34" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(7) "batesec" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(4) "6680" ["user_id"]=> string(3) "439" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level1";s:1:"1";}" ["ID"]=> string(3) "439" ["user_login"]=> string(9) "jwrbloom1" ["user_pass"]=> string(34) "$P$BiWAfdVjTDyRmqkf308JI8qEDgx/eA0" ["user_nicename"]=> string(9) "jwrbloom1" ["user_email"]=> string(19) "jwrbloom1@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-09-22 12:21:04" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(9) "jwrbloom1" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(4) "6704" ["user_id"]=> string(3) "440" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "440" ["user_login"]=> string(9) "jwrbloom2" ["user_pass"]=> string(34) "$P$BUkGPbRPb0Nu8vVbdX3/ATHUBApjc4." ["user_nicename"]=> string(9) "jwrbloom2" ["user_email"]=> string(19) "jwrbloom2@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-09-22 12:21:20" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(9) "jwrbloom2" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(4) "6728" ["user_id"]=> string(3) "441" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level3";s:1:"1";}" ["ID"]=> string(3) "441" ["user_login"]=> string(9) "jwrbloom3" ["user_pass"]=> string(34) "$P$BJspn/T.Va.k9m8rZDWTo1FFLxB8PG." ["user_nicename"]=> string(9) "jwrbloom3" ["user_email"]=> string(19) "jwrbloom3@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-09-22 12:21:33" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(9) "jwrbloom3" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(4) "6752" ["user_id"]=> string(3) "442" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(3) "442" ["user_login"]=> string(9) "jwrbloom4" ["user_pass"]=> string(34) "$P$BaWT9s06qPTnK3Ga2MUpd72OSKmHPI/" ["user_nicename"]=> string(9) "jwrbloom4" ["user_email"]=> string(19) "jwrbloom4@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-09-22 12:21:49" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(9) "jwrbloom4" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "10835" ["user_id"]=> string(3) "927" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "927" ["user_login"]=> string(12) "jjthomps2007" ["user_pass"]=> string(32) "a297dbbd1c4a28266c470198d25b3e25" ["user_nicename"]=> string(12) "jjthomps2007" ["user_email"]=> string(22) "jjthomps2007@yahoo.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-12-13 21:43:39" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(1) "J" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "10932" ["user_id"]=> string(3) "933" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "933" ["user_login"]=> string(7) "schumes" ["user_pass"]=> string(32) "71c6fcc23e2847d3f79961eb51636107" ["user_nicename"]=> string(7) "schumes" ["user_email"]=> string(21) "cnschumerth@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2010-12-14 00:35:39" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(5) "Chris" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "11322" ["user_id"]=> string(3) "956" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "956" ["user_login"]=> string(9) "jimmer136" ["user_pass"]=> string(34) "$P$Btpj/bChoMVPYRi4O2TgNLaECY5YAT." ["user_nicename"]=> string(9) "jimmer136" ["user_email"]=> string(25) "jmm@northeasterngroup.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-01-17 22:48:17" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(5) "James" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "11909" ["user_id"]=> string(3) "974" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "974" ["user_login"]=> string(9) "bball1959" ["user_pass"]=> string(34) "$P$BB1Sms9vS7WKlZgAx7lviSPFq7cll8." ["user_nicename"]=> string(9) "bball1959" ["user_email"]=> string(13) "tjwbr@msn.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-02-26 15:38:20" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(6) "Teresa" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "11928" ["user_id"]=> string(3) "975" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "975" ["user_login"]=> string(2) "aj" ["user_pass"]=> string(34) "$P$B7n5S/cMHFSHywXWNgvDh.9UxVwnOs0" ["user_nicename"]=> string(2) "aj" ["user_email"]=> string(18) "ahatke@indy.rr.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-02-26 21:46:24" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(2) "aj" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "12619" ["user_id"]=> string(4) "1004" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1004" ["user_login"]=> string(9) "rthoosier" ["user_pass"]=> string(34) "$P$Byj06sGZZ0BESU6VxyexI5wj7cw1Jo0" ["user_nicename"]=> string(9) "rthoosier" ["user_email"]=> string(19) "rthoosier@gmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-03-19 12:41:40" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(5) "Randy" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "13439" ["user_id"]=> string(4) "1030" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1030" ["user_login"]=> string(7) "e060486" ["user_pass"]=> string(34) "$P$B8DAjdo.nHeA3nkAuX3gJPDd1Yl4zq/" ["user_nicename"]=> string(7) "e060486" ["user_email"]=> string(23) "gary_strong@hotmail.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-05-20 17:16:39" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(4) "Gary" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "13770" ["user_id"]=> string(4) "1043" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(4) "1043" ["user_login"]=> string(12) "depauwtigers" ["user_pass"]=> string(34) "$P$Bcu5FFk92nkfUu50dJzak4f4g7d5hP." ["user_nicename"]=> string(12) "depauwtigers" ["user_email"]=> string(17) "boilar@depauw.edu" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-07-19 13:56:38" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(12) "depauwtigers" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "15617" ["user_id"]=> string(4) "1073" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1073" ["user_login"]=> string(17) "HoosierHysterical" ["user_pass"]=> string(34) "$P$BOEuAKBSqak8EHteMgkm52yRYePyCt." ["user_nicename"]=> string(17) "hoosierhysterical" ["user_email"]=> string(22) "valpolaw1232@yahoo.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-08-18 17:30:17" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(13) "John Shanahan" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "16034" ["user_id"]=> string(4) "1094" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1094" ["user_login"]=> string( "kgranger" ["user_pass"]=> string(34) "$P$Br8gJev/A20WkZxKBXqIbmj.cpGIP.1" ["user_nicename"]=> string( "kgranger" ["user_email"]=> string(29) "kenton_c_granger@raytheon.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-10-19 01:37:47" ["user_activation_key"]=> string(20) "Q1YGYwNR9tjmdFNBtl8Y" ["user_status"]=> string(1) "0" ["display_name"]=> string(14) "Kenton Granger" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "16484" ["user_id"]=> string(4) "1117" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1117" ["user_login"]=> string(10) "lisamorlan" ["user_pass"]=> string(34) "$P$B4An85OCSTHbGXIxTAA9cMYQbx/8/p0" ["user_nicename"]=> string(10) "lisamorlan" ["user_email"]=> string(18) "jmorlan136@aol.com" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-12-03 05:40:14" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(11) "lisa morlan" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } array(16) { ["umeta_id"]=> string(5) "16664" ["user_id"]=> string(4) "1126" ["meta_key"]=> string(15) "wp_capabilities" ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1126" ["user_login"]=> string(9) "shelgeson" ["user_pass"]=> string(34) "$P$Br9KEsPSZUJV95mTVMOradlIcEhXCv/" ["user_nicename"]=> string(9) "shelgeson" ["user_email"]=> string(21) "shelgeson@comcast.net" ["user_url"]=> string(0) "" ["user_registered"]=> string(19) "2011-12-22 14:17:50" ["user_activation_key"]=> string(0) "" ["user_status"]=> string(1) "0" ["display_name"]=> string(14) "steve helgeson" ["spam"]=> string(1) "0" ["deleted"]=> string(1) "0" } Region : 18 members

[PFMaBiSmAd]

so that you can see exactly what your query is returning

 

The point of having you use var_dump was so that you could see what the array index names are so that you could correct your code. $line['um1.meta_value'] does not exist in the fetched array.

[Psycho]

@psycho,

 

I need the user name and email address from wp_users, and your query doesn't match anything.  It's not going to.  No row will have the meta_key and meta_value together.  They will be separate rows, linked by user_id, hence the Inner Join. 

 

 

Well, I only worked off the information you provided, which was not much. The original code didn't do anything with user name or email address, so I didn't include that in the code I wrote (You should only query the fields you need, not '*'). And, why would you have separate records to store the meta_key and meta_value data? You have separate fields for each so why not one record per user with both values in their respective fields? Seems like a poor DB design - well the fact that you are storing serialized data is usually a poor option since you can't use a lot of MySQL features against those fields.

 

I think you need to give a clearer picture of the DB design. Do you only have two records in the wp_usermeta table per user (one for the meta_key and meta_value) or can there be many records for each user?

 

Based on the results above, there is no "county" in the serialized values of meta_value. So, the "18" you are seeing is the result of the value $regions[''] being incremented by 1 on each loop that processes the results.

[PFMaBiSmAd]

Unfortunately, this thread is really a continuation of the previous thread where the relevant information was reviled.

[Psycho]

Unfortunately, this thread is really a continuation of the previous thread where the relevant information was reviled.

 

"Reviled" - wow, I didn't think it was that bad 

[PFMaBiSmAd]

Everything to do with Wordpress is vile and reviling. CSC (Clairvoyant Spell Checker) should have known the correct word to put in there.

[Jim R]

The query provides the correct information with the correct count of total rows.  That much is certain.  Why this particular result isn't being unserialized is beyond me.  I've never dealt with serialized data before.  It seems to me to work up until the "foreach" loop. 

 

The previous query provided the breakdown called for in the "foreach".

 

Here is that query:

$custom = 'SELECT * 
FROM wp_usermeta 
WHERE meta_key = "wp_s2member_custom_fields" 
AND user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%")';

 

It didn't provide the information produced by the Inner Join--the second set of meta_key and meta_value columns--which I need.  The Join to wp_users gives me their user name and email, which has worked in both queries though not shown above.

 

 

[Jim R]

Maybe I should carry over my ultimate needs onto this topic.  What I'm trying to get is the following:

 

 

Region 1:

Yearly: ## members        <- s2member_level3

username, username, username, etc

 

Semi-annually:  ## members      <- s2member_level2

username, username, username, etc

 

Monthly:  ## members    <-s2member_level1

username, username, username, etc

 

 

 

I need that for each of the 5 regions.  The previous query just provided the total number for each region, without splitting up what type of subscription they have.  I'm lousy at echo'ing the levels in a hierarchy, and dealing with serialized data isn't doing much for my understanding.

 

 

 

[Jim R]

bump

[Psycho]

. . .  there is no "county" in the serialized values of meta_value. So, the "18" you are seeing is the result of the value $regions[''] being incremented by 1 on each loop that processes the results.

 

The DB results do not contain any data related to "county", "region" or anything similar. So, there is no way to get the results you want. It has nothing to do with the data being serialized. However, you stated later that

The query provides the correct information with the correct count of total rows.  That much is certain.  Why this particular result isn't being unserialized is beyond me.

 

Since you are *certain* the correct data is there I decided not to respond further. I already suggested that the structure of your database and how you are using it are flawed. But, you apparently are not open to the idea of improving it. I also asked for more details on your DB structure so that perhaps we could find a solution to work with the flawed structure. You did not provide that information. If you are not going to use the advice given or provide the requested information there's not much more we can do for you.

 

The bottom line is that the data you want is NOT in the query results. The serialized field only contains a value for "s2member_level4". And, that is it.

[Jim R]

I'm looking at the database results, and the query to get those results were figured out here in another topic. So the question is why is it showing up in the database query but not the var_dump. 

[Psycho]

I'm looking at the database results, and the query to get those results were figured out here in another topic. So the question is why is it showing up in the database query but not the var_dump. 

What do you mean it shows up in the query? Where - exactly - are you seeing the data with 'county' in the serialized data? It is not in your DB results based upon the var_dump of the records extracted. One possibility is that your database has individual records in that table for each piece of meta data associated with each user. Then when you do a GROUP BY the records are collapsed into a single record. Therefore, the meta data field is only going to contain one of the meta records for those records that are grouped. But, that is only one possibility. I'm not going to try and guess what all the possible problems might be when you can simply provide more information about the database structure as I have previously requested.

[PFMaBiSmAd]

You are not getting the 'left' meta_value from the joined result because the result contains two meta_value columns and your SELECT term is not specifying which one you want (the last - 'right' one wins) and if you happen to need both you will need to specifically list them in your SELECT term with appropriate alias name(s) to reference them by.

[Jim R]

@Psycho, in my database viewer, Sequel. 

 

@PFM, I asked two different people that, and they said it didn't matter.  Since it showed up in my query in Sequel I never posed the question.  It makes perfect sense to me, but it doesn't appear to have changed anything.  I'm getting the Selected columns in my database viewer but not the var_dump.

 

Well, I'll post the code and var_dump below:

 

$custom = 'SELECT um1.meta_value,um2.meta_value,u.ID,u.user_login,u.user_email FROM wp_usermeta um1 
INNER JOIN wp_usermeta um2
JOIN wp_users u
ON um1.user_id=um2.user_id
WHERE um1.meta_key = "wp_s2member_custom_fields" AND um2.meta_value LIKE "%s2member_level%"
AND um1.user_id = u.ID
GROUP BY um1.user_id';

$c_results = mysql_query($custom);
$region = array();
while($line = mysql_fetch_assoc($c_results)) {	
$meta_value = unserialize($line['um1.meta_value']);

$region[$meta_value['county']]++;

var_dump($line);
}
foreach ($region as $key => $value) {
echo "Region $key: $value members<br>";
}

 

Here is the var_dump:

 

array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(2) "36" ["user_login"]=> string(9) "rcsimmons" ["user_email"]=> string(17) "rcsimmons@bsu.edu" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(2) "42" ["user_login"]=> string(7) "batesec" ["user_email"]=> string(18) "cbates30@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level1";s:1:"1";}" ["ID"]=> string(3) "439" ["user_login"]=> string(9) "jwrbloom1" ["user_email"]=> string(19) "jwrbloom1@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "440" ["user_login"]=> string(9) "jwrbloom2" ["user_email"]=> string(19) "jwrbloom2@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level3";s:1:"1";}" ["ID"]=> string(3) "441" ["user_login"]=> string(9) "jwrbloom3" ["user_email"]=> string(19) "jwrbloom3@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(3) "442" ["user_login"]=> string(9) "jwrbloom4" ["user_email"]=> string(19) "jwrbloom4@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "927" ["user_login"]=> string(12) "jjthomps2007" ["user_email"]=> string(22) "jjthomps2007@yahoo.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "933" ["user_login"]=> string(7) "schumes" ["user_email"]=> string(21) "cnschumerth@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "956" ["user_login"]=> string(9) "jimmer136" ["user_email"]=> string(25) "jmm@northeasterngroup.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "974" ["user_login"]=> string(9) "bball1959" ["user_email"]=> string(13) "tjwbr@msn.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(3) "975" ["user_login"]=> string(2) "aj" ["user_email"]=> string(18) "ahatke@indy.rr.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1004" ["user_login"]=> string(9) "rthoosier" ["user_email"]=> string(19) "rthoosier@gmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1030" ["user_login"]=> string(7) "e060486" ["user_email"]=> string(23) "gary_strong@hotmail.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level4";s:1:"1";}" ["ID"]=> string(4) "1043" ["user_login"]=> string(12) "depauwtigers" ["user_email"]=> string(17) "boilar@depauw.edu" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1073" ["user_login"]=> string(17) "HoosierHysterical" ["user_email"]=> string(22) "valpolaw1232@yahoo.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1094" ["user_login"]=> string( "kgranger" ["user_email"]=> string(29) "kenton_c_granger@raytheon.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1117" ["user_login"]=> string(10) "lisamorlan" ["user_email"]=> string(18) "jmorlan136@aol.com" } array(4) { ["meta_value"]=> string(37) "a:1:{s:15:"s2member_level2";s:1:"1";}" ["ID"]=> string(4) "1126" ["user_login"]=> string(9) "shelgeson" ["user_email"]=> string(21) "shelgeson@comcast.net" } Region : 18 members

 

[PFMaBiSmAd]

...if you happen to need both you will need to specifically list them in your SELECT term with appropriate alias name(s) to reference them by.

 

You are not using any alias names, so the 'right' meta_value still wins and is the only meta_value key in the fetched array.

 

You are also not looking at the var_dump output or what I wrote in reply #3 and #8, because you are still trying to use the wrong index name in the $line array.

 

Do you have php's error_reporting set to E_ALL and display_errors set to ON? I'm pretty sure unserialize($line['um1.meta_value']) will be producing an undefined index error message.

[Jim R]

If um1.meta_value isn't the alias name, then I don't know what that is.  I clearly thought that was the alias name, as I changed it to that after you said to. 

 

And I do have var_dump($line) inside the While as you said in reply #3.

[PFMaBiSmAd]

This is some information from the php.net documentation under the mysql_fetch_xxxx statements that might help you -

Return Values

...

 

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column.

 

Examples

 

Example #1 Query with aliased duplicate field names

 

SELECT table1.field AS foo, table2.field AS bar FROM table1, table2

 

[Jim R]

How does that help me with the Inner Join?  Table1 would be the same as table2.  I added the AS in my query, and it didn't change the results. 

 

As I understand it, these are aliases:

FROM wp_usermeta um1

INNER JOIN wp_usermeta um2

JOIN wp_users u