The usermeta table data

This seems like it would be a great question for the https://lists.automattic.com/mailman/listinfo/wp-hackers email list.

However, step back a minute and look at this from a different perspective: How many columns SHOULD the table have, considering all the varied and different uses it has? There are plugins that store data there too, not just core WP. From a design perspective, if you don’t know exactly how many columns you need or all of the intended usage, then going to the “name/value” pairs approach makes perfect sense.

Way more sense than 40 columns named, “Col1, col2, col3… col39, col40” right? Or trying to anticipate every possible use and guessing at columns.

Well, I see that the code for get_userdata() in pluggable-functions.php makes use of the separate records for each meta field to easily construct an array of an indefinite number of fields. Yet, HandySolo, I know the extra fields I need (address, city, phone, fax…) but think that I might just want to convert the below code to read out a table into a set number of fields from one record. Although it’s not as elegantly extensible, I don’t think having 100,000 records to search through will be efficient for my database.
Snippet from get_userdata() in pluggable-functions.php —-

$metavalues = $wpdb->get_results("SELECT meta_key, meta_value FROM $wpdb->usermeta WHERE user_id = '$user_id'");
$wpdb->show_errors();
'
if ($metavalues) {
foreach ( $metavalues as $meta ) {
@ $value = unserialize($meta->meta_value);
if ($value === FALSE)
$value = $meta->meta_value;
$user->{$meta->meta_key} = $value;
// We need to set user_level from meta, not row
if ( $wpdb->prefix . 'user_level' == $meta->meta_key )
$user->user_level = $meta->meta_value;
} // end foreach
} //end if


Oh, I wasn’t saying not do your own thing, by any means. WP is open source, go nuts! I was attempting to explain why meta_key / meta_value gives the most flexibility.

As many blogs do NOT run into 5000+ users… you certainly could rationalize hacking up some code and using one big wide table in place of the current meta table. Upgrades will be a bummer, but if you keep good notes, shouldn’t be too bad I suppose.

Someone kindly tell me if this makes sense or if I’m off my gourd:

$infovalues = $wpdb->get_results("SELECT address, city, province, postal_code FROM $wpdb->userinfo WHERE user_id = '$user_id'");
$wpdb->show_errors();

if ($infovalues ) {
foreach ( $infovalues as $inforow) {
foreach ( $inforow as $key => $value) {
$user->{$key} = $value;
} // end foreach
} // end foreach
} //end if

I’m not familiar with using the unserialize function. I understand that it’s used to work out an array from the roles/capabilities data in wp_options table, but what how would it be used in this case of the usermeta table? I’m referring to the original WP 2.01 code for pulling usermeta data that I posted above.

With the code above, I hope to be able to call this:
<?php get_usermeta(241,'address'); ?>

…to get the address of the user with the id 241.

See:
https://us2.php.net/manual/en/function.serialize.php
https://us2.php.net/manual/en/function.unserialize.php

I know that. Been there. But why is it used in the original function? It doesn’t seem that data would be serialized in the usermeta. Is it for other functions to have the ability? Would I be better off using serialized data?

gadzooks, it works.

although I found I must repeat the above code in the function get_userdatabylogin() as well for it to work on the profile of the logged in user. And I’d also need to update get_usermeta in functions.php for that function to work for me.

If any database/WP gurus out there can give me their theory on the prudence of my method to pull extra user data from a new table rather than serializing all the data I want into a field in the usermeta table, please I’d like to know what you have to say.

Bear in mind that I’m doing it this way partly because I’m not familiar with the structure of the serialize functions and worry that they’ll cause me to many headaches in development because I have trouble understanding them. However, my focus is on speed and efficiency of the system that will have thousands of users in the table.