Use a serialized array

Thanks for your post.

I actually originally toyed with making the data serialized and completely agree with you that it makes the most sense from a technical perspective. I've never been happy with storing the values as comma-delimited. I also like the idea of using commas in the option values if so needed. My thinking at the time was to make the single value presentable and accessible by other modules expecting a string value. I also didn't want to create (yet) another table to store the multiple values, which is why the existing "value" column in table profile_values was used.

That said, I'm open to exploring this. However, before proceeding to make this change, we need to consider how it will affect core and third-party modules expecting a plain string. It seems like it would need to be the responsibility of Profile Checkboxes to unserialize the data. This could be achieved with the load operation in hook_user. Also, the module update would have to serialize any existing values.

Does this make sense? Maybe there's no downside. Let me know what you think.

Thanks,
John

OK, I will proceed with serializing the data. And yes, it will make the module more polished and elegant.

I should have some downtime next week to work on this. Yeah, I will produce a dev-branch and the eventual release will be a new branch as well.

Thanks again for suggesting this. It's time for Profile Checkboxes to grow-up. ;-)

Merry Christmas to you too.

@chrism2671,

I have just created a dev release of Profile Checkboxes, which contains the most significant changes since the module's original release. These include:

1) me alias module support
2) values stored as serialize array
3) individual user list links
4) definable delimiter separator string per field
5) updated schema structure

One important thing worth noting about #3. In order for the user list links to work properly, a small (cringe) hack must be made to profile-pages.inc file of the profile module. The attribute array definition of the 'selection' case in function profile_browse() must be changed from:

$query = "v.value = '%s'";

to

$query = "v.value LIKE '%%%s%%'";

The reason is because the individual value must be searched for within the serialized string instead of an absolute match. I can't think of another way to accomplish this without hacking this one SINGLE LINE in profile-pages.inc. Unfortunately hook_db_rewrite_sql only allows you to append WHERE clause statements, not change the existing ones. If you can think of another way, please let me know.

Anyway, please give the development snapshot a whirl. Run the update and make sure it preserves your data.

Your feedback/comments is eagerly anticipated.

Thanks,
John

Hi Chris,

Sorry about that. The dev branch is correct now--promise! Please try again.

Thanks in advance for taking the time to test.

Regards,
John

Hi Chris,

Thanks for your feedback.

As to your points:

1) The data conversion (CSV -> serialized) worked for me during my tests, but I will certainly test again.
2) Yeah, I can write a Views handler. I was putting that off until last. I was also thinking of including a helper function to unserialize and output the values using the predefined delimiter for a given field.
3) The default Drupal behavior of individual profile links is important to some people. See issue #939756. Without the hack, the user list links will lead to empty pages. I can provide a patch for people interested in user list support.

Anyway, let me do some more testing and put together the Views handler.

Thanks,
John

Hi Chris,

I just checked in a new version of profile_checkboxes.install, which correctly serializes the value data in table profile_values. As your time permits, please test the updating.

I will work on the views handler next.

Thanks,
John

Hi!
dev version works fine for me just now. :-)
Here is a small patch (quick and dirty) to handle correctly numeric keys/values in 'options' array - we can't use array_shift for them:

--- profile_checkboxes.module.orig      2011-01-08 19:54:54.000000000 +0300
+++ profile_checkboxes.module   2011-01-08 20:10:15.000000000 +0300
@@ -65,7 +65,10 @@
         if (isset($form[$field->category][$field->name])) {
           if (!$form[$field->category][$field->name]['#required']) {
             // remove default selection element from options array
-            array_shift($form[$field->category][$field->name]['#options']);
+           if (!empty($form[$field->category][$field->name]['#options'])) {
+             reset($form[$field->category][$field->name]['#options']);
+             unset($form[$field->category][$field->name]['#options'][key($form[$field->category][$field->name]['#options'])]);
+           }
           }
           $value = unserialize($field->value) ? unserialize($field->value) : array();
           switch ($field->type) {

@skylord,

Thanks for the feedback.

Also, good catch re: numeric keys/values and thanks for the patch. Funny, I was aware that array_shift modified numeric array keys, but for some reason it's never come up. I guess everyone must be using strings. :-)

John

@skylord,

Patch applied successfully--thanks!

I actually reconsidered whether or not this this patch was really necessary. I assumed the array keys are ALWAYS numeric, but after examining function profile_form_profile() in profile.module I confirmed the option array keys are defined using the array values.

John

Hi all,

I just checked in another update, which includes:

1) fixed comma-delimited -> serialize data conversion
2) Views support, unserialize multiple choice values

Kindly (re)test both of these updates. Feedback and code review comments gladly accepted.

Thanks,
John

Hi all (again),

I just checked in yet another (albeit small) update, which fixes a validation bug when saving data for a particular profile category.

I believe this version is solid and ready for release. Please review and confirm.

Thanks,
John

Hi,
just tried to install the devel version but when I try to update the checkbox list there is Selection options missing. Also I can't see chekboxes - only name of the checkbox is visible.

@brnco,

Thanks for testing and reporting your findings.

I'm not completely sure what you're referring to. Could you possibly provide a screen shot and/or provide more details.

Thanks,
John

Ok, here are two screens:

Thanks a lot.

@brnco,

Thanks for posting the screen shots.

I have identified the source of the problem and will have a new development snapshot available soon.

John

@brnco,

I believe this issue has been fixed. Kindly test the newest dev snapshot, which I believe is ready for release (sans any other reported problems!)

Thanks,
John

@chrism2671,

Since you were the one who originated this design change request could you please provide some feedback on the latest dev version? I believe it's ready for release, but would like independent confirmation before officially releasing it into the wild.

Regards,
John

@chrism2671,

I totally understand about professional obligations. Let me know when you know. :-)

Also, thanks for the heads-up on stackflow.com. I just added a note to the OP.

Regards,
John

Thanks for posting on the issue I had John, I updated to the dev version but have a few q's

When I updated, it did serialize one of my text fields by mistake somehow (wasn't a problem and easy to change back, just a heads up)

I am still having trouble with views though. By default, it's still just giving a query as if it's looking for a field with one value:

WHERE... (profile_values_profile_lang.value in ('Portuguese'))

Is there a different way I should be filtering these? (I am using a "User" view instead of a node FYI)

Worst case I have the custom module fix I made before I saw your post

Hey kilrizzy,

Thanks for posting your feedback.

Regarding the first issue, the update should only convert Profile Checkboxes field comma-delimited values. I will certainly double-check to insure that's the case.

I have some professional obligations that require my immediate attention, but I will get back to you regarding the second issue.

Thanks,
John

Thanks! Take your time, I fixed it with a custom module to modify the views query.

So regarding a Views filter for serialized data... what's the best approach? Will creating a LIKE filter handler suffice? Besides being slower, are there any pitfalls or gotchas? Anyone, anyone?

Wow, great developments here for this module.. Keep up the good work.

Will definitely test this in a project I'm working on.

A quick question though, in CCK multi value checkboxes, you can work with key/value pairs..
It would be awesome if this module did the same.
Example: a user "spoken language" select field.

Currently I have selection between:
English
German
French
Dutch
...

Ideally, the list would be configured like this:
en|English
de|German
fr|French
nl|Dutch

The 2nd element in the pair would be translated throughout the site, while the first part should never change (so it can be used for e.g. showing a country flag programmatically).
In processing it makes a lot more sense to write a switch-case with the short keys than using the full string values.

What do you think? I am willing to help code this or provide a patch for it if you think this is a nice idea.

@rv0,

Adding key/value support is a good idea.

As kilrizzy points out in comment #26, Views filter support using the serialized values is needed. I actually started working on this last week, but got distracted with my professional and personal obligations. I'll be out of town this weekend and won't be able to look at it again until sometime next week at the earliest.

In summary, I'd like to get Views fully implemented before adding any other features. If you or someone is inclined, I would greatly appreciate help in this area.

Thanks,
John

Looking into how the module stores things in the database, I notice

profile_values table: I see some duplication there that I cannot explain.
I added 4 options (value1...value4).
and selected 2 options on a profile page.

the stored serialized array looks like this:

a:2:{s:6:"value2";s:6:"value2";s:6:"value3";s:6:"value3";}

so i see values are stored twice, this must be a bug?

profile_fields table
this stores the configuration, it stores it like a plain text also.
Looking at how this is solved in CCK module, they do the same, plain text inside a serialized array with other data, so thats ok i guess (see allowed values string in it the following example:

a:4:{s:15:"text_processing";s:1:"0";s:10:"max_length";s:0:"";s:14:"allowed_values";s:42:"nl|Dutch
de|German
en|English
fr|French";s:18:"allowed_values_php";s:0:"";}

)

I'll see what I can do, I'm working on a project that uses this module in a lot of ways and I could really use the key=>value association, so I may be able to invest some time in this in the next couple of days.

Profile Checkboxes doesn't actually write anything to the profile_values table directly. It simply manipulates the value via a custom validation handler before saving.

Yes, the duplicate serialized values should be considered a bug. Good catch. I can provide an easy fix for this, but not until next week (as previously stated.)

The key/value options pairs should not pose a problem as long as the output displays the value and not the key.

Again, I would prefer to get Views support fully implemented before adding any other new features.

I've never written views integration before, but I'll definitely take a further look into it.

Is it specifically the filters part or is there anything else that has to be done?

edit: looking further into how cck handles this for multi value checkboxes, for the field there is a "group multiple values" setting.
Imagine your checkbox consists of 4 values and it has 2 values checked for a particular profile, this will result in 2 rows.
This might be something to think about.
However.. CCK stores every checkbox value in a separate row, which makes this a lot easier (and also makes the views filter easier).
I'm not sure if this is possible here.
A screenshot of the setting: http://i.imgur.com/WLBSY.png

Writing views handlers can be a slight pain in the ass sometimes. It's not always completely obvious or intuitive (unless you've created a bunch of them, which I haven't). I've got a pretty good handle on what's required though--I just need to carve out the time to do it.

Hey, first of all compliments for this tiny and not overbloated (like, IMO, e.g. content_profile) addon and thanks for your work!

I have tested the current dev-release, that is the one from Feb 25th. I came across the following behavior (steps to reproduce).

1) create a generic field like e.g. "YOB" (single line text) and enable the list function ("all users born in %value").
2) create a multpiple choice field (checkboxes) named "profile_test", title: "Test", hint: "Test", public field (both profile and lists), enable list function ("All %value"), add some option lines (e.g. "Test1","Test2","Test3")
3) log in as a user (eg "JonDoe"). Fill in both profile fields, e.g. "YOB" with "1970" and "Test" with both "Test1" and "Test 3" checked.
4) Now view your users page.
4a) click on the "Test1" or "Test3" link => list page is empty.
4b) click on the "1970" link, you will see as "all users born in 1970":
4aa) *JonDoe

Edit: Already found the reason meanwhile. Each single item links to /profile/[itemname]/[itemvalue] which is actually attached to core profile module's profile_browse() callback which, AFAIK, is not overrideable. There, the db query is build to find "where value='%s'". This is exact matching.

Thus, Trying to find all users applying to [itemvalue] will never work unless they have checked only one of the multiple options. I am afraid your approach of linking the single portions towards the generic profile_browse() function will fail here and needs re-thinking. :-(

Edit2: The profile_browse() callback is a really lousy implementation so far since it can either match exactly or "%%%s%%". Hence I would also not do what I thought at first, that is no longer alter the "selection" but the "list" type. IMO, what we need is a query that searches for

"{profile_values} v where v.value like '%s'"

having %s = '"$value"'.

In other words: The default profile_browse() cannot deal with your serialized array. Or am I getting that completely wrong at some place?

If not, how about linking to a custom profile_checkboxes_browse() callback instead, hat uses the generic theming functions so that there would be at least no visible difference? I will not just check for updates here regularly but also have a closer look at these things myself.

Since that thing did not let me sleep properly (in a literal sense!), I just implemented what I thought was right. Along with this I fixed another issue regarding radio boxes, whose values got lost on saving since they were (and, while this is correct, still are) NOT being stored as a serialized array.

What did I do?

1) I added a custom menu callback (as planned) which handles the multi value fields' (multi, checkbox) list page links (multiprofile/%/%) and passes it to a custom callback handler. I couldn't figure out why (and, to be honest, how) the core module handles the request without using wildcards, but I don't see a reason not to do so here (we will always DO HAVE name and value to pass, so what?!).

2) I just cloned the profile_browse() callback handler from profile.module. I know, this is close to, but actually is not core-hacking ;) but after all still, well: kind of, overriding. Now, we will find all users having checked the option the link is related to (and, besides, do this more precisely than the core module does when browsing its "list" type).

Here's my diff:

--- profile_checkboxes.module.default	Fr 25. Feb 02:11:52 2011
+++ profile_checkboxes.module	Di 15. Mrz 08:41:05 2011
@@ -70,7 +70,7 @@
               unset($form[$field->category][$field->name]['#options'][key($form[$field->category][$field->name]['#options'])]);
             }
           }
-          $value = unserialize($field->value) ? unserialize($field->value) : array();
+          $value = unserialize($field->value) ? unserialize($field->value) : (array)$field->value;
           switch ($field->type) {
             case 'checkboxes':
               // change list selection field type from 'select' to 'checkboxes'
@@ -137,7 +137,7 @@
     case 'validate':
       $fields = profile_checkboxes_selection_fields($account->uid);
       while ($field = db_fetch_object($fields)) {
-        if (isset($edit[$field->name])) {
+        if (is_array($edit[$field->name])) {
           $value = array_filter($edit[$field->name]);
           // convert non-empty field array to serialize array
           $edit[$field->name] = count($value) ? serialize($value) : NULL;
@@ -159,7 +159,7 @@
         $links = array();
         foreach ($items as $item) {
           // create individual links to mimic default behavior
-          $links[] = l($item, 'profile/'. $field->name .'/'. $item);
+          $links[] = l($item, 'multiprofile/'. $field->name .'/'. $item);
         }
         // replace single value link with multiple value links
         $account->content[$field->category][$field->name]['#value'] = implode($field->delimiter, $links);
@@ -212,3 +212,71 @@
     'path' => drupal_get_path('module', 'profile_checkboxes') .'/views',
   );
 }
+
+/**
+ * Implementation of hook_menu().
+ */
+function profile_checkboxes_menu() {
+  $items['multiprofile/%/%'] = array(
+    'title' => 'User list',
+    'page callback' => 'profile_checkboxes_browse',
+    'page arguments' => array(1,2),
+    'access arguments' => array('access user profiles'),
+    'type' => MENU_SUGGESTED_ITEM,
+  );
+  return $items;
+}
+
+/**
+ * Menu callback; display a list of user information.
+ */
+function profile_checkboxes_browse($name,$value) {
+
+  $field = db_fetch_object(db_query("SELECT DISTINCT(fid), type, title, page, visibility FROM {profile_fields} WHERE name = '%s'", $name));
+
+  if ($name && $field->fid) {
+    // Only allow browsing of fields that have a page title set.
+    if (empty($field->page)) {
+      drupal_not_found();
+      return;
+    }
+    // Do not allow browsing of private and hidden fields by non-admins.
+    if (!user_access('administer users') && ($field->visibility == PROFILE_PRIVATE || $field->visibility == PROFILE_HIDDEN)) {
+      drupal_access_denied();
+      return;
+    }
+
+    // Compile a list of fields to show.
+    $fields = array();
+    $result = db_query('SELECT name, title, type, weight, page FROM {profile_fields} WHERE fid != %d AND visibility = %d ORDER BY weight', $field->fid, PROFILE_PUBLIC_LISTINGS);
+    while ($record = db_fetch_object($result)) {
+      $fields[] = $record;
+    }
+
+    // Determine what query to use:
+    $arguments = array($field->fid);
+    $query = "v.value like '%%:\"%s\";%%'";
+    $arguments[] = $value;
+
+    // Extract the affected users:
+    $result = pager_query("SELECT u.uid, u.access FROM {users} u INNER JOIN {profile_values} v ON u.uid = v.uid WHERE v.fid = %d AND $query AND u.access != 0 AND u.status != 0 ORDER BY u.access DESC", 20, 0, NULL, $arguments);
+
+    $content = '';
+    while ($account = db_fetch_object($result)) {
+      $account = user_load(array('uid' => $account->uid));
+      $profile = _profile_update_user_fields($fields, $account);
+      $content .= theme('profile_listing', $account, $profile);
+    }
+    $output = theme('profile_wrapper', $content);
+    $output .= theme('pager', NULL, 20);
+
+    $title = strtr(check_plain($field->page), array('%value' => theme('placeholder', $value)));
+
+    drupal_set_title($title);
+    return $output;
+  }
+  else {
+    drupal_not_found();
+  }
+}
+

HTH & cheers

Once one has started working on things. I came across something else worth "polishing" - what happens with profile fields having been converted (and thus serialized) when this module is uninstalled but the profile field is not deleted?

In the current dev state, the profiles show up the serialized string like a:2:{s:4:"test";s:5:"test2";}. In a huge site, however, one could delete the profile field. Or delete its values by DB hacking. here's what I like much more:

--- profile_checkboxes.install.default	Fr 25. Feb 02:11:52 2011
+++ profile_checkboxes.install	Di 15. Mrz 17:40:33 2011
@@ -18,6 +18,21 @@
  * Implementation of hook_uninstall().
  */
 function profile_checkboxes_uninstall() {
+  // Transform serialized data in the profile_values table
+  // to avoid confusing values in forgotten fields
+  $result = db_query("SELECT pc.type, pc.delimiter, v.* FROM {profile_checkboxes} pc JOIN {profile_values} v ON pc.fid=v.fid");
+  while ($record = db_fetch_object($result)) {
+    // check each field whether it is a "multiple" type
+    if ($record->type == "multiple" || $record->type == "checkboxes") {
+      if(isset($record->value) && $record->uid && $record->fid) {
+        // An imploded array is probably the best way to hold the user data for display
+        $transformed = implode($record->delimiter,(array)unserialize($record->value));
+        // update the related field in the database
+        db_query("UPDATE {profile_values} SET value='%s' WHERE fid=%d AND uid=%d",$transformed,$record->fid,$record->uid);
+      }
+    }
+  }
+  
   // Remove tables
   drupal_uninstall_schema('profile_checkboxes');
 }

Hope you like this too.

@doitDave,

I'm sorry for my delayed response. Thanks so much for your work on this!

As I pointed out in comment #4, the use of serialized arrays with the profile user list functionality is incompatible for the reason you described. However I hadn't identified a viable workaround without hacking the core (ack!)

I like the sound of your override. I will apply the patch to dev and give it a whirl.

Thanks also for the uninstall data clean-up patch. I will apply that as well.

Regards,
John

Nice work
I'm running the latest dev with the patches provided in this topic and everything is working great so far.
Applied/Updated seamlessly.

Would be nice to get Views Filter support working next :)

@rv0,

Thanks for the feedback.

Views Filter support is definitely my next priority with this module. However my time is somewhat limited right now due to professional and personal obligations. I would be grateful to you (or someone) who was willing to contribute the Views Filter handler code. I will try to carve out time to do it myself, but I'm not sure how soon that will be.

John

@doitDave,

I just tried applying your code to the latest dev version, but was unable to do so due to malformation errors. Would it be possible for you to produce the patches again?

Thanks,
John

First off, great module! Finally incorporates a much-needed function without rewriting profile.module. doitDave's patch from #37 makes it even better. I have attached .patch based on his code with two slight differences: 1) his (array)$field->value doesn't really make sense, replaced with array($field->value) . 2) The list-building with %%:\"%s\";%% works great for string values, but not for numbers (e.g. years on my page), thus replaced with %%%s%% . This of course has limitations as well, but not as many in my opinion.

Hey and Hugh!, to be honest, I am so fcukin new to all this patch-diff-etc stuff I was rly proud having generated one (confessing: not using the drupal tools but "my" Version system which is actually SVN...).

Hm. What now? Would you like to guide me in providing the patch properly? Would be a benefit for all sides probably? :-)

@oneoftwo,

I applied your revised patch containing your tweaks--thanks!. I also manually applied doitDave's patch in comment #38 and have checked in the changes.

All,

The latest dev snapshot should be available soon. Please kindly test and report back.

Thanks,
John

Hi,

glad to see my idea spreads! :)

Good point that I didn't think of, the serialization issue of integers. But %%%s%% is, IMO, too lazy here, we should then use something like

$query = "(v.value like '%%:\"%s\";%%' OR v.value like '%%:%s;%%')";
$arguments[] = $value; // first placeholder
$arguments[] = $value; // second placeholder

That will make sure you have only exact matching but will also work with numbers :-)

Hi,

I don't know exactly where you've come by now but this project sounds very promising and I was going to use it for sure until I ran into a bunch of conflicts with other modules. I have made an interesting observation in the meantime, though: Views can filter fields containing several items for one of them: filtering by roles does exactly what you've been looking for probably allowing you to use a fair bit of that filter for your purpose.

Hope this helps. Good luck!

@sOs,

Thanks for the feedback and information regarding Views filtering.

I ran into a bunch of conflicts with other modules.

Please create a new issue if you discover a problem with the module.

I *believe* the dev version is stable. I have been meaning to create a new release, but my professional and personal obligations have prevented me from doing so.

I hope to find time within the next couple of weeks.

Thanks,
John

Serialized values included in the 6.x-2.0 release.

has anything changed regarding views support?

Views support is there, but it's pretty limited--hence the "partial" in the project description. Filtering still needs to be implemented.