Support » Fixing WordPress » The usermeta table data

  • Resolved geoffe


    I’d like to know why the usermeta table stores each option as a new record rather than sets the many default options as many fields in one record.

    I’m planning on using WP to manage the online version of a magazine and there are >5000 subscribers that I want to add to the database. I also plan to create a new table that has information about them, such as mailing address, fax, etc. to link to their Login ID.

    Is it easier (and practical) for me to create all of these extra metadata in the usermeta table rather than to structure a table with all this data? If there are 20 metadata fields (or more) per user and 5000 users, that would be 100,000 records in the usermeta table.

    Seems to me that would make a mess of a usermeta table. The option I’m considering is disregarding creating usermeta fields for all the users I’m adding and creating my own table or tables to store all the data for them. One point being that they will be able to login and update this data through WP.

Viewing 8 replies - 1 through 8 (of 8 total)
  • This seems like it would be a great question for the 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'");
    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'");

    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.

    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.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘The usermeta table data’ is closed to new replies.