Jim R

I probably can't do the subscription levels the same way as I did the Regions, can I? I'd have to associate the s2member_level1 to Monthly, s2member_level2 to Semi, and s2member_level3 to Yearly, then count each set, then echo in the usernames.

Yes, I tried the code, and it works. I noted that it was progress. You didn't change any of the Joins from what I could tell. I had never set up aliases in the Select part. I posted the data on the first post. The Join you don't like links the two rows or data to a single ID. Not everyone who has Subscribed listed their County. Not everyone who has put in their County is a Subscriber. I don't want it to pull people who listed their County or people who have Subscribed. I only want it to pull those who have both. -- ON um1.user_id=um2.user_id -- links the ID's on each side of the Join. Otherwise, it pulls 97 rows, AND it just shows them all as having the same subscription (not sure why that would be the case). With that line, 18 rows, correct associations. You changed it around a little, but here is the query I had before, altering the way the aliases are set up: SELECT um1.meta_value AS custom,um2.meta_value AS level,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 It gave me the same results. I get what you're saying, but that query was formed on here in another, helped in part by PMF. I do see, however, the logic in how you reformed the query and will stick with it. Now, my last issue (I think) is something I posted earlier. How do I get the output to look like this: (I struggle with printing hierarchies) Region 1: Yearly: ## members Semi-annual: ## members Monthly: ## members Region 2: Yearly: ## members Semi-annual: ## members Monthly: ## members etc...through Region 5.

Without it, it gives me 97 rows, instead of 18. It matches the two criteria to similar user_id's. Otherwise, it would just pull every instance. I just need the ones that are associated with the same User. Just like without the Group By, I get 42 rows instead of 18. I need Users who have set up their County of residence and are Subscribers, and for reasons of listing them, I want their User name and email address. There is some progress. The var_dump shows the County fields, so that's good. I appreciate you putting the code up. I've never seen aliases set up that way. For sanity's sake, I changed $meta_value to $county. So that's looking good. I assume if I want to unserialize the s2member_levels, I just duplicate it like this: $level = unserialize($line['level']);

I've never had any issues with the table query not working in a PHP query until now. I've worked with aliases before but not with Inner Joins, which is why this has been troublesome. Can you, looking at my code, just tell me what it should look like instead of what it isn't? Because what you showed me doesn't appear to apply to Inner Joins where table 1 and table 2 are the same.

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

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.

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

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.

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.