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.
// 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.
// 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
// 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
// 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.
// 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
// 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
// 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.
// 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
and also the rest off the site is also really good.
and also the rest off the site is also really good.
Leave a Comment