Best practice for db table use in a plugin

  • Resolved shahar

    (@shahar)


    16 years, 6 months ago

    Hi, I’m writing a plugin that will allow authors to be assigned to categories manually – allowing authors to be defined in groups. Assignment will be done on the category editing page by checking-off authors from a list. There will be 100± authors and 7-12 categories.

    I’ll be needing to display a list of authors for a particular category and, separately, to display the categories to which an author is assigned.

    As I see it there are 2 options for the table structure I’d use:

    1) Create a table which stores an array of assigned author id’s to a category and then add a column to the wp_users table which will store an array of category id’s for each user.

    2) Create a table that has a column for each category and a row for each author. These would be added dynamically whenever a category was edited to assign authors.

    I’d be really grateful for any guidance or suggestions, perhaps I’m missing some important info? I’m relatively new to WordPress…

    Thanks in advance!

Viewing 9 replies - 1 through 9 (of 9 total)
  • MichaelH

    (@michaelh)

    16 years, 6 months ago

    What about using the usermeta table?

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    Option #1 using the wp_usermeta table instead of wp_user? That’s certainly an option…
    Any advice as to what’s best?

    MichaelH

    (@michaelh)

    16 years, 6 months ago

    Using wp_usermeta means not having to modify any tables.

    Just for consideration of how plugins handle ‘user fields’:
    http://wordpress.org/extend/plugins/cimy-user-extra-fields/
    http://wordpress.org/extend/plugins/register-plus/

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    Thanks for the references 🙂

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    Wouldn’t using usermeta make querying for all users assigned to a category cumbersome?

    MichaelH

    (@michaelh)

    16 years, 6 months ago

    Not sure if this is considered cumbersome…

    <?php
//display all users that have "user_category" of "6"
$meta_key = 'user_category';
$meta_value ='6';
$user_ids = $wpdb->get_col($wpdb->prepare("SELECT user_id FROM $wpdb->usermeta WHERE meta_key = %s AND meta_value = %s", $meta_key,$meta_value));
if ($user_ids) {
foreach ($user_ids as $user_id) {
$curuser = get_userdata($user_id);
echo '<p>--User nicename: '.$curuser->user_nicename .', display Name: '. $curuser->display_name . ', link to author posts <a href="' . $author_post_url . '" title="' . sprintf( __( "Posts by %s" ), $curuser->user_nicename ) . '" ' . '>' . $curuser->user_nicename .'</a></p>';
}
}
?>

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    So I’d store an array of group numbers for each user?
    That sounds good 🙂

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    Oh, I forgot to clarify before that users may well be members of more than one group.

    Thread Starter shahar

    (@shahar)

    16 years, 6 months ago

    Using FIND_IN_SET in the query this method seems to be just fine.
    Thanks for your guidance 😀

Viewing 9 replies - 1 through 9 (of 9 total)

The topic ‘Best practice for db table use in a plugin’ is closed to new replies.