Custom Database Queries

Specifically, we’re going to look at the standard operations for Select ,insert ,update ,delete and more, we’ll take a look at examples of each. Then, we’ll finalize our discussion by talking about parameterization so that we’re able to write secure queries.

SELECT

// Always globalize $wpdb
global $wpdb;
// Select a single variable - the post title of the first post
$title = $wpdb->get_var( "SELECT post_title FROM $wpdb->posts WHERE ID = 1;" );
echo $title;
global $wpdb;
// Select an entire row of information from the first post
$info = $wpdb->get_row( "SELECT * FROM $wpdb->posts WHERE ID = 1;" );
print_r( $info );
// Get all of the titles of posts (and pages and custom post types) that have an ID less than 10
$titles = $wpdb->get_col( "SELECT post_title FROM $wpdb->posts WHERE ID < 10;" );
print_r( $titles );
// Retrieve a generic result set of post IDs and post titles from the posts table where posts have an ID less than 10
$results = $wpdb->get_results( "SELECT ID, post_title FROM $wpdb->posts WHERE ID < 10;" );
print_r( $results );

INSERT

global $wpdb;
// Into the posts table, insert a published post with a title and content with the arbitrary post ID of 9999
$wpdb->insert(
    'wp_posts',
    array(
        'id'           => 9999,
        'post_title'   => 'Inserted Post',
        'post_status'  => 'publish',
        'post_content' => 'Example content for a post inserted via direct query.'
    ),
    array( '%d', '%s', '%s', '%s' )
);

UPDATE

global $wpdb;
// Update the title and the post content of the post table row hat has the ID of 9999
$wpdb->update(
    'wp_posts',
    array(
        'post_title'   => 'Inserted Post (Updated)',
        'post_content' => 'Example content for a post *updated* via direct query.'
    ),
    array( 'id' => 9999 ),
    array( '%d','%s', '%s' ),
    array( '%d' )
);

DELETE

global $wpdb;
// Delete the record from the database where the ID column has the value 9999
$wpdb->delete(
    'wp_posts',
    array( 'ID' => 9999 ),
    array( '%d' )
);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s