Trouble with using serialized data...

[PFMaBiSmAd]

Those are table aliases to save some typing in the query statement. They don't have any meaning outside the query.

[Jim R]

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.

 

[Psycho]

As PFMaBiSmAd has stated you have duplicate column names in the results. But, I still think your query is malformed and is inefficient. For example, look at the JOIN condition for the "wp_users" table

JOIN wp_users u ON um1.user_id=um2.user_id

That is joining the table based upon criteria of the other two tables - which has no effect. The only reason that table is being joined correctly is due to criteria in the WHERE clause. Plus, you are JOINing every record in the two "meta_value" tables together. The only reason you get one record per user is the GROUP BY clause which wouldn't be needed if the JOINs were correct.

 

This query should be more efficient AND should resolve your problem. You will just need to change the logic to reference the aliased names. But, I'm still curious why you are querying all those fields when you are only using the meta field for county in the results, but oh well

 

$query = '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
             ON um1.user_id = um2.user_id
           JOIN wp_users u
             ON um1.user_id = u.ID
           WHERE um1.meta_key = "wp_s2member_custom_fields"
             AND um2.meta_value LIKE "%s2member_level%"';


$result = mysql_query($query);
$region = array();
while($row = mysql_fetch_assoc($result))
{   
    $meta_value = unserialize($row['custom']);
    $region[$meta_value['county']]++;
    //var_dump($row);
}

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

[Jim R]

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']);

 

 

[Psycho]

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']);

 

 

 

You were getting all those additional rows because the JOINs were not done properly. That query was joining every row on every other row. Did you even try the code I posted? I only hobbled that query together because even after NUMEROUS requests you still have not provided information I have requested multiple times now regarding the structure of the data. You could have had this problem solved days ago if you provided that information. If I had a clearer picture of your database this would be so much easier.

 

As for the aliases used that way, that basic stuff so I'm not sure how you've seen them used.

 

After additional consideration, I would probably flip the query to start with the user table and then join the meta table on that two times. But again, that is just a guess because you still have not provided enough details about the table and data structure. I would be happy to provide a better query if you want to attach an export of those two tables.

[Jim R]

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.

 

 

 

[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.

[Psycho]

You didn't change any of the Joins from what I could tell.

I absolutely changed the JOINs - the previous ones were flawed. I don't think you were understanding some of the information I was stating previously. Look at this JOIN

JOIN wp_users u ON um1.user_id=um2.user_id

 

That makes no sense. How can you join table 'u' based upon a condition between table 'um1' and table 'um2'?

 

Again, I am willing to help, but you have still not provided the details that I need. Which is detailed information about the structure of the data. So, I am stuck having to make assumptions. I will give it one last try. If this is not what you need and you fail to provide more information I'm done.

 

I have built a couple sample tables based on what I think your structure and data is. I have also modified the query to a more logical format. This query starts on the user table and then JOINs records from the meta table in two instances (where there is a meta_key = "wp_s2member_custom_fields" AND where there meta_value LIKE "%s2member_level%"). Based upon your comments this is what you want.

 

I also have to make an assumptions based upon how you determine the subscription type since you haven't stated that. I assume it is the KEY s2member_level# in the meta value, but I don't know what values are for what. Also, I still don't see where you want to use the email or login in your output. If you don't need those in the output then remove them from the query as they are not needed.

 

//Create array to define subscription levels
//these are just guesses
$subscriptionLevels = array(
    's2member_level4' => 'Yearly',
    's2member_level3' => 'Semi-annual',
    's2member_level2' => 'Monthly',
    's2member_level1' => 'Weekly'
);

//Create and run query
$query = 'SELECT u.ID,u.user_login, u.user_email, um1.meta_value as custom, um2.meta_value as level
          FROM wp_users u
          JOIN wp_usermeta um1 
            ON u.ID = um1.user_id AND um1.meta_key = "wp_s2member_custom_fields"
          JOIN wp_usermeta um2 
            ON u.ID = um2.user_id AND um2.meta_value LIKE "%s2member_level%"';
$result = mysql_query($query);

//Process results into temp array
$regionData = array();
while($row = mysql_fetch_assoc($result))
{    
    //var_dump($row);

    $custom = unserialize($row['custom']);
    $user_region = $custom['county'];
    $level = unserialize($row['level']);
    $level_desc = key($level); //E.g. s2member_level3
    $user_level = $subscriptionLevels[$level_desc];

    if(!isset($regionData[$user_region]))
    {
        $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0);
    }
    $regionData[$user_region][$user_level]++
}

//Output the results
foreach ($regionData as $region => $data)
{
    echo "Region {$region}<br>\n";
    foreach($data as $subscription_type => $member_count)
    {
        echo "{$subscription_type}: {$member_count} members<br>\n";
    }
}

[Jim R]

I get that, but that aspect of it was still working.  I tested it.  I put in the aliases as you helped me with the rest of my query.  It produced the same results as your changes did.  I definitely see the logic of your point though, and I appreciate your help.  I'm stepping out for lunch right now and will work on that code when I get back.

 

Thank you!

[Jim R]

Why did you change the query?  It worked.  I tried your last one without even looking at the query.  I just looked at how you were creating the array. 

 

Getting rid of um1.user_id=um2.user_id screwed up the count. 

[Jim R]

I added a line break.  It has a couple of odd lines.  One in Region 0.  Another at the end.

 

Region 0

Yearly: 0 members

Semi-annual: 4 members

Monthly: 0 members

: 3 members

 

Region 1

Yearly: 0 members

Semi-annual: 3 members

Monthly: 1 members

 

Region 2

Yearly: 0 members

Semi-annual: 3 members

Monthly: 0 members

 

Region 3

Yearly: 1 members

Semi-annual: 2 members

Monthly: 0 members

 

Region 4

Yearly: 0 members

Semi-annual: 0 members

Monthly: 0 members

: 1 members

[Psycho]

As I have said COUNTLESS TIMES you have not provided the data requested and I am working off of a lot of assumptions. The original query was a mess and I have tried to improve it as I was able to ascertain more information from the various posts.

 

If the count is off either you made a modification or I still don't understand your data structure. Either way, it's your fault.

[Jim R]

You need to explain to me what you mean by data requested if what I've posted isn't good enough.  I posted the data in the post that started this topic, and I noted that couple of posts up.  THAT's the data the Inner Join needed to get.  It's just a sample of the data, but there are over 7,000 rows in that table.  I figured just a few might help.

 

As for the other table, I've noted a few times I just need it for user_login and user_email.

 

Each User has anywhere from 20-30 rows associated to their user_id. 

 

To keep it simple, just know, that line links the user_id on both sides of the Inner Join, and that is necessary to get the correct count.  I've noted that before.

 

What we have now is really close, minus a couple of weird lines.

[Jim R]

I know what those odd lines are now.  They Users with s2member_level4, which shouldn't be part of the print out.  That's a different part of the site.  I didn't think about that. 

 

 

[Psycho]

I still think the query I provided is correct per what you have requested and is much, much simpler. I assume that there is only one record per user in the wp_users table. So, based upon your requirements you want the results to contain one record for every user who:

- Has a meta_key = "wp_s2member_custom_fields"

- Has a meta_value LIKE "%s2member_level%"'

 

The query I provided will do that. I tested it with a very small set of data and it worked. But, I don't have all the information about possible values in your database that might cause the query not to pull the correct data.

 

I am more than willing to provide more assistance, but I really need some real data. Can you do an export from your two tables for a small subset of records? Do something like 20 users and all their associated records from the meta table.

[Jim R]

I think we're good on the data now.  It was the first query you provided, but I added the Group By back.  There are more than one record for some of the Users.  Not sure why, but I think it could have been due to a change in the plugin.  At first, I think it Inserted new rows with each change, where now it updates, because suddenly my last four Users just have one record that matches. 

 

Here is what I have now, with your tweaks and initial query. 

 

$subscriptionLevels = array(
    's2member_level3' => 'Yearly',
    's2member_level2' => 'Semi-annual',
    's2member_level1' => 'Monthly'
);

$query = '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
             ON um1.user_id = um2.user_id
           JOIN wp_users u
             ON um1.user_id = u.ID
           WHERE um1.meta_key = "wp_s2member_custom_fields"
             AND um2.meta_value LIKE "%s2member_level%"
		 GROUP BY um1.user_id';

$result = mysql_query($query);

//Process results into temp array
$regionData = array();
while($row = mysql_fetch_assoc($result))
{    
    //var_dump($row);

    $custom = unserialize($row['custom']);
    $user_region = $custom['county'];
    $level = unserialize($row['level']);
    $level_desc = key($level); //E.g. s2member_level3
    $user_level = $subscriptionLevels[$level_desc];

    if(!isset($regionData[$user_region]))
    {
        $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0);
    }
    $regionData[$user_region][$user_level]++;
}

//Output the results
foreach ($regionData as $region => $data)
{
    echo "<br>Region {$region}<br>\n";
    foreach($data as $subscription_type => $member_count)
    {
        echo "{$subscription_type}: {$member_count} members<br>\n";
    }
}

 

 

Here is what it's producing, which is really close to my needs:

 

Region 0

Yearly: 0 members

Semi-annual: 4 members

Monthly: 0 members

: 3 members

 

Region 1

Yearly: 0 members

Semi-annual: 3 members

Monthly: 1 members

 

Region 2

Yearly: 0 members

Semi-annual: 3 members

Monthly: 0 members

 

Region 3

Yearly: 1 members

Semi-annual: 2 members

Monthly: 0 members

 

Region 4

Yearly: 0 members

Semi-annual: 0 members

Monthly: 0 members

: 1 members

 

Region 5

Yearly: 1 members

Semi-annual: 0 members

Monthly: 0 members

 

I need for Region 0 to not show up at all. 

 

Also, the rows with just a count (one in Region 4, the other is in Region 0), I need for those not to show.  Those represent s2member_level4.

 

[PFMaBiSmAd]

I need for Region 0 to not show up at all.

 

OK, why not devise a method and test it to either A) remove the region 0 data (hint: unset), B) skip over the region 0 data while looping (hint: if), or C) use a loop that specifically loops over just regions 1-5 (hint: for)?

 

 

I need for those not to show

 

OK, why not devise a method and test it to detect the condition that represents the s2member_level4 in the data and skip over displaying those values(hint: if)?

 

To get from "I want"/"I need" in programming to a working result is what programming is all about. Define something you want to happen, then devise (and test) code to do that. Repeat as needed until you are done.

 

Listing the "I want"/"I need" is just the first step. It's up to you to attempt the rest of the steps.

[Jim R]

I come here as my last resort, and I put the problem out there while I work on it.  Sometimes I get it by talking through it.  Sometimes I don't.

 

I would think a "where" in the foreach loop would make the most sense, but I'm not sure that works.  I started to search for it as I sat down then saw the email notification. 

[Jim R]

I got rid of Region 0, but I can't get rid of the s2member_level4 row.  I've tried a few variables. 

 

$subscriptionLevels = array(
    's2member_level3' => 'Yearly',
    's2member_level2' => 'Semi-annual',
    's2member_level1' => 'Monthly'
);

$query = '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
             ON um1.user_id = um2.user_id
           JOIN wp_users u
             ON um1.user_id = u.ID
           WHERE um1.meta_key = "wp_s2member_custom_fields"
             AND um2.meta_value LIKE "%s2member_level%"
		 GROUP BY um1.user_id';

$result = mysql_query($query);

//Process results into temp array
$regionData = array();
while($row = mysql_fetch_assoc($result))
{    
    //var_dump($row);

    $custom = unserialize($row['custom']);
    $user_region = $custom['county'];
    $level = unserialize($row['level']);
    $level_desc = key($level); //E.g. s2member_level3
    $user_level = $subscriptionLevels[$level_desc];

    if(!isset($regionData[$user_region]))
    {
        $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0);
    }
    $regionData[$user_region][$user_level]++;
}

//Output the results



foreach ($regionData as $region => $data)
{if ($region != 0) {
    echo "<br>Region {$region}<br>\n";


    foreach($data as $subscription_type => $member_count)	
    {if ($subscription_type != "s2member_level4") {
        echo "{$subscription_type}: {$member_count} members<br>\n";
}
}
}
}

[Jim R]

Here is the code with comments where I added IF loops:

 

$subscriptionLevels = array(
    's2member_level3' => 'Yearly',
    's2member_level2' => 'Semi-annual',
    's2member_level1' => 'Monthly'
);

$query = '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
             ON um1.user_id = um2.user_id
           JOIN wp_users u
             ON um1.user_id = u.ID
           WHERE um1.meta_key = "wp_s2member_custom_fields"
             AND um2.meta_value LIKE "%s2member_level%"
		 GROUP BY um1.user_id';

$result = mysql_query($query);

//Process results into temp array
$regionData = array();
while($row = mysql_fetch_assoc($result))
{    
    //var_dump($row);

    $custom = unserialize($row['custom']);
    $user_region = $custom['county'];
    $level = unserialize($row['level']);
    $level_desc = key($level); //E.g. s2member_level3
    $user_level = $subscriptionLevels[$level_desc];

    if(!isset($regionData[$user_region]))
    {
        $regionData[$user_region] = array_fill_keys($subscriptionLevels, 0);
    }
    $regionData[$user_region][$user_level]++;
}

//Output the results



foreach ($regionData as $region => $data)
{if ($region != 0) {       // This worked, eliminating Region 0
    echo "<br>Region {$region}<br>\n";


    foreach($data as $subscription_type => $member_count)	
    {if ($subscription_type != "s2member_level4") {     // This did not eliminate s2member_level4 row
        echo "{$subscription_type}: {$member_count} members<br>\n";
}
}
}
}

 

I'm trying to get rid of:

 

Region 4

Yearly: 0 members

Semi-annual: 0 members

Monthly: 0 members

: 1 members      <---------- that row there

 

[PFMaBiSmAd]

Since $subscription_type is what is being echoed to produce the output that you see, how could it have "s2member_level4" in it, since that would echo - "s2member_level4: 1 members"?

[Jim R]

If I knew the answer to that, I wouldn't have tried it.  Is there a chance instead of telling me what doesn't work, which I already know doesn't work, could you show me what does work?  I've tried several of the variables dealing with level, and I've tried that IF in a couple of places.  I simply left it as I last tried it and posted the code here so it could be seen as reference.

 

it's reflecting those with s2member_level4 with that line, and there would be others showing if Region 0 (those who hadn't picked a County) was still showing.  I took out the s2member_level4 from the first array because it's not going to be used.

[PFMaBiSmAd]

var_dump($subscription_type);

[Jim R]

That's not going to tell me what variable to use.  That's just going to confirm what I already know.  It doesn't work.  As I said, that was just the last variable I tried before posting the code.

[PFMaBiSmAd]

There's a reason someone with a huge number of posts suggest that line of code. It is relevant to the problem you are trying to solve and it would have shown you what the value is that you would need to use in your comparison logic.