WPDB Class: Connect, Fetch & Post Data to WordPress Database

WPDB Class: Connect, Fetch & Post Data to WordPress Database

There were some requirements raised by my clients to post or fetch or display data from WordPress database in table format. Mostly WordPress developers need to develop separate plugin to achieve this functionality & we need use code which deals with the database.

Two things are important in this case. First, we need to connect to database in proper way and secondly use wpdb WordPress class.

Connect to WordPress Database

First you need to check whether below code is present in wp-config.php of your WordPress etup

if ( !defined('ABSPATH') )
define('ABSPATH', dirname(__FILE__) . '/');

If it is present, then go ahead and start creating connection in your theme or plugin. To connect with wp-config.php you have to invoke file and then your can create connection using Database Name, Database UserName, Database Password, Database Host. Here is the code for that

require_once(ABSPATH . 'wp-config.php');
$connection = mysql_connect($DB_HOST, $DB_USER, $DB_PASSWORD);
mysql_select_db($DB_NAME);

Interact With WordPress Database using wpdb class

wpdb is a class defined by WordPress itself. To get any data or post data to WordPress database this class can be used. We will see various examples of usage of this class.

Fetch Data From Database

To retrieve data functions can be used are: get_var (), get_row(), get_col(), get_results(). I am giving simple information along with the simple examples for all below functions.

$wpdb->get_var()

This function is used along with wpdb class to return single value from database. As an example let say we want to display number of users from database then code will be

$user_count = $wpdb-get_var( "SELECT COUNT(*) FROM $wpdb-users" );

$wpdb->get_row()

Sometimes there is a need to retrieve entire row from the database then this function can be used. For this query you will need to provide a unique ID in the query.

$mylink = $wpdb-get_row( "SELECT * FROM $wpdb-links WHERE link_id = 10" );

$wpdb->get_col()

In case if you want to retrieve one column for specific condition in query then this function can be used. Say, you want to display number of published posts of website then code will be

$post_published = $wpdb-get_col( "SELECT * FROM $wpdb-posts WHERE post_status = 'publish'" );

$wpdb->get_results()

This function can retrieve multiple rows for given query. The result of this query is an array. Later we have to get data out of array.

$fivesdrafts = $wpdb-get_results(
"
SELECT ID, post_title
FROM $wpdb-posts
WHERE post_status = 'draft'
AND post_author = 5
"
);
foreach ( $fivesdrafts as $fivesdraft )
{
echo $fivesdraft-post_title;
}

Post Data To Database

$wpdb->insert() & $wpdb->replace() functions are used to push data to database.

$wpdb->insert()

As its name suggest, this function is used to insert data in row. In below query, we are inserting value and its associated data type

$wpdb->insert(
'table',
array(
'column1' => 'value1',
'column2' => 123
),
array(
'%s',
'%d'
)
); 

$wpdb->replace()

This function takes id of any row to replace a row content. In below code the same is executed

$wpdb->replace(
'table',
array(
'indexed_id' => 1,
'column1' => 'value1',
'column2' => 123
),
array(
'%d',
'%s',
'%d'
)
);

Modify Data Entries in Database

$wpdb->update() & $wpdb->delete() functions are used to do updates with database.

$wpdb->update()

The below code updates a row with id 1

$wpdb->update(
'table',
array(
'column1' => 'value1', // string
'column2' => 'value2' // integer (number)
),
array( 'ID' => 1 ),
array(
'%s', // value1
'%d' // value2
),
array( '%d' )
);

$wpdb->delete()

This function deletes row in table with id 1.

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Show Buttons
Hide Buttons
%d bloggers like this: