IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. WordPress database access with $wpdb

WordPress database access with $wpdb

2021-02-01 3595 Views 0 Like 0 Comments

Table of Contents

  • What is in $wpdb object
  • The wpdb functions
    • Get row
      • Get single row: $wpdb->get_row();
      • Get multi rows: $wpdb->get_results();
    • Get column value: $wpdb->get_col()
    • Get a single value: $wpdb->get_var()
    • Data Manipulation
      • Insert: $wpdb->insert()
      • Update: $wpdb->update()
      • Replace: $wpdb->replace()
      • Delete rows: $wpdb->delete()
    • General Queries
  • Show and Hide SQL Errors
  • Getting Column Information
  • Clearing the Cache
  • Security feature of 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 );
$id = $result->id;

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 )
foreach($postids as $id) $ids.=','.$id;

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

<?php $wpdb->show_errors(); ?>
<?php $wpdb->hide_errors(); ?>
<?php $wpdb->print_error(); ?>
<?php define( 'DIEONDBERROR', true ); ?>

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."
      )
   )
);

Loading

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Tags: None
Last updated:2021-08-11

IT Team

This person is lazy and left nothing

Like
< Previous
Next >

Comments

Cancel reply
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Parallax One analysis Common Regular Expressions How to change copyright footer for Parallax-One theme Add further reading list after post Exam MB2-716 Dynamics 365 Customization and Configuration Visual Paradigm v16.1 Installation
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com