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.

@Andy, That worked very well. Thank you. @PFMaBiSmAd I'm still curious about your security concern. Is it really an issue considering how the output will be? I'm sure I'll be back trying to get help on how to display it how I want. I have a bit of an idea, but I'll give it a shot. It will eventually look like this for each region: Region # (# of subscribers) Yearly: user, user, user, user, user, etc Semi-annual: user, user, user, user, user, etc Monthly: user, user, user, user, user, etc

The output is just going to be a list of where subscribers reside, and that will only be available to my editors. Users don't have the opportunity to change their own capability beyond subscribing, and none of the subscription levels affects what they can see on the back end. Which is also to say I don't follow what your concern is.

OK...I tested it, and that appears to be the case now, which is good. It doesn't appear that has always been the case. I have only one custom field, and my test accounts clearly prompted separate rows of data when I changed its County. The four most recent accounts don't have duplicates. It could have been a change via an update in the last month.

It's a County code, not country, just one number, 1-5. I set up the custom field within the plugin. Zero is the default, and all new subscribers will have to declare in the future.

It doesn't write it back into the row. It creates a separate row. WP isn't really creating the entry, a plug-in is.

The query I have works except for duplicates. I just need the latest submission (change in their profile) from the User.

:-)

We are well beyond that query.

I changed the quotes in the Where line to double quotes. It didn't change anything.

It's just the unique key for that table. You asked me to show you the data, and I did. umeta_id | user_id | meta_key | meta_value 13068 | 439 | wp_s2member_custom_fields | a:1:{s:6:"county";s:1:"1";} 15195 | 439 | wp_capabilities | a:1:{s:15:"s2member_level1";s:1:"1";} 15569 | 439 | wp_s2member_custom_fields | a:1:{s:6:"county";s:1:"0";} 15195 | 439 | wp_capabilities | a:1:{s:15:"s2member_level1";s:1:"1";} Those are the columns. With each change a new row is shown. The query takes each pair of rows in the table and makes in one row in the query. I had a database issue switching from WP-multiuser to WP, and it duplicated some early users. I eliminated that data for you. What you see above now is the User switching what County they live in. I only want it to reflect the most recent, based on user_id.

This would be one example: (I assume um1.meta_id could be differentiated from um2.meta_id.) umeta_id,user_id,meta_key,meta_value,umeta_id,user_id,meta_key,meta_value 13068,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"1";},6680,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 13068,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"1";},15195,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 15569,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"0";},6680,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";} 15569,439,wp_s2member_custom_fields,a:1:{s:6:"county";s:1:"0";},15195,439,wp_capabilities,a:1:{s:15:"s2member_level1";s:1:"1";}

What appears to be happening is it's reflecting all the inputs, so if a User (such as my test accounts and a few others) have gone in and switched where they live. I'm getting their old and current locations. Is there a way to limit my search to the latest input from each ID?

That didn't have any effect. That's taken care of with: AND um2.meta_value LIKE "%s2member_level%" It would be: meta_key | meta_value wp_capabilities | %s2member_level%

Ok...I think this is getting what I'm wanting, but I'm getting repeats. It makes sense, but I don't want repeats since I'm eventually going to make a count of how many of each type of entry I have. The custom field shows where the User lives by a defined district. The s2member_level shows what subscription level the User has. I brought in the Join because it adds the user name and email address to the results. 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 ORDER BY um1.user_id

Looking at Inner Joins, what is the "On" condition determining? Could that be my ultimate path?

Do you mean an inner join? I've not dealt with it, and the examples I see deal with different columns.

"AND" produced zero results. "OR" produced 201. It should be closer to 19 or so. They have to be of the same user_id. One row is noting what subscription level they have. The other tells what county they live in.

I'd like the two relevant rows of data for each ID that it applies to.

Uhm...I do like to think. The rows pulled have to have the same user_id. They don't.

I tried it. It pulled a number of rows that don't have the s2member_level value.

I'm not sure yet what that gave me. It appeared to pull anything with wp_capabilities, whether if it had s2member_level or not.

Yeah...both rows of data for each user that met the criteria.

Instead of 19 rows of data, it produced 1775 rows of data.