Jim R

bump

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.

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.

@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(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

I did a var_dump, and it produced: array(1) { [""]=> int(18) } Not surprising, but I'm still needing help.

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

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.