Querying the WordPress Database

This is a summary of basic ways to query the WordPress database:

Basic Query returning an object:

get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'future'
AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")

// Echo the title of the first scheduled post
echo $posts[0]->post_title;
?>

 Basic Query returning a row:

get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")

// Echo the title of the most commented post
echo $posts->post_title;
?>

Basic query returning a variable:

get_var("SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' ")

// Echo the user's email address
echo $email;
?>

Insert into the database:

Format is as follows:

$wpdb->insert( $table, $data, $format);

for example:

insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", %s", "%d"));

// Equivalent to:
// INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
?>

Formats are:

%s = string

%d = decimal number

%f = floats

Update a row

Format is as follows:

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

for example:

$wpdb->update( $wpdb->posts, array("post_title" => "Modified Post Title"), array("ID" => 5), array("%s"), array("%d") );

Other Queries:

Use the general query method, for example:

$wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

SQL Injection Protection

All helper functions above escape the input for you.

The general query doesn’t escape data so use the prepare() function:

$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );

or

$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
$wpdb->query($sql);

Insert_ID()

Get last insert auto increment value:

$meta_id = $wpdb->insert_id;

Table Names

Because table prefix might change use this function to get table name:

//get the name of the posts table (wp_posts)
$wpdb->posts

//get name of postmeta table (wp_postsmeta)
$wpdb->postsmeta

Add New Table Name

If you create a custom table in the database you need to add it to the $wpdb object by adding this to your themes functions.php file:

// add table to $wpdb class
if (!isset($wpdb->paystation_transactions)) {
$wpdb->paystation_transactions = $table_prefix . 'paystation_transactions';
}

Full article is here >>

Leave a Comment