Couldnot answer such questions on an interview, cheat sheet is needed. The database s c h e m a with specific functions found to work with each table described with real world examples.

  • wp_options
  • wp_users
  • wp_usermeta
  • wp_posts
  • wp_postmeta
  • wp_comments
  • wp_commentsmeta
  • wp_terms, wp_term_taxonomy, wp_term_relationships

wp_options - stores the name, description, and admin email that you entered , also come prepopulated with a few records that store the various default settings.

<?php
// add option
$twitters = array( '@ssd', '@bmess', '@jason_coleman' );
add_option( 'ssd_twitter_accounts', $twitters );
 
// get option
$ssd_twitter_accounts = get_option( 'ssd_twitter_accounts' );
echo '<pre>';
print_r( $ssd_twitter_accounts );
echo '</pre>';
 
// update option
$twitters = array_merge( 
    $twitters, 
    array( 
        '@webdevstudios', 
        '@strangerstudios' 
    ) 
);
update_option( 'ssd_twitter_accounts', $twitters );
 
// get option
$ssd_twitter_accounts = get_option( 'ssd_twitter_accounts' );
echo '<pre>';
print_r( $ssd_twitter_accounts );
echo '</pre>';
 
// delete option
delete_option( 'ssd_twitter_accounts' );
 
 
/*
The output from the above example should look something like this:
Array
(
    [0] => @ssd
    [1] => @bmess
    [2] => @jason_coleman
)
Array
(
    [0] => @ssd
    [1] => @bmess
    [2] => @jason_coleman
    [3] => @webdevstudios
    [4] => @strangerstudios
)
*/
?>

wp_users When log in to WordPress with your username and password, you are referencing data stored in this table. All users and their default data are stored in the wp_users table.

<?php
// insert user
$userdata = array(
    'user_login'    => 'brian',
    'user_pass'     => 'KO03gT7@n*',
    'user_nicename' => 'Brian',
    'user_url'      => 'http://webdevstudios.com/',
    'user_email'    => 'brian@schoolpress.me',
    'display_name'  => 'Brian',
    'nickname'      => 'Brian',
    'first_name'    => 'Brian',
    'last_name'     => 'Messenlehner',
    'description'   => 'This is a SchoolPress Administrator account.',
    'role'          => 'administrator'
);
wp_insert_user( $userdata );
 
// create users
wp_create_user( 'jason', 'YR529G%*v@', 'jason@schoolpress.me' );
 
// get user by login
$user = get_user_by( 'login', 'brian' );
echo 'email: ' . $user->user_email  . ' / ID: ' . $user->ID . '<br>';
echo 'Hi: ' . $user->first_name . ' ' . $user->last_name . '<br>';
 
// get user by email
$user = get_user_by( 'email', 'jason@schoolpress.me' );
echo 'username: ' . $user->user_login . ' / ID: ' . $user->ID . '<br>';
 
// update user - add first and last name to brian and change role to admin
$userdata = array(
    'ID'         => $user->ID,
    'first_name' => 'Jason',
    'last_name'  => 'Coleman',
    'user_url'   => 'http://strangerstudios.com/',
    'role'       => 'administrator'
);
wp_update_user( $userdata );
 
// get userdata for brian
$user = get_userdata( $user->ID );
echo 'Hi: ' . $user->first_name . ' ' . $user->last_name . '<br>';
 
// delete user - delete the original admin and set their posts to our new admin
// wp_delete_user( 1, $user->ID );
 
/*
The output from the above example should look something like this:
email: brian@schoolpress.me / ID: 2
Hi: Brian Messenlehner
username: jason / ID: 3
Hi: Jason Coleman
*/
?>

wp_usermeta - to store additional data along with a user. WordPress provides an easy way to do this without having to add additional columns to the users table. Each record is associated to a user ID in the wp_user table by the user_id field

<?php
// get brian's id
$brian_id = get_user_by( 'login', 'brian' )->ID;
 
// add user meta - unique is set to true. no polygamy! only one wife at a time.
add_user_meta( 
    $brian_id, 
    'ssd_wife', 
    'Robin Jade Morales Messenlehner', 
    true
);
 
// get user meta - returning a single value
$brians_wife = get_user_meta( $brian_id, 'ssd_wife', true);
echo "Brian's wife: " . $brians_wife . "<br>";
 
// add user meta - no 3rd parameter/unique. 
// can have as many kids as wife will let me.
add_user_meta( $brian_id, 'ssd_kid', 'Dalya' );
add_user_meta( $brian_id, 'ssd_kid', 'Brian' );
add_user_meta( $brian_id, 'ssd_kid', 'Nina' );
 
// update user meta - this will update brian to brian jr.
update_user_meta( $brian_id, 'ssd_kid', 'Brian Jr', 'Brian' );
 
// get user meta - returning an array
$brians_kids = get_user_meta( $brian_id, 'ssd_kid' );
echo "Brian's kids:";
echo '<pre>';
print_r($brians_kids);
echo '</pre>';
 
// delete brian's user meta
delete_user_meta( $brian_id, 'ssd_wife' );
delete_user_meta( $brian_id, 'ssd_kid' );
 
// get jason's id
$jason_id = get_user_by( 'login', 'jason' )->ID;
 
// update user meta - creates user meta if key doesn't exist for the user.
update_user_meta( $jason_id, 'ssd_wife', 'Kimberly Ann Coleman' );
 
// get user meta - returning an array
$jasons_wife = get_user_meta( $jason_id, 'ssd_wife' );
echo "Jason's wife:";
echo '<pre>';
print_r($jasons_wife);
echo '</pre>';
 
// add user meta - storing as an array
add_user_meta( $jason_id, 'ssd_kid', array( 'Isaac', 'Marin' ) );
 
// get user meta - returning a single value which happens to be an array.
$jasons_kids = get_user_meta( $jason_id, 'ssd_kid', true );
echo "Jason's kids:";
echo '<pre>';
print_r($jasons_kids);
echo '</pre>';
 
// delete jason's user meta
delete_user_meta( $jason_id, 'ssd_wife' );
delete_user_meta( $jason_id, 'ssd_kid' );
 
/*
The output from the above example should look something like this:
Brian's wife: Robin Jade Morales Messenlehner
Brian's kids:
Array
(
    [0] => Dalya
    [1] => Brian Jr
    [2] => Nina
)
Jason's wife:
Array
(
    [0] => Kimberly Ann Coleman
)
Jason's kids:
Array
(
    [0] => Isaac
    [1] => Marin
)
*/
?>

wp_posts table is where most of post data is stored. By default, WordPress comes with posts and pages. Both are technically posts and are stored in this table. The post_type field is distinguishes type of post: post, a page, a menu item, a revision, or any custom post type

<?php
// insert post - set post status to draft
$args = array(
    'post_title'   => 'Building Web Apps with WordPress',
    'post_excerpt' => 'WordPress as an Application Framework',
    'post_content' => 'WordPress is the key to successful cost effective 
    web solutions in most situations. Build almost anything on top of the 
    WordPress platform. DO IT NOW!!!!',
    'post_status'  => 'draft',
    'post_type'    => 'post',
    'post_author'  => 1,
    'menu_order'   => 0
);
$post_id = wp_insert_post( $args );
echo 'post ID: ' . $post_id . '<br>';
 
// update post - change post status to publish
$args = array(
    'ID'          => $post_id,
    'post_status' => 'publish'
);
wp_update_post( $args );
 
// get post - return post data as an object
$post = get_post( $post_id );
echo 'Object Title: ' . $post->post_title . '<br>';
 
// get post - return post data as an array
$post = get_post( $post_id, ARRAY_A );
echo 'Array Title: ' . $post['post_title'] . '<br>';
 
// delete post - skip the trash and permanently delete it
wp_delete_post( $post_id, true );
 
// get posts - return 100 posts
$posts = get_posts( array( 'posts_per_page' => '100') );
// loop all posts and display the ID & title
foreach ( $posts as $post ) {
    echo $post->ID . ': ' .$post->post_title . '<br>';
}
 
/*
The output from the above example should look something like this:
post ID: 589
Object Title: Building Web Apps with WordPress
Array Title: Building Web Apps with WordPress
"A list of post IDs and Titles from your install"
*/
?>

wp_postmeta to store additional data along with a post without having to add additional fields to the posts table. Store as much post metadata as you need to in the wp_postmeta table. Each record is associated to a post through the post_id field. When editing any post in the backend of WordPress, you can add/update/delete metadata or Custom Fields via the UI.

<?php
// get posts - return the latest post
$posts = get_posts( 
    array( 
        'posts_per_page' => '1', 
        'orderby' => 'post_date', 
        'order' => 'DESC' 
    ) 
);
foreach ( $posts as $post ) {
    $post_id = $post->ID;
 
    // update post meta - public meta data
    $content = 'You SHOULD see this custom field when editing your latest post.';
    update_post_meta( $post_id, 'ssd_displayed_field', $content );
 
    // update post meta - hidden meta data
    $content = str_replace( 'SHOULD', 'SHOULD NOT', $content );
    update_post_meta( $post_id, '_ssd_hidden_field', $content );
 
    // array of student logins
    $students[] = 'dalya';
    $students[] = 'ashleigh';
    $students[] = 'lola';
    $students[] = 'isaac';
    $students[] = 'marin';
    $students[] = 'brian';
    $students[] = 'nina';
 
    // add post meta - one key with serialized array as value
    add_post_meta( $post_id, 'ssd_students', $students, true );
 
    // loop students and add post meta record for each student
    foreach ( $students as $student ) {
        add_post_meta( $post_id, 'ssd_student', $student );
    }
 
    // get post meta - get all meta keys
    $all_meta = get_post_meta( $post_id );
    echo '<pre>';
    print_r( $all_meta );
    echo '</pre>';
 
    // get post meta - get 1st instance of key
    $student = get_post_meta( $post_id, 'ssd_student', true );
    echo 'oldest student: ' . $student;
 
    // delete post meta
    delete_post_meta( $post_id, 'ssd_student' );
}
 
/*
The output from the above example should look something like this:
Array
(
    [_ssd_hidden_field] => Array
        (
            [0] => You SHOULD NOT see this custom field 
            when editing your latest post.
        )
 
    [ssd_displayed_field] => Array
        (
            [0] => You SHOULD see this custom field when editing 
            your latest post.
        )
 
    [ssd_students] => Array
        (
            [0] => a:7:{i:0;s:5:"dalya";i:1;s:8:"ashleigh";i:2;
            s:4:"lola";i:3;s:5:"isaac";i:4;s:5:"marin";i:5;
            s:5:"brian";i:6;s:4:"nina";}
        )
 
    [ssd_student] => Array
        (
            [0] => dalya
            [1] => ashleigh
            [2] => lola
            [3] => isaac
            [4] => marin
            [5] => brian
            [6] => nina
        )
)
oldest student: dalya
*/
?>

wp_comments Comments can be left against any post. The wp_comments table stores individual comments for any post and default associated data

<?php
// insert post
$args = array(
    'post_title'   => '5 year anniversary on 9/10/16',
    'post_content' => 'Think of somthing cool to do, make a comment!',
    'post_status'  => 'publish'
);
$post_id = wp_insert_post( $args );
echo 'post ID: ' . $post_id . ' - ' . $args['post_title'] . '<br>';
 
// make comments array
$comments[] = 'Take a trip to South Jersey';
$comments[] = 'Dinner at Taco Bell';
$comments[] = 'Make a baby';
 
// loop comments array
foreach ( $comments as $comment ) {
    // insert comments
    $commentdata = array(
        'comment_post_ID' => $post_id,
        'comment_content' => $comment,
    );
    $comment_ids[] = wp_insert_comment( $commentdata );
}
echo 'comments:<pre>';
print_r( $comments );
echo '</pre>';
 
// update comment
$commentarr['comment_ID'] = $comment_ids[0];
$commentarr['comment_content'] = 'Take a trip to Paris, France';
wp_update_comment( $commentarr );
 
// insert comment - sub comment from parent id
$commentdata = array(
    'comment_post_ID' => $post_id,
    'comment_parent' => $comment_ids[0],
    'comment_content' => 'That is a pretty good idea...',
);
wp_insert_comment( $commentdata );
 
// get comments - search taco bell
$comments = get_comments( 'search=Taco Bell&number=1' );
foreach ( $comments as $comment ) {
    // insert comment - sub comment of taco bell comment id
    $commentdata = array(
        'comment_post_ID' => $post_id,
        'comment_parent' => $comment->comment_ID,
        'comment_content' => 'Do you want to get smacked up?',
    );
    wp_insert_comment( $commentdata );
}
 
// get comment - count of comments for this post
$comment_count = get_comments( 'post_id= ' . $post_id . '&count=true' );
echo 'comment count: ' . $comment_count . '<br>';
 
// get comments - get all comments for this post
$comments = get_comments( 'post_id=' .$post_id );
foreach ( $comments as $comment ) {
    // update 1st comment
    if ( $comment_ids[0] == $comment->comment_ID ) {
        $commentarr = array(
            'comment_ID' => $comment->comment_ID,
            'comment_content' => $comment->comment_content . 
            ' & make a baby!',
        );
        wp_update_comment( $commentarr );
        // delete all other comments
    }else {
        // delete comment
        wp_delete_comment( $comment->comment_ID, true );
    }
}
 
// get comment - new comment count
$comment_count = get_comments( 'post_id= ' . $post_id . '&count=true' );
echo 'new comment count: ' . $comment_count . '<br>';
 
// get comment - get best comment
$comment = get_comment( $comment_ids[0] );
echo 'best comment: ' . $comment->comment_content;
 
/*
The output from the above example should look something like this:
post ID: 91011 - 5 year anniversary on 9/10/16
comments:
Array
(
    [0] => Take a trip to South Jersey
    [1] => Dinner at Taco Bell
    [2] => Make a baby
)
comment count: 5
new comment count: 1
best comment: Take a trip to Paris, France & make a baby!
*/
?>

wp_commentsmeta Just like the wp_usermeta and wp_postmeta table, this table stores any custom, additional data tied to a comment by the comment_id fields

<?php
// get comments - last comment ID
$comments = get_comments( 'number=1' );
foreach ( $comments as $comment ) {
    $comment_id = $comment->comment_ID;
 
    // add comment meta - meta for view date & ip address
    $viewed = array( date( "m.d.y" ), $_SERVER["REMOTE_ADDR"] );
    $comment_meta_id = add_comment_meta( 
        $comment_id, 
        'ssd_view_date', 
        $viewed, 
        true 
    );
    echo 'comment meta id: ' . $comment_meta_id;
 
    // update comment meta - change date format to fomrat like 
    // October 23, 2020, 12:00 am instead of 10.23.20
    $viewed = array( date( "F j, Y, g:i a" ), $_SERVER["REMOTE_ADDR"] );
    update_comment_meta( $comment_id, 'ssd_view_date', $viewed );
 
    // get comment meta - all keys
    $comment_meta = get_comment_meta( $comment_id );
    echo '<pre>';
    print_r( $comment_meta );
    echo '</pre>';
 
    // delete comment meta
    delete_comment_meta( $comment_id, 'ssd_view_date' );
}
 
/*
The output from the above example should look something like this:
comment meta id: 16
Array
(
    [ssd_view_date] => Array
        (
            [0] => a:2:{i:0;s:24:"August 11, 2013, 4:16 pm";
            i:1;s:9:"127.0.0.1";}
        )
 
)
*/
?>

wp_terms table stores each category name or term name , Each record is tied to its taxonomy in the wp_term_taxonomy table by the term_id, each category or tag is stored in this table, and technically they are both taxonomies. The wp_term_taxonomy table stores each taxonomy type you are using. The wp_term_relationships table relates a taxonomy term to a post. Every time assign a category or tag to a post, it’s being linked to that post in this table.

<?php
// list all taxonomies
$taxonomies = get_taxonomies();
echo '<pre>';
print_r( $taxonomies );
echo '</pre>';
 
// list all taxonomies tied to the post post type that have an available ui
$args = array(
    'public' => 1,
    'show_ui' => 1,
    'object_type' => array( 'post')
);
$taxonomies = get_taxonomies( $args );
echo '<pre>';
print_r( $taxonomies );
echo '</pre>';
 
// add some new terms for the category and post_tag taxonomies
wp_insert_term( 'Fun',    'post_tag' );
wp_insert_term( 'Boring', 'post_tag' );
wp_insert_term( 'Home',   'category' );
wp_insert_term( 'Work',   'category' );
wp_insert_term( 'School', 'category' );
 
// get school term id
$term = get_term_by( 'slug', 'school', 'category' );
$term_id = $term->term_id;
echo $term_id;
 
// add sub terms under the school term
wp_insert_term( 'Math',      'category', array( 'parent' => $term_id ) );
wp_insert_term( 'Science',   'category', array( 'parent' => $term_id ) );
wp_insert_term( 'WordPress', 'category', array( 'parent' => $term_id ) );
 
// list all terms for the category and post_tag taxonomies
$args = array(
     'orderby'    => 'count',
     'hide_empty' => 0
);
$terms = get_terms( $taxonomies, $args );
echo '<pre>';
print_r( $terms );
echo '</pre>';
 
 
// get posts - return the latest post
$posts = get_posts( array( 'posts_per_page' => '1', 'orderby' => 'post_date', 'order' => 'DESC' ) );
foreach ( $posts as $post ) {
    $post_id = $post->ID;
 
    echo $post_id;
}
?>

2 comments

#90
Monday, September 22, 2014 2:07 AM
Way cool! Some extremely valid points! I appreciate you penning this article
and also the rest off the site is also really good.
#89
Monday, September 22, 2014 2:06 AM
Way cool! Some extremely valid points! I appreciate you penning this article
and also the rest off the site is also really good.

Leave a Comment

Fields with * are required.

Please enter the letters as they are shown in the image above.
Letters are not case-sensitive.