Serialized data question

[Jim R]

I'm weak with serialized data.  The code below is also through a lot of help on here, and it queries two rows from the same data table for each User.  I need to add an extra layer or trigger to my output.  Right now the code determines:

 

1)  Determine who has subscribed

2)  Among subscribers, which region they live in relative to their county of residence.

 

It generates an ordered list:

 

Region 1

Yearly: 0 members

Semi-annual: 4 members

Monthly: 1 members

 

Region 2

Yearly: 0 members

Semi-annual: 3 members

Monthly: 0 members

 

Region 3

Yearly: 1 members

Semi-annual: 4 members

Monthly: 2 members

 

Region 4

Yearly: 1 members

Semi-annual: 1 members

Monthly: 0 members

 

Region 5

Yearly: 1 members

Semi-annual: 0 members

Monthly: 1 members

 

$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

if ($level_desc != "s2member_level4") {	
    $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)	
    {

//var_dump($subscription_type);
        echo "{$subscription_type}: {$member_count} members<br>\n";
}

}
}

 

 

What I need it to do is:

 

1)  Determine who has subscribed (which it already does)

2)  Determine what they are a fan of (reason) high school basketball or any of 10 colleges in Indiana

3)  If they are a fan of any of the colleges, group them accordingly

4)  If they are a fan of high school basketball "hsbball", group them according which region they live in

 

 

Below are the two rows of data for each User is queried.  There are 12 different "reasons", determined in the first row.  In the second row, that's what determines if a User has subscribed.  I have that part taken care of above. 

 

meta_key

meta_value

wp_s2member_custom_fields

a:2:{s:6:"reason";s:2:"ue";s:6:"county";s:1:"4";}

wp_capabilities

a:1:{s:15:"s2member_level2";s:1:"1";}

[gizmola]

If this is important data that you want to query on, why are you seralizing it in obtuse strings that can't be queried?  That defeats the entire purpose of using a relational database and forces you to write volumes of code.  For example, if you ever want to query and get a list of people who "are a fan of highschool basketball" + some other criteria, you guarantee with this structure that you will read every single row from the database every single time you want that list.  Then in reading them you'll need to deserialze and THEN check the criteria.  Talk about painful....

[Jim R]

I don't mean to sound dismissive, but I think I had this discussion the last time.  That's how a very robust payment/User management plugin I'm using inputs the data into the datatable for custom registration fields.  I actually wrote the query, but it's been awhile, and I couldn't get it to output as I wanted it to.  After looking at it for a couple of days, I can't wrap my head around adding the extra trigger/condition/etc. to sort and group Subscribed Users.

[Psycho]

You've had this discussion before because the way the data is being stored is incorrect for how you are trying to use it. As gizmola explained (and I believed I did in a previous thread) you are creating way more work than you have to be (or should be) doing.

 

Even if you have no control over how that application puts data into the database, that doesn't mean you have to work with it that way! A better approach, IMHO, would be to create a process to repopulate that data into normalized tables so you can use the database how it should be used.

[gizmola]

I don't mean to sound dismissive, but I think I had this discussion the last time.  That's how a very robust payment/User management plugin I'm using inputs the data into the datatable for custom registration fields.  I actually wrote the query, but it's been awhile, and I couldn't get it to output as I wanted it to.  After looking at it for a couple of days, I can't wrap my head around adding the extra trigger/condition/etc. to sort and group Subscribed Users.

 

I wasn't part of that discussion, although I do understand that sometimes people are backed into a corner.  Serializing data and storing it in a string, and then trying to query on that data, won't scale at worst, and can become a bottleneck and performance suck at best.  In your query above you do a LIKE '%something%'.  I'm not here to preach, but simply to inform that any LIKE '%something%' query means the entire table must be scanned, every time you query. 

 

With that said, the best answer I can give, is that you need to unserialize the data back into a variable, and then loop through that data, looking for the key in question, and comparing it to the values you want. 

[Jim R]

You've had this discussion before because the way the data is being stored is incorrect for how you are trying to use it. As gizmola explained (and I believed I did in a previous thread) you are creating way more work than you have to be (or should be) doing.

 

Even if you have no control over how that application puts data into the database, that doesn't mean you have to work with it that way! A better approach, IMHO, would be to create a process to repopulate that data into normalized tables so you can use the database how it should be used.

 

And if I knew how to do any of that, I'd do it.  I don't know why choose to write it as they did, but it's an extension beyond the core offering of the plugin.  I also don't know how to rewrite the plugin so upon creating custom registration fields, instead of producing serialized data, it creates new data table columns and populates them accordingly. 

 

It's a WordPress plugin.  If there is a plugin that creates custom registration fields and use their own columns for data, that would be awesome.  However, right now what I'm using is part of a great User Management plugin that also happens to let me create custom registration fields.  I don't think they planned on anyone using it as narrowly as I'm trying to. 

 

That said, I need help with my extra level of sorting.  :-)

[Psycho]

Then, it seems, you're going to need to build a very robust post-processing engine

And if I knew how to do any of that, I'd do it.

Well, you apparently don't know how to do what you are trying to do now either. So, if you need to learn how to do something, why not learn a better way? I'm not an expert in MySQL, but I know that repopulating the data into appropriate tables can be achieved without modifying the code or functionality of the app you are currently using. A couple things you should look into are MySQL triggers and MySQL stored procedures. The first is a process to instruct MySQL to perform some action when a particular event occurs (in this case when a record is inserted or updated in the table). The former is a set of code that MySQL will perform. You may even be able to accomplish this only using a trigger. The only work to do would be to parse the metadata value and then perform operations based upon that. But, MySQL has plenty of functions that should be able to do that.

 

 

http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/

http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

 

[Jim R]

Can't I just get help on the question I asked?  What you linked doesn't apply to my needs, and I'm not the only one asking about serialized data on this forum. 

[gizmola]

Can't I just get help on the question I asked?  What you linked doesn't apply to my needs, and I'm not the only one asking about serialized data on this forum. 

 

What do you have so far?

[Jim R]

Just what I have above.  I just need to add the extra condition which is part of the serialized data.

[PFMaBiSmAd]

There are 12 different "reasons", determined in the first row.

 

If you post examples of what these 12 different values are and what result you want for them (which of your #1,#2,#3,or #4 items they apply to), your problem would be clearer and someone might then be able to show how to write a query that is as efficient as possible at extracting/testing the values. I suspect that SUBSTRING_INDEX might be part of the solution - http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index along with a CASE statement in the query or in a stored procedure. Also, for your list of #1,#2,#3,or #4 items, showing what result you expect with some example data would help too. I.E. 'group them accordingly' doesn't mean anything to us without an example of what you consider to be groups and what about the data values defines which group it belongs in and how this relates to your existing results.

 

Solving programming problems requires knowing what all the expected input values are (so that any patterns/limits can be seen that would lead to a general/optimum solution) and what the expected result is, defined for each possible input value.

[Jim R]

I don't know what else to say than what I started the topic with.  (Other than saying it creates an ordered list--it doesn't--just the groupings above.)

 

I'm going to have 12 "reasons".  For 11 of them, it's going to list the "reason" and break down Yearly, Semi, Monthly.  The 12th one is "hsbball", under that "reason" it will continue to print what's above.  I figured it would be something like:

 

If "hsball" {

  what's printed above

}

 

else {

11 other reason groups

}

[PFMaBiSmAd]

Sample of a mysql term that will extract the reason value string -

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(wp_s2member_custom_fields,LOCATE('s:6:"reason";s:',wp_s2member_custom_fields)),'"',4),'"',-1);

 

edit: simplified it a little

 

[Jim R]

Does it need to be different from what is above?  What I have posted above works perfectly without the extra of trying to figure out why (what they are a fan of) someone is subscribing. 

[gizmola]

Does it need to be different from what is above?  What I have posted above works perfectly without the extra of trying to figure out why (what they are a fan of) someone is subscribing. 

 

Jim,

  Clearly you do not understand that you have not provided enough information for us to help you fully, and you are in denial about that.

 

2)  Determine what they are a fan of (reason) high school basketball or any of 10 colleges in Indiana

3)  If they are a fan of any of the colleges, group them accordingly

4)  If they are a fan of high school basketball "hsbball", group them according which region they live in

 

I'll just start with #2.  To be fair you did provide an example: wp_s2member_custom_fields a:2:{s:6:"reason";s:2:"ue";s:6:"county";s:1:"4";}

 

Now even if it wasn't already obvious that when an array or object gets serialized it's data gets encoded, and that you're interested in the 'reason' column attribute.  Here's a program that illustrates this clearly (and one you should have written yourself if you don't understand these concepts.

 

  $t = array('reason' => 'ue', 'county' => 4);
  var_dump($t);
  $s = serialize($t);
  echo $s;
  $unserialized = unserialize($s);
  var_dump($unserialized);

 

The output of this program:

 

array(2) {
  ["reason"]=>
  string(2) "ue"
  ["county"]=>
  int(4)
}
a:2:{s:6:"reason";s:2:"ue";s:6:"county";i:4;}array(2) {
  ["reason"]=>
  string(2) "ue"
  ["county"]=>
  int(4)
}

 

So we can extrapolate from this that the original data in the "reason" element as "ue" and that county was apparently a string containing "4".  It's a mystery to me how you would think that this is enough information for anyone to figure out in specific terms what your code should be.  We don't even know what "ue" indicates, and we sure don't know what county the number "4" is, or how you would deduce whether that number tells you anything about whether this is "one of the 10 colleges in Indiana".  I'm not going to continue to break this down further, as it should be painfully clear at this point, that you need to put more effort into describing your problem, and clearly describing what you are trying to do.

 

Even without a clearly written question, the initial advice of people was -- you're going about this the wrong way. 

 

To make an analogy, this is a bit like someone going to a car forum, and stating that they want to put 5 monster truck tires on a VW bug so they can drive sideways down the freeway.  After some back and forth, people accepted that the entire idea is ludicrous and wrong headed, and fraught with issues that you will only encounter were you to get even part way down the road, they nevertheless accept the challenge of your premise and endeavor to advise you, starting with the fact that they really need more information about the specific model of Bug, and exactly what roadways you plan to drive down.  In essence your reply is -- but I already have the VW! 

 

Let's just be clear about what phpfreaks does.  We help people who are putting in a good faith effort to learn how to program by advising, clarifying and from time to time giving a push in the right direction.  We're not here to write code to your specifications.  Already three times now in this thread you've seemingly ignored the opportunity for you to investigate a suggestion. 

 

I wrote:

With that said, the best answer I can give, is that you need to unserialize the data back into a variable, and then loop through that data, looking for the key in question, and comparing it to the values you want.

 

You either ignored this or didn't read it.  Since your question involved serialized data, I thought you understood what serialized data is.  Had you followed my advice, you might be well on your way to being done already. 

 

Psycho suggested:

he first is a process to instruct MySQL to perform some action when a particular event occurs (in this case when a record is inserted or updated in the table). The former is a set of code that MySQL will perform. You may even be able to accomplish this only using a trigger. The only work to do would be to parse the metadata value and then perform operations based upon that. But, MySQL has plenty of functions that should be able to do that.

 

Your reply:  "Can't I just get help on the question I asked?  What you linked doesn't apply to my needs, and I'm not the only one asking about serialized data on this forum. "

 

Let's straighten some things out -- First off, nobody here is working for you, or for phpfreaks for that matter.  Everyone involved is volunteering their time.  Secondly, you don't know what the hell you are talking about.  Your question is not about serialized data, it's about having a mysql string that has a pattern of characters in it that you want to SELECT into a computed column.  These strings happen to be the output from the serialize function but they could just as well have been base64 or json, or xml.  All that matters is that you have varchars and you want to find patterns in those varchars. 

 

PFMaBiSmAd provided you this very helpful snippet: 

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(wp_s2member_custom_fields,LOCATE('s:6:"reason";s:',wp_s2member_custom_fields)),'"',4),'"',-1);

 

This shows you a great example of how you can use mysql string handling functions to locate and extract the reason data. 

 

In short -- you have been given a number of different options.  Get busy analyzing them.  Test out PFMaBiSmAd's code.  This is a "teach a guy to fish" forum.  Stop telling us about your query that does 1/2 of the job, and none of the part that you are floundering on. 

 

If this task is beyond your capabilities, then perhaps you should hire someone to do the work for you. 

[Jim R]

Sample of a mysql term that will extract the reason value string -

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(wp_s2member_custom_fields,LOCATE('s:6:"reason";s:',wp_s2member_custom_fields)),'"',4),'"',-1);

 

 

Where would that go in the current code?

[Jim R]

Before revisited the topic, I tried another shot at it:  (no avail)

 

I took what Psycho had helped me with earlier in the year and tried to expand it by adding:

 

// *****   Trying to determine why someone is subscribing	

$reason = unserialize($row['c_reason']);
$user_reason = $reason['reason'];


if ($user_reason = "iu") {
echo $row['user_login'] . ' ';
}	
// ****** end Reason testing area	

 

 

It just prints all the user names.  I figured if it didn't work, it wouldn't print any of them.  The entire code is below:

 

 

 

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

$query = 'SELECT um1.meta_value as custom, um2.meta_value as level, um3.meta_value as c_reason, u.ID, u.user_login, u.user_email
           FROM wp_usermeta um1 
           INNER JOIN wp_usermeta um2
	   INNER JOIN wp_usermeta um3
             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

// *****   Trying to determine why someone is subscribing	

$reason = unserialize($row['c_reason']);
$user_reason = $reason['reason'];


if ($user_reason = "iu") {
echo $row['user_login'] . ' ';
}	
// ****** end Reason testing area	


if ($level_desc != "s2member_level4") {	
    $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)	
    {

//var_dump($subscription_type);
        echo "{$subscription_type}: {$member_count} members<br>\n";
}

}
}

[Psycho]

if ($user_reason = "iu") {
   echo $row['user_login'] . ' ';
   }   

 

That is an ASSIGNMENT operator not a comparison. So, if the PHP parser is able to assign 'iu' to the variable $user_reason then the condition passes. In other words, it would always be true. You should be using $user_reason == "iu" Note the double equal sign.

[Jim R]

Yeah, thanks.  It's little crap like that frustrate me.  It still didn't unserialize the data, as it did for $custom, which you helped me with.  The code PFMaBiSmAd provided, where would I put that? 

 

Just not sure where it fits:

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(wp_s2member_custom_fields,LOCATE('s:6:"reason";s:',wp_s2member_custom_fields)),'"',4),'"',-1);