Category Archives: Code

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

Basic Query returning an object:

 PHP | 
 copy code |
?
 
1
<?php
2
   $posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'future'
3
   AND post_type='post' ORDER BY post_date ASC LIMIT 0,4")
4
 
5
   // Echo the title of the first scheduled post
6
   echo $posts[0]->post_title;
7
?>

 Basic Query returning a row:

 PHP | 
 copy code |
?
 
1
<?php
2
   $posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'
3
   AND post_type='post' ORDER BY comment_count DESC LIMIT 0,1")
4
 
5
   // Echo the title of the most commented post
6
   echo $posts->post_title;
7
?>

Basic query returning a variable:

 PHP | 
 copy code |
?
 
1
<?php
2
   $email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = 'danielpataki' ")
3
 
4
   // Echo the user's email address
5
   echo $email;
6
?>

Insert into the database:

Format is as follows:

 PHP | 
 copy code |
?
 
1
$wpdb->insert( $table, $data, $format);

for example:

 PHP | 
 copy code |
?
 
1
<?php
2
   $wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", %s", "%d"));
3
 
4
   // Equivalent to:
5
   // INSERT INTO wp_usermeta (user_id, meta_key, meta_value) VALUES (1, "awesome_factor", 10);
6
?>

Formats are:

%s = string

%d = decimal number

%f = floats

Update a row

Format is as follows:

 PHP | 
 copy code |
?
 
1
$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

for example:

 PHP | 
 copy code |
?
 
1
$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:

 PHP | 
 copy code |
?
 
1
$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:

 PHP | 
 copy code |
?
 
1
$sql = $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );

or

 PHP | 
 copy code |
?
 
1
$sql = $wpdb->prepare( "INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290 )
2
$wpdb->query($sql);

Insert_ID()

Get last insert auto increment value:

 PHP | 
 copy code |
?
 
1
 $meta_id = $wpdb->insert_id;

Table Names

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

 PHP | 
 copy code |
?
 
1
//get the name of the posts table (wp_posts)
2
$wpdb->posts
3
 
4
//get name of postmeta table (wp_postsmeta)
5
$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:

 PHP | 
 copy code |
?
 
1
// add table to $wpdb class
2
if (!isset($wpdb->paystation_transactions)) {
3
$wpdb->paystation_transactions = $table_prefix . 'paystation_transactions';
4
}
5

Full article is here >>

If you can’t use an absolute url to an include file due to server configuration you can use the ABSPATH WordPress constant:

 PHP | 
 copy code |
?
 
1
require_once(ABSPATH .'/Connections/boatparts.php');

If ABSPATH is not defined you can use this:

 PHP | 
 copy code |
?
 
1
require (dirname(__FILE__)."/makesessionid.php");

Get post type for custom post types etc:

 PHP | 
 copy code |
?
 
1
get_post_type( $post->ID )

Test for a parent page and get its ID

 PHP | 
 copy code |
?
 
1
 if($post->post_parent){
2
 $parentID = ($post->post_parent);
3
}