In Part 1, I mocked up a Posts submenu for adding a quote of the day to the blog. To complete this example, I’ll show how we can process the form when it is posted, perform a basic security check, and then add the post to the blog.
In this case, we will use the wp_insert_post function — one of several WordPress provides for specific types of database interactions. Later in this post, I’ll cover how to create custom database queries, inserts, and updates.
The function shown below is tied to the WordPress ‘admin_init’ action, which occurs after the system has loaded its database connections and other key variables and functions but before anything has been sent to the browser. Depending on whether my processing of the form post is successful, I can redirect the user to a success or error page and then exit. I use this approach to avoid the sort of problems associated with duplicate form posts and because it mirrors the way the native WordPress functions handle things like posting a blog entry.
quote_post.php :
<?php function quote_post() { if($_POST["qnonce"] && $_POST["live"]) { $url = admin_url().'edit.php?page=demo_post_submenu'; if(wp_verify_nonce($_POST["qnonce"], "qday") ) { $posting = stripslashes_deep($_POST); $quote = sprintf('<p style="font-size: 20px;">%s</p><p><em><strong>%s</strong><br />%s</em></p>',$posting["quote"],$posting["author"],$posting["source"]); global $current_user; $my_post = array( 'post_title' => 'Quote of the Day for '.date('l jS \of F Y'), 'post_content' => $quote, 'post_status' => 'publish', 'post_author' => $current_user->ID ); // Insert the post into the database $id = wp_insert_post( $my_post ); header("Location: ".$url."&success=".$id); exit(); } else { header("Location: ".$url."&error=security"); exit(); } } } add_action("admin_init", "quote_post"); ?>
Whenever you write a function that will alter the database or cause something to be publicly displayed on your website, you need to insert some security checks. In this case, the data entry form is in a password-protected part of the website, but we still want to check to ensure any data we process is actually coming from that form.
In the markup for the form, I included this function:
wp_nonce_field("qday","qnonce");
A WordPress nonce is a security code designed to be used only once (“number used once) as an identifier for a transaction. The basic function for creating a nonce to be used in any context is $nonce= wp_create_nonce ('my-nonce');
. The parameter inside the command is a code that is also used on the server side to check that a submission is valid. The format is
if (! wp_verify_nonce($nonce, 'my-nonce') ) die('Security check');
The wp_nonce_field is a utility function for creating a form input field, with the nonce code you assign as the first parameter and the name of the data entry field as the second. So in my example, we check for
if(wp_verify_nonce($_POST["qnonce"], "qday") )
If the submission passes this test, we format the form submission and create a new blog post from this data.
The result looks like this:

Setting and Retrieving Options
Note: This needs to be updated to reflect the newer Settings API, which simplifies saving settings.
If your plugin needs to set and retrieve settings, such as default values for various functions, you can do so through the get_option and update_option functions. Typically, if you have multiple options for your plugin, you will provide a whole array of them to the update_option function and retrieve them the same way — WordPress takes care of serializing and unserializing the data automatically.
Here is an example of creating an options menu and processing input.
options.php :
<?php function demo_options() { $options = get_option('demo'); if ( isset($_POST['notification']) ) { check_admin_referer('demo_action','demo_nonce_field'); $options['notification']=$_POST['notification']; $options['thank_you_message']=stripslashes($_POST['thank_you_message']); update_option('demo', $options); echo '<div class="updated fade"><p>Plugin settings saved.</p></div>'; } $action_url = $_SERVER['REQUEST_URI']; ?> <div class="wrap"> <div id="icon-options-general" class="icon32"><br /></div> <h2>Notification Options</h2> <form name="form1" method="post" action="<?=$action_url?>"> <p>Notification Email:<br /> <input type="text" name="notification" id="notification" value="<?=$options["notification"]?>"> </p> <p>Thank you message:<br /> <textarea name="thank_you_message" id="thank_you_message" ><?=$options["thank_you_message"]?></textarea> </p> <?php wp_nonce_field('demo_action','demo_nonce_field'); ?> <input type="submit" name="button" id="button" value="Submit" /> </form> </div> <?php } function add_demo_options_menu() { $page_title = $menu_title = "Demo Options"; $capability = "manage_options"; $menu_slug = "demo_options"; $function = "demo_options"; add_options_page( $page_title, $menu_title, $capability, $menu_slug, $function); } add_action('admin_menu', 'add_demo_options_menu'); ?>
Here, instead of using the generic function for adding an administration page, I’ve used one of the variations on it, add_options_page, which will make the new menu item a submenu under settings.
Database Programming In WordPress
Functions like wp_insert_post or update_option allow us to update a WordPress website without having to access the underlying database tables directly. For example, get_option('demo')
accomplishes the same thing as mysql_query("SELECT option_value FROM wp_options WHERE option_name='demo' ", $dbpointer)
However, there are times when you want to do something more custom, for which you need to be able to execute your own SQL. I’ve managed several political campaign websites that allowed people to sign up as volunteers or supporters, with a reporting function that allows a campaign manager to access a report on these volunteer signups through the WordPress administration system.
Instead of initializing a database pointer or object, you can use the built-in WordPress $wpdb data access object. One thing I always forget is that the first thing you have to do is declare $wpdb as a global variable within the function that outputs your database form or report. So a data access routine winds up looking something like this.
<?php function volunteer_report() { global $wpdb; if($search = $_GET["search"]) $sql = $wpdb->prepare(" SELECT * FROM volunteers WHERE last_name=%s ", $search); else $sql = "SELECT * FROM volunteers ORDER BY last_name"; $results = $wpdb->get_results($sql, ARRAY_A); if($results) foreach($results as $row) echo $row["first_name"].' '.$row["last_name"]." "; } ?>
The $wpdb->prepare function acts like PHP’s sprintf except that it also takes care of quoting and escaping string data. Like sprintf, it makes sure that any %d placeholders are filled with numeric data. Since the $search variable is coming from a user supplied $_GET, we want to make sure we’re not feeding any improper commands into the database. In a real example, we might also use a nonce appended to the URL.
The $wpdb->get_results function gets us all the matching results for our query. I always use the ARRAY_A parameter to make it return an array, which I can then have the script iterate over using foreach.
The other common patterns for data retrieval are:
$row = $wpdb->get row($sql, ARRAY_A);
and
$last_name = $wpdb->get_var("SELECT last_name FROM volunteers WHERE id=1");
To insert or update records, we pass our SQL to $wpdb->query as shown below.
<?php if($_POST["last_name"]) { $postdata = array_map( 'stripslashes_deep', $_POST ); // code to retrieve and validate post values goes here $sql = $wpdb->prepare("INSERT INTO volunteers SET first_name=%s, last_name=%s",$postdata["first_name"],$postdata["last_name"]); $status = $wpdb->query($sql); //test for errors if($status == false) echo "An error occurred"; else echo "Thank you"; } ?>
The use of the stripslashes_deep routine is recommended in the WordPress docs to make your plugin work consistently across systems that may or may not have the PHP “magic quotes” function turned on. Instead of letting PHP escape quotation marks automatically, you want to do it as part of your own data management routines — for example, by using $wpdb->prepare.
I’ve shown several examples that access custom database tables, but you can also access the standard WordPress tables but just interact with them a little differently. In this example, I use a filter on ‘the_content’ to append a list of post revisions to any page or post that is accessed with the query string ?show_revisions=1 — see http://www.webliberationnow.com/?show_revisions=1.
Note that instead of using the full name of the table I’m querying, I use $wpdb->posts. That’s because although the default install creates the table as wp_posts, some administrators opt to change the ‘wp_’ prefix to something else in their wp-config.php file. So $wpdb->posts will still work, even though the table name has been changed to xyz_posts in an attempt to frustrate SQL-based hack attacks.
filter.php :
<?php function content_revisions($content) { if($_GET["show_revisions"]) { global $wpdb; global $post; $sql = "SELECT post_status, post_name, post_date FROM $wpdb->posts WHERE post_parent = $post->ID"; $results = $wpdb->get_results($sql,ARRAY_A); if($results) { $content .= "<h3>Revisions of this Post</h3>"; foreach($results as $row) { $content .= $row["post_status"]." ".$row["post_name"]." ".$row["post_date"]."<br />"; } } } return $content; } add_filter('the_content','content_revisions'); ?>