Profile values inconsistently saved

corrected Project and componet this is listed against.

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

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.

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

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?

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.

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

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;
}

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!

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.

function pu_update($uid = NULL) {

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

function ph_user($op, &$edit, &$account, $category = NULL) {
}

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.

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.

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

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

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

Hopefully a fix will come soon.

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

Rerolled the patch that I submitted there. Please review.

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

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

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.

Tested and committed to 5.

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.

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

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 $

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.

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?

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.

Setting back to how drumm set it.

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

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.

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 :)

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

Great job! :D

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

Great thx heaps

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.

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

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. :-)

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()

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:

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

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:

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;
}

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

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

Having the same problem...

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

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.

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