Dividing data into multiple DIV's

[Jim R]

I have about 300 users, and I'm trying to group them geographically according what part of the state their county is in OR if they're reading my site because they have a favorite college team.  I've created a page that I want to show the total count for each group.  There will be 13 groups, and I'd rather not have 13 queries. 

 

Basically, I think, I can't make heads or tails of nested loops.

 

I'd like it to look like this:

 

 

Region 1 (## members)

Region 2 (## members)

Region 3 (## members)

etc.

...

...

...

...

...

 

 

I assume there will be IF and ElseIF statements.  Here is what I'm starting with, but I can't even get the first group working:

 

$query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"';
$results = mysql_query($query);



while($line = mysql_fetch_assoc($results)) {
$meta_value = unserialize($line['meta_value']);

echo '<div>Region 1 (' . $d1 . ' Users)</div>';
if ($meta_value['county'] == '1' || $meta_value['county'] == '2') {

	++$d1;
}

};

[joel24]

why not do something like

//you'll have to reconfigure the SQL query to work with your db setup
$sql = "SELECT region, count(*) 
FROM wp_usermeta 
GROUP BY region"

[Jim R]

Even if the "region" isn't really a column?  I've never dealt with unserialized data before.  That was a suggested earlier, and while it works, I'm trying to extend the information presented to my user.

 

 

[joel24]

how are you determining what region the users are in?

IP address? or signup data?

[Jim R]

With the meta_value noted above.  Here is the raw data:

 

a:1:{s:6:"county";s:1:"1";}

 

The "1" is the region.  Basically, registrants choose what county they live in, and each county is assigned a number of 1-5.

[joel24]

sorry, misunderstood your question.

something like this should work

$query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"';
$results = mysql_query($query);

$region = array();

while($line = mysql_fetch_assoc($results)) {
$meta_value = unserialize($line['meta_value']);

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

};

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

[Jim R]

That works, but I just discovered an issue with the data I need.  I need to also get

WHERE meta_value LIKE '%s2member_level2%'

.  The count has to be where both those conditions are met with the matching user_id.

 

This is all from the same table. 

 

[Jim R]

Union query?  Learning more about it.

[Jim R]

Probably not unions.

[btherl]

Do you have multiple "meta_key" for each user, each with a "meta_value"?  And do you need to check one meta_key for a particular condition and another meta_key for another condition?

[Jim R]

Yes.  Here is what I'm needing to query.

 

$member = 'SELECT * FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%"';
$m_results = mysql_query($member);

$custom = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"';
$c_results = mysql_query($custom);

 

The columns are user_id, meta_key, meta_value.  Keep in mind, what Joel put together worked perfectly, but as I went to implement it, I realized I didn't fully understand what I was needing.  You might be able to use that as a guide.

 

What he wrote provided a total numbers of Users live in each region (based on which county they live in).  The number I need is among those, which ones are subscribers.

 

 

[btherl]

Assuming you have a user id column in wp_usermeta to identify the user:

 

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

 

The idea is for MySQL to check which users are subscribers and make a list of their user ids, and then restrict the custom_fields query by that list of user ids.