Copying user profile fields to other fields

I’m a PHP developer, tasked with creating a “members only” section of a website. I’ve found s2Member to be very useful to help eliminate the need to custom code a lot of functionality.

My only real gripe is the way that user profile information is stored in the database. Since it is stored in one field, in a serialized string format, I’m running into problems when trying to update field programmatically.

I have your typical “textfield” user profile address fields for address, city, state, zip. However the client has requested similar fields that are for “internal use only” and need to be pre-filled out with the same data that is already stored in the above mentioned fields. Since each field is stored in one big serialized array, I have no choice but to pull all of the data and re-INSERT it into the database for the additional fields instead of performing a MySQL UPDATE.

I wrote a script to do this, but the users who have special characters that need to be escaped, especially for an apostophre (‘), are coming up blank in the WP user list once imported. This is because the serialized data is considered to be invalid by PHP. For example, if we have a field that contains the value “Harry’s Boats”, the ‘ needs to be escaped, so it becomes Harry’s Boats. I’m properly escaping the ‘ using PHP’s mysql_real_escape_string() or addslashes(), but even though the DB insert was successful, the user information is suddenly blank when viewing in WP Users “/wp-admin/users.php”.

I’m not totally sure what the problem is, other than I think it may have something to do with the database not storing the correct amount of characters in the serialized array, rendering it invalid. If anybody knows what I’m talking about or has experienced this kind of problem before, please let me know. I have 1,000+ users, so I would really prefer not to go through the database manually and check them.

Thank you!

More information on my post.. here are the pertinent sections of my script (bootstraps WP first):

/** Loads the WordPress Environment and Template */
require($_SERVER[‘DOCUMENT_ROOT’].’/wp-load.php’);

/* Grab all of the user ID’s minus Administrator accounts whose ID’s are lower than 5 to query
* the user_meta table
*/
$user_query = “SELECT ID FROM {$wpdb->prefix}users WHERE ID > 5″;
$user_res = mysql_query($user_query) or die(mysql_error());
$userIDs = array();
while ($userIDs = mysql_fetch_object($user_res)) {
$userID[] = $userIDs->ID;
}

/* loop through the userID array and construct a query to pull all s2member profile field
* data.
*/
for ($i=0; $iprefix}usermeta um INNER JOIN {$wpdb->prefix}users u ON u.ID = um.user_id WHERE meta_key = ‘wp_s2member_custom_fields’ AND user_id = ‘$tmp_user_id'”;
$res = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_object($res);
// serialized s2member custom fields row
$serialized = $row->meta_value;
$user_email = $row->user_email;
// array containing the unserialized string data from wp custom fields
$user_array = unserialize($serialized);

// create array placeholder for sanitized user data
$sanitized_user_array = array();

// loop through all user data and escape ‘s
foreach ($user_array as $k => $v) {
$sanitized_user_array[$k] = addslashes($v);
}

// create array for new “internal use only” s2member profile fields
$address = array(
‘rc_mailing_address’ => addslashes($user_array[‘address1’]),
‘rc_mailing_address_2’ => addslashes($user_array[‘address2’]),
‘rc_city’ => addslashes($user_array[‘city’]),
‘rc_state’ => $user_array[‘state’],
‘rc_zip’ => $user_array[‘zip’],
‘rc_email_address’ => $user_email,
‘rc_phone’ => $user_array[‘phone_1’],
‘rc_fax’ => $user_array[‘phone_2’],
);

// merge the sanitized array with escaped internal use only array for insert
$new_array = array_merge($sanitized_user_array, $address);
// re-serialize merged wp custom fields array for insert
$new_serialize = serialize($new_array);

// construct query for updating wp custom fields with new merged array
$insert_query = “UPDATE {$wpdb->prefix}usermeta SET meta_value = ‘”.$new_serialize.”‘ WHERE user_id = ‘$tmp_user_id’ AND meta_key = ‘wp_s2member_custom_fields'”;

$insert_res = mysql_query($insert_query) or die(mysql_error());
}

Thanks for your inquiry. ~ We appreciate your patience :-)

The issue here is that you’re adding slashes to each array value separately. The only reason to add slashes (I recommend using mysql_real_escape_string), is to properly escape the final serialized data string so it can be inserted properly into the database.

Example:

$array = array(
		'key' => $value,
		'another_key' => $another_value
	);

mysql_query("UPDATE ... meta_value='".mysql_real_escape_string(serialize($array))."' ...");

That’s about as far as I can go on custom coding. I’ll be happy to provide you with more leads on where to find certain things, or to share ideas with you, but I can’t debug any custom code that you end up with; due to time constraints :-) Please see: s2Member® » Support Policy » Within Scope for some clarification on this. Thanks!