WordPress database access with $wpdb

What is in $wpdb object

WordPress provides a global object, $wpdb , which is an instantiation of the wpdb class. By default, $wpdb is instantiated to access the WordPress database, but it can be used to manipulate any table in the WordPress database, not just those created by WordPress itself.

With the following code

include "../../wp-config.php";
print_r($wpdb);

We will get result as (original data before formatting):

wpdb Object ( [show_errors] => [suppress_errors] => [last_error] => [num_queries] => 42 [num_rows] => 0 [rows_affected] => 0 [insert_id] => 0 [last_query] => SELECT option_value FROM wp_options WHERE option_name = 'pods_upload_require_login_cap' LIMIT 1 [last_result] => Array ( ) [result:protected] => mysqli_result Object ( [current_field] => 0 [field_count] => 1 [lengths] => [num_rows] => 0 [type] => 0 ) [col_meta:protected] => Array ( ) [table_charset:protected] => Array ( ) [check_current_query:protected] => 1 [checking_collation:wpdb:private] => [col_info:protected] => [queries] => [reconnect_retries:protected] => 5 [prefix] => wp_ [base_prefix] => wp_ [ready] => 1 [blogid] => 0 [siteid] => 0 [tables] => Array ( [0] => posts [1] => comments [2] => links [3] => options [4] => postmeta [5] => terms [6] => term_taxonomy [7] => term_relationships [8] => termmeta [9] => commentmeta ) [old_tables] => Array ( [0] => categories [1] => post2cat [2] => link2cat ) [global_tables] => Array ( [0] => users [1] => usermeta ) [ms_global_tables] => Array ( [0] => blogs [1] => blogmeta [2] => signups [3] => site [4] => sitemeta [5] => sitecategories [6] => registration_log ) [comments] => wp_comments [commentmeta] => wp_commentmeta [links] => wp_links [options] => wp_options [postmeta] => wp_postmeta [posts] => wp_posts [terms] => wp_terms [term_relationships] => wp_term_relationships [term_taxonomy] => wp_term_taxonomy [termmeta] => wp_termmeta [usermeta] => wp_usermeta [users] => wp_users [blogs] => [blogmeta] => [registration_log] => [signups] => [site] => [sitecategories] => [sitemeta] => [field_types] => Array ( [post_author] => %d [post_parent] => %d [menu_order] => %d [term_id] => %d [term_group] => %d [term_taxonomy_id] => %d [parent] => %d [count] => %d [object_id] => %d [term_order] => %d [ID] => %d [comment_ID] => %d [comment_post_ID] => %d [comment_parent] => %d [user_id] => %d [link_id] => %d [link_owner] => %d [link_rating] => %d [option_id] => %d [blog_id] => %d [meta_id] => %d [post_id] => %d [user_status] => %d [umeta_id] => %d [comment_karma] => %d [comment_count] => %d [active] => %d [cat_id] => %d [deleted] => %d [lang_id] => %d [mature] => %d [public] => %d [site_id] => %d [spam] => %d ) [charset] => utf8mb4 [collate] => utf8mb4_unicode_520_ci [dbuser:protected] => hostlike_211cnca [dbpassword:protected] => Pass123$$ [dbname:protected] => hostlike_211book [dbhost:protected] => localhost [dbh:protected] => mysqli Object ( [affected_rows] => 0 [client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $ [client_version] => 50012 [connect_errno] => 0 [connect_error] => [errno] => 0 [error] => [error_list] => Array ( ) [field_count] => 1 [host_info] => Localhost via UNIX socket [ info] => [insert_id] => 0 [server_info] => 5.6.40-84.0-log [server_version] => 50640 [stat] => Uptime: 2202200 Threads: 10 Questions: 6519834844 Slow queries: 73439 Opens: 35182629 Flush tables: 1 Open tables: 2048 Queries per second avg: 2960.600 [sqlstate] => 00000 [protocol_version] => 10 [thread_id] => 18004069 [warning_count] => 0 ) [func_call] => $db->query("SELECT option_value FROM wp_options WHERE option_name = 'pods_upload_require_login_cap' LIMIT 1") [is_mysql] => 1 [incompatible_modes:protected] => Array ( [0] => NO_ZERO_DATE [1] => ONLY_FULL_GROUP_BY [2] => STRICT_TRANS_TABLES [3] => STRICT_ALL_TABLES [4] => TRADITIONAL [5] => ANSI ) [use_mysqli:wpdb:private] => 1 [has_connected:wpdb:private] => 1 [categories] => wp_categories [post2cat] => wp_post2cat [link2cat] => wp_link2cat )

Format data structure

function console($obj) {
    $js = json_encode($obj);
    print_r('<script>console.log('.$js.')</script>');
}
console($wpdb);

Formated wpdb data:

Here list the basic tables in the database that created by WordPress.

wp_commentmeta
wp_comment
wp_links
wp_options
wp_postmeta
wp_posts
wp_termsmeta
wp_terms
wp_term_relationships
wp_term_taxonomy
wp_usermeta
wp_user

Compare to the existing tables with wpdb data structure, we can find that they are not the same thing. Actually, wpdb defined its own data structure, just based on WordPress standard database, it also support multisite. In this case wpdb creates data objects by itself, and does not actually access the database. - run faster!

The wpdb functions

Get row

Get single row: $wpdb->get_row();

This function retrieve an entire row from a query.

$result = $wpdb->get_row( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );

The second parameter could be one of the following:

OBJECT: an objects with the columns of the table as the members of the object.

ARRAY_A: returns an associative array.

ARRAY_N: returns an indexed array.

It returns the specific row. Show result as:

echo $result->option_name;

stdClass Object ( [option_id] => 1 [option_name] => siteurl [option_value] => https://book.211cn.ca [autoload] => yes )

Get multi rows: $wpdb->get_results();

This function gets multiple rows data.

$results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );

The result as:

echo $results[0]->option_name;
Array ( [0] => stdClass Object ( [option_id] => 1 [option_name] => siteurl [option_value] => https://book.211cn.ca [autoload] => yes ) )

Using super global we can get the same result:

$results = $GLOBALS['wpdb']->get_results( "SELECT * FROM {$wpdb->prefix}options WHERE option_id = 1", OBJECT );
echo $results[0]->option_name;

To retrieve its result, use foreach loop, like the following code sample:

foreach($results as $result){
    $id = $result->id;
}

Get column value: $wpdb->get_col()

This function gets values from a column and returns column values in one dimensional array.

$postids = $wpdb->get_col( $wpdb->prepare(
      "SELECT  post_id FROM $wpdb->postmeta
       WHERE  meta_key = %s", '_wp_attached_file') );
 print_r($postids);  //Array ( [0] => 21 [1] => 37 [2] => 1099 [3] => 1102 [4] => 2690 [5] => 3432 [6] => 3438 [7] => 3439 [8] => 3440 [9] => 5641 )

Get a single value: $wpdb->get_var()

The get_var function takes in a query and returns a single value which can be directly assigned to a variable.

returns a single variable from the database.

$user_count=>$wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users");
"User count is {$user_count}";

Data Manipulation

Insert: $wpdb->insert()

Insert a single row into a table.

insert( $table, $data, $format);

$data - array data to insert, in (column=>value) pairs
$format - An array of formats to be mapped to each of the values in $data. Example:

$wpdb->insert(
  $wpdb->postmeta,
  array(
    'post_id' => 1,
    'meta_key' => 'price',
    'meta_value' => '500'
  ),
  array('%d','%s','%s')
);

$wpdb->insert($wpdb->usermeta, compact('user_id', 'meta_key', 'meta_value') );

After insert, the ID generated by AUTO_INCREMENT column can be accessed:

$wpdb->insert_id

Update: $wpdb->update()

This function updates data in a table.

Note that the update logic behind the scene as following:

• • if row exists - update
  • if row not exists - insert

The previous insert statement can also be achieved by the following update statement.

$wpdb->update(
    $wpdb->postmeta,
    array( 'meta_value' => '750' ),
  array(
    'post_id' => 1,
    'meta_key' => 'price',
  )
);

Replace: $wpdb->replace()

This function replace row values by primary key or unique index. If the key or index does not exists it inserts a new row, and the new id can be accessed by $wpdb->insert_id.

replace( $table, $data, $format );
$wpdb->insert_id

Example:

$wpdb->replace( $wpdb->prefix . 'fafa', 
   array( 
   'id',
   'title' =>  trim($row->item(0)->nodeValue) ,
   'liveprice' => trim($row->item(2)->nodeValue)  ,
   'changing'  => trim($row->item(4)->nodeValue)  ,
   'lowest'    => trim($row->item(6)->nodeValue)  ,
   'topest'    => trim($row->item(8)->nodeValue)  ,
   'time'      => trim($row->item(10)->nodeValue)), 
   array( 
   '%d',
   '%s',
   '%s',
   '%s',
   '%s',
   '%s',
   '%s'
));
$id = $wpdb->insert_Id;

Delete rows: $wpdb->delete()

This function deletes a row from the table.

delete( $table, $where, $where_format= null );
$wpdb->delete( 'table', array( 'ID'=> 1 ) );

// Using where formatting.
$wpdb->delete( 'table', array( 'ID'=> 1 ), array( '%d' ) );

General Queries

This function performs a MySQL database query.

query('query');

returns an integer value indicating the number of rows affected/selected for SELECT, INSERT, DELETE, UPDATE

For CREATE, ALTER, TRUNCATE and DROP SQL statements, (which affect whole tables instead of specific rows) this method returns TRUE on success, if mysql error is encountered, the method will return FALSE.

Note: do not put any character before query, or you won't get the expected return value.

Show and Hide SQL Errors

Getting Column Information

$wpdb->get_col_info('type', offset);

Clearing the Cache

clear the SQL result cache. This clears $wpdb->last_result, $wpdb->last_query, and $wpdb->col_info.

$wpdb->flush();

Security feature of wpdb

To protect query against sql injection attack, all data must be sql escaped by using $wpdb->prepare():

$sql = "SELECT * FROM {$wpdb->prefix}options WHERE option_id = %d";
$sql = $wpdb->prepare( $sql , '1 and 1=1' );
echo $sql;

Result:

SELECT * FROM wp_options WHERE option_id = 1

It filter out the illigal string - ' and 1=1'.

The placeholder here is:

• • %s (string)
  • %d (integer)
  • %f (float)

$wpdb->query()

This can be used for DML.

$wpdb->query(
   $wpdb->prepare("INSERT INTO $wpdb->postmeta ( post_id, meta_key, meta_value ) VALUES ( %d, %s, %s )",
      array(10,
         'Funny Phrases',
         "WordPress' database interface is like Sunday Morning: Easy."
      )
   )
);

 208 total views,  4 views today

Author: IT Team