When a new user registers they have certain fields which are presented to them at the point of registration, others can be filled in post-registration. This is configurable within Administer/Profiles.

Regardless of what profile fields are filled in at the point of registration all of the data is put into the 'dru_users' table into the 'data' field, much like the example below.

a:13:{s:13:"profile_fname";s:6:"Sharon";s:13:"profile_sname";s:7:"Smith";s:16:"profile_hometown";s:9:"New York";s:13:"profile_state";s:17:"New York";s:15:"profile_country";s:14:"United States";s:16:"profile_homepage";s:0:"";s:11:"profile_dob";a:3:{s:5:"month";s:1:"8";s:3:"day";s:2:"13";s:4:"year";s:4:"1970";}s:17:"profile_pportrait";s:19:"I live in New York City";s:21:"profile_education_era";s:5:"1970s";s:20:"profile_first_school";s:8:"New York College";s:21:"profile_second_school";s:9:"New York Univeristy";s:15:"profile_college";s:4:"No College";s:7:"contact";i:1;}

At this point you can click on domain.com/country to see members living in the United States and this member will NOT be listed. The reason is that the query for domain/country pulls data from dru_profile_values.

However, once Sharon goes to 'My Account' AND Edits/Saves it the data is moved from dru_Users to dru_profile_values, like below.

10 13 New York
12 13 New York
8 13 Smith
7 13 Sharon
11 13 United States
4 13 a:3:{s:5:"month";s:1:"8";s:3:"day";s:2:"13";s:4:"year";s:4:"1970";}
9 13 I live in New York City
13 13 1970s
6 13 New York College
20 13 New York University
21 13 No College

Note that the data is ONLY moved per category saved. So, if she goes into Edit and selects the Education category then when she saves it (the Education category) the data will be moved from the dru_Users to dru_profile_values ONLY for the Education fields. She does NOT have to change any values, the act of saving it moved the data between tables.

I will do a little more testing but I am pretty sure this is the probolem. Unfortunately I am not a coder so I cant help with the solution!

FYI I have posted this to the Core Drupal Development forum at http://drupal.org/node/119067.

Thanks,

Comments

VM’s picture

Project:Profile Extended» Drupal core
Version:» 5.1
Component:Code» profile.module

corrected Project and componet this is listed against.

dldege’s picture

Having worked a lot with profiles on my last project I got a lot of exposure to the category system used by the profile module. I had requirements of showing profile fields on the same form as the default account information. Unfortunately, drupal does not allow you to set a profile field to category "account" saying that is reserved by drupal. I then looked at the code for how "Show on registration" was handled and noticed the that the registration fields were all saved under "account" and thus not populated into the profile_values table.

Specifically, when saving profile

<?php
function profile_save_profile(&$edit, &$user, $category) {
 
$result = _profile_get_fields($category);
  while (
$field = db_fetch_object($result)) {
    if (
_profile_field_serialize($field->type)) {
      
$edit[$field->name] = serialize($edit[$field->name]);
    }
   
db_query("DELETE FROM {profile_values} WHERE fid = %d AND uid = %d", $field->fid, $user->uid);
   
db_query("INSERT INTO {profile_values} (fid, uid, value) VALUES (%d, %d, '%s')", $field->fid, $user->uid, $edit[$field->name]);
   
// Mark field as handled (prevents saving to user->data).
   
$edit[$field->name] = NULL;
  }
}
?>

Note at the end that fields handled are NULLed out to prevent them from being serialized into the users table data column. This won't be the case on registration when the category is "account" but it is on subsequent edit/submits of the profile category pages. It all appears to work because the values in users.data are loaded into the $user array before the user account page is viewed and as you noted on edit/submit they are moved into the correct profile_values table. Its hard to tell if this was intentionally how it was coded or just missed because the "register" flag appeared to be doing the right thing visually. If definitely is a problem for profile searching and browsing since these fields are not created for the user who has only registered.

I would also contend (http://drupal.org/handbook/modules/profile#comment-189831) that restricting the use of the "account" category to Drupal core is not necessary and an arbitrary design decision as I have modified the profile module so that I can add fields to the main account page.

esllou’s picture

So did this problem ever get fixed? Considering it's a core module, I thought there would have been a patch by now.

RedDread’s picture

I'm also very keen to find a resolution to this problem, I need to get the data out of the user profiles, but it is now both in the profile_values and user table. I'm not sure how to access the data in the users table, and I need to do so quite urgently.

Are there any work arounds or fixes in the pipeline?

dldege’s picture

StatusFileSize
new1.12 KB

I wrote a simple module that will move the profile fields from the user table to the profile_values table. It worked on my small test site but please back up your database before trying it because it just does the update without any way to cancel or backout.

This probably could just be a script like update.php but I found it faster to do a quick module.

To use
download the attached file and remove the .txt extension and then unzip (you can't attach zip files).
enable pu.module
after backing up your database go to ?q=profile/update which will run the update handler - there is no turning back accept to restore your previously backed up database if it goes bad for any reason.

I hope this helps.

dldege’s picture

The code if you want to make the above into a .php script

<?php
function pu_update() {
 
$categories = profile_categories();

 
$output .= "Starting user profile update";
 
$result = db_query("SELECT uid FROM {users}");
 
$list = array();
  while (
$record = db_fetch_object($result)) {
    if (
$record->uid == 0) continue;
   
$account = user_load(array('uid' => $record->uid));
   
profile_load_profile($account);

   
$userdata = db_query("SELECT data FROM {users} WHERE uid = %d", $record->uid);
   
$userdata = unserialize(db_fetch_object($userdata)->data);
    if (!
$userdata) $userdata = array();
   
    foreach (
$categories as $category) {
     
$data = array();
     
$values = db_query("SELECT * FROM {profile_fields} pf INNER JOIN {profile_values} pv ON (pf.fid = pv.fid) WHERE pf.category = '%s' && pv.uid = %d", $category['name'], $account->uid);
      while (
$value = db_fetch_object($values)) {
       
$data[$value->name] =  $value->value;
      }
     
$data = array_merge($data, (array)$userdata);
     
user_save($account, $data, $category['name']);
    }
   
$list[] = ("Updated " . theme('username', $account));
  }
 
$output .= theme('item_list', $list);
  return
$output;
}
?>
RedDread’s picture

Much appreciated dldege, that worked!

Just a note that when I tried to run it on my live site I got a T_OBJECT_OPERATOR error on line 33. I changed that line to:

$test = db_fetch_object($userdata);
$userdata = unserialize($test->data);

I assume it's a php version difference with objects as it worked fine on my local account.

Again, many thanks!

pcoghlan’s picture

Could I just check on where this code should ideally go to ensure it is run when a user registers?

That way the infomration will immediately become available at registration. Edits post-registration appear to work normally.

Thanks,
Paul

pcoghlan’s picture

Sorry, just realized this is a site-wide update.

If it could update the fields ONLY for the current user at the point of registration it would be a good fix for the general problem. That way information would be constantly up to date and this wouldn't need to be run as a cron job or something.

dldege’s picture

The function pu_update could be modified to take a $uid with the default = NULL meaning site wide update - the current behavior - or if a number is passed it could do that single user. I'm not sure where you'd call it though because hook_user operation 'insert' happens before the information has been saved to the database. 'after_update' might work but its called after all updates, not just after registration like 'insert'.

A cron job, hook_cron, might be a better place to do an update of the whole site. Let me know if you come up with something.

<?php
function pu_update($uid = NULL) {

  if (
$uid = NULL) {
    
//all users
 
}
  else {
    
// single user
 
}
}

function
ph_user($op, &$edit, &$account, $category = NULL) {
}
?>
Pancho’s picture

Title:Profile database error at registration» Profile values inconsistently saved

I can replicate this issue in a clean install: On registration, entered profile data is not saved to table {profile_values} but to the data field of {users} table. When profile data gets edited again, it is moved to the {profile_values} table.

Saving the same values in one of two different fields is bad practice, even if there was a small performance gain at some point. With this argumentation we could always save everything to one table and move the data around for this and for that. In the end we have a great performance loss plus inconsistency plus error-prone code.

To me the only correct way seems to be to always save the profile data to the {profile_values} table and retrieve it from there.

I marked http://drupal.org/node/128574 and http://drupal.org/node/116294 as duplicates and renamed this issue, as "database error" is misleading.

Pancho’s picture

Priority:Normal» Critical

One more issue filed for this bug: http://drupal.org/node/109972.

As this bug seems to bother lots of people, I mark it as critical.

Pancho’s picture

And one more "duplicate": http://drupal.org/node/118822.

http://drupal.org/node/60761 and http://drupal.org/node/78922 might be related, too.

(Sorry for not having it condensed into one single comment.)

Crell’s picture

I can confirm this as well. In my case I was saving from the admin create-user screen, specifically using macro.module. Same symptom, though. Data saved to user.data, then on edit gets saved to the profile tables As God Intended(tm).

denney’s picture

Subscribing to this as this seems to be the most active report of this issue.

Hopefully a fix will come soon.

edmund.kwok’s picture

Status:Active» Needs review
StatusFileSize
new1.62 KB

This might be related to this http://drupal.org/node/105425.

Rerolled the patch that I submitted there. Please review.

denney’s picture

+1 for the patch.

I've tested the patch on all 4 of my websites and it appears to work correctly.

When registering, profile values are now saved to the {profile_values} table.

scor’s picture

This works great for me as well. Can this patch be reviewed and committed ?

Walterh’s picture

the propsed patch first seems to work, but when a new user confirms his account by email all his profile settings are removed. So the patch is nog working for me.

drumm’s picture

Version:5.1» 6.x-dev
Status:Needs review» Reviewed & tested by the community

Tested and committed to 5.

denney’s picture

Version:6.x-dev» 5.1
Status:Reviewed & tested by the community» Needs work

I can confirm WalterH's issue.

The patch appears to work at first but if the user uses the activation link in an email, the profile data is removed.

If the user edits his profile, the data is also removed.... so this patch ONLY works during registration... useless thereafter.

denney’s picture

Status:Needs work» Reviewed & tested by the community
StatusFileSize
new978 bytes

The following patch fixes the error posted by WalterH. All cases seem to work now.

scor’s picture

I can't reproduce the problem mentionned by WalterH. Can you give more details on the exact procedure and links you click on?
The first patch of edkwh works fine for me.

denney, what is the difference between your patch and the first patch (edkwh)? They both produce the same file after being patched against
// $Id: profile.module,v 1.195 2007/03/27 05:13:54 unconed Exp $

denney’s picture

The difference between the 2 patches is mine adds the following line:

     case 'update':
+   return profile_save_profile($edit, $user, $category);
     case 'insert':
-      return profile_save_profile($edit, $user, $category);
+      return profile_save_profile($edit, $user, $category, TRUE);

whereas his patch is:

     case 'update':
-    case 'insert':
       return profile_save_profile($edit, $user, $category);
+    case 'insert':
+      return profile_save_profile($edit, $user, $category, TRUE);

My patch stops the "register" variable being used when a user account is "updated".

The steps I use to replicate WalterH's issue:

1. Create a custom profile field to show on the registration page (put it into a "Personal Information" category).
2. Register a new user, including the custom profile field.
3. Click the activation link (or login manually) using the information in the email.
4. Goto the "My Account" page and click edit.
5. Save the form without editing anything.
6. The custom profile field data from the "Personal Information" category should be gone.

scor’s picture

I've followed the steps from a fresh install of drupal-5.x-dev.tar.gz and I still can't reproduce the bug. I see no problem with current profile.module (v 1.189.2.3 2007/03/29) which includes the first patch.

The patch you suggest is written in a different way, but at the end of the day, it makes the same changes, and you end up with the following lines (l 174):

    case 'update':
      return profile_save_profile($edit, $user, $category);
    case 'insert':
      return profile_save_profile($edit, $user, $category, TRUE);

Am I missing something?
denney, WalterH can you try with current drupal-5.x-dev.tar.gz?

denney’s picture

Sorry about the mix-up. I re-applied edkwh's patch after looking at the latest module code and you're right, the code is the same.

Something must of gone wrong when I applied the patch. I believe the same might of happened to WalterH.

WalterH, take a look at YOUR profile.module file and see if the lines are the same as what scor posted. Mine weren't after applying the patch.

If WalterH's file isn't the same as what you have posted scor, I can safely say this bug is fixed.

denney’s picture

Version:5.1» 6.x-dev

Setting back to how drumm set it.

Dries’s picture

Version:6.x-dev» 5.x-dev

Committed this to CVS HEAD. Do we need to fix Drupal 5? Looks like Neil might have committed the wrong patch?

scor’s picture

no Dries, it's ok. The 2 patches were actually the same. denney and I tested the drupal-5.x-dev and it works fine.

Pancho’s picture

Status:Reviewed & tested by the community» Fixed

Tested it posititive, too.
WOW... we (or actually Ed) finally got a bug fixed, that has been bothering people for a long time! And it didn't even take a week...
I set this one to fixed, guess we're not getting any problems with this anymore :)

Walterh’s picture

I have tested the profile.module_28.patch and it works fine. There is no data deleted anymore. Thanks a lot!

webchick’s picture

Great job! :D

skizzo’s picture

will profile.module_28.patch fix the
problem for new users only? Or will
it fix existing users as well? Thanks.

KelvinWong’s picture

Great thx heaps

scor’s picture

I'm afraid this patch will only fix the problem for the new users only. All the existing users should be edited and saved to update the profile_values table to make the profile values searchable.

Anonymous’s picture

Status:Fixed» Closed (fixed)
solipsist’s picture

Status:Closed (fixed)» Patch (to be ported)

Has anyone ported this patch to Drupal 5.1? It appears inconsistent with our 5.1 version of profile.module.

Crell’s picture

Status:Patch (to be ported)» Closed (fixed)

It's been applied to 5.x-dev, which means that it will be in 5.2 whenever that's released. 5.2 will be a bug-fix release for the 5.x series, replacing 5.1. drumm hasn't said when that will be, though. Personally I hope it's soon. :-)

EliaM’s picture

Version:5.x-dev» 5.3

This problem may be fixed for new user registrion, but not if you're adding a new user through your own code with user_save()

EnekoAlonso’s picture

Well, it looks like the problem of profile values getting updated when a user is registered is solved on Drupal 5.5. But this issue of saving profile values on user->data field is still causing a lot of trouble.

Here is my problem:
I update user profile values manually, with some SQL code. It's very simple because I just need the field id and the user id to do it. So the saving process is not a problem. The problem is when I try to read user profile values: $user->profile_XXXXX is returning the data stored on user->data field, and not the value I entered on profile tables.

I have seen some code to synchronize the data field with the updated profile values, but it is very complicated and annoying.

The worst is than calling to profile_load_profile($user) does not fix the problem, since it checks if the variables have already some data. The only trick I have came up with is to unset the variables, so this method will load the right ones:

<?php
 
// Trick to reload user coordinates from profile table instead of user->data
 
unset($user->profile_anyprofilefield);
  unset(
$user->profile_anyprofilefield);
 
profile_load_profile($user);
 
// Now $user->profile_anyprofilefield contains the value from profile table instead of the value stored on user's data field
?>

Obviously, this is a little bit of overload, since the user profile has been loaded twice at this point.

So I have some questions:
Is there a simple way to avoid saving user profile data on users data field?
And how can I clear the user data field?

This won't be a big issue if there was a php function I could call to update user's data field variables. Something like variable_set function, but for user's data fields. Also I have no idea of any php function to update user's profile fileds, so I do it manually usign a sql query. Does any of these functions exists?

Thanks a lot.

mtg82814’s picture

Version:5.3» 5.5
StatusFileSize
new1.74 KB

I made a module based off of "dldege" module in post #5.

My module displays all the profile fields for each user in a table. it does not attempt to move any data, only performs reads.

here is the core code:

<?php
function ProfileDisplay_menu($may_cache) {
   
   
//init return array
   
$items = array();

   
//build module attributes
   
if ($may_cache) {
       
$items[] = array(
           
'path' => 'profile/display',
           
'title' => t('Display registration user profiles'),
           
'description' => t('Display profile data'),
           
'access' => user_access('administer users'),
           
'callback' => 'pd_update'
       
);
    }
    else {
       
//intentionally left blank
   
}
   
   
//return module attributes
   
return $items;
}

function
pd_update() {

   
//start output variable
   
$output .= "<br>Here is the Profile Data:<br>\n";
   
   
//query for all users
   
$usersResultSet = db_query("SELECT * FROM {users} where uid != 0");
   
   
//get all profile fields
   
$profileFields = db_query("SELECT * FROM {profile_fields}");

   
//print out table header row
   
$output .= "<table border=1 cellspacing=0 cellpadding=0>\n <tr>\n";
   
   
//print user column header
   
$output .= "  <td><strong>User</strong></td>\n";

   
//print email column header
   
$output .= "  <td><strong>Email</strong></td>\n";
   
   
//array to hold profile fields
   
$pFieldsArr = array();
   
   
//init hash to store pFields data
   
$hash = null;

   
//cycle through and print profile fields as table headers
   
while ($pFields = db_fetch_object($profileFields)) {
       
$output .= "  <td><strong>" . $pFields->title . "</strong></td>\n";
       
       
//store pfield info
       
$hash["fid"] = $pFields->fid;
       
$hash["name"] = $pFields->name;
       
$hash["type"] = $pFields->type;
       
//$hash["title"] = $pFields->title;
       
array_push($pFieldsArr, $hash);
       
       
//clear hash
       
$hash = null;
       
    }

   
//end row of table header
   
$output .= " </tr>\n";

   
//cycle through users
   
while ($usersRow = db_fetch_object($usersResultSet))
    {
       
//print user name
       
$output .= "  <td><strong>" . $usersRow->name . "</strong></td>\n";

       
//print email
       
$output .= "  <td>" . $usersRow->mail . "</td>\n";

       
//get any profile data that may be in the "data" column of the users table
       
$sql = "SELECT data FROM users WHERE uid = " . $usersRow->uid;
       
$userDataResultSet = db_query($sql);

       
//unserialize it
       
$userData = unserialize(db_fetch_object($userDataResultSet)->data);

       
//no values, init to blank array
       
if (!$userData) $userData = array();
       
       
// 4. cycle thru profile fields
       
foreach ($pFieldsArr as $hash)
        {
           
$fid = $hash["fid"];
           
$name = $hash["name"];
           
$type = $hash["type"];
           
           
$pValuesResultSet = db_query("SELECT pv.value FROM profile_values pv where pv.fid = %d and uid = %d", $fid, $usersRow->uid);
           
           
//get value for current fid
           
$value = db_fetch_object($pValuesResultSet)->value;

           
//if no value, check user data column
           
if (($value == null)  || ($value == ""))
            {
               
$value = $userData[$name];
            }
           
           
//if profile type is "date", then deserialize into a writable date format
           
if ($type == "date")
            {
                if (
is_string($value))
                {
                   
//unserialize it
                   
$dateHash = unserialize($value);
                   
                   
//get values from unserialized hash
                   
$value = $dateHash["month"] . "/" . $dateHash["day"] . "/" . $dateHash["year"];
                   
                   
//blank it out, if no values exist
                   
if ($value == "//")
                    {
                       
$value = "";
                    }
                }
                else
                {
                   
//$value is already deserialized, just gets its values from its hash
                   
$dateHash = $value;
                   
$value = $dateHash["month"] . "/" . $dateHash["day"] . "/" . $dateHash["year"];
                   
                   
//blank it out, if no values exist
                   
if ($value == "//")
                    {
                       
$value = "";
                    }
                }
            }
           
           
//print out value
           
$output .= "  <td>" . $value . "</td>\n";
        }
       
//end row
       
$output .= " </tr>\n";
    }

   
//end table
   
$output .= "</table>";   
   
   
//return output
   
return $output;
}
?>
zmove’s picture

Version:5.5» 6.2

I have the same problem with D6, is there any feedback about it ?

KatyB’s picture

Status:Closed (fixed)» Active

I have the same problem with multiple entries in 6.2 also. Is there a fix for v6?

ekrispin’s picture

Having the same problem...

sun’s picture

Status:Active» Closed (fixed)

Please create a new issue and add a reference to this one.

sun’s picture

Version:6.2» 5.5
jvieille’s picture

This is still a mess.
The profile handling of visibility can only work in the only situation fields are set manually through the UI.
There is no way (I tried everything, I guess) to deterministically set profile values programmatically.

Saving the same values both in user->data and in profile_values is wrong to begin with, and the provided functions user_savet() and profile_save_profile() are unable to cope with this.

Solution: use content profile and play with cck fields at will.

jvieille’s picture

Version:5.5» 6.29
Issue summary:View changes
Status:Closed (fixed)» Closed (won't fix)

Change to D6, that still doen't work - and will never be fixed.