Using the $wpdb object in WordPress

WordPress stores its data in a MySql database.Given below is a snapshot of a MySql database containing a WordPress installation.

Almost all WordPress data bar the pictures, video, audio and other files is stored in these tables. We will study these tables a bit later.

Database access in WordPress is best done via the global $wpdb object. It provides a connection to the database and provides utility functions for data manipulation and access.

To access the $wpdb object we simply write:
global $wpdb;

Let us check this by writing the following code in our theme’s index.php file.

<?php
global $wpdb;
if($wpdb)
print ("wpdb found");
else
print("wpdb not found");
?>

Here is the output:

The $wpdb object has been found. Now let us create a table and insert a record.
Here is the required code;

<?php
global $wpdb;
try

{

$sql = "CREATE TABLE `book` (`bookid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `bookname` VARCHAR(50) NOT NULL, `price` INT NOT NULL, `subject` VARCHAR(50) NOT NULL) ENGINE = InnoDB;";
$result=$wpdb->query($sql);
print($result);
$wpdb->insert(  'book',  array(  'bookname' => 'Basic Java',  'price' => 123 , 'subject'=>'Java' ),  array(  '%s',  '%d', '%s' ) );
$newid=$wpdb->insert_id;
print ("New id is $newid<br/>");
}
catch(Exception $ex)
{
print ($ex);
?>

The table has been created.

Click on the table to view the records.

The insert function takes three arguments.
1. The name of the table.
2. The data to be inserted as an array.
3. The data types of the values to be inserted as an array.

The query function simply takes a sql query and executes it on the database.

To update use the following code.

<?php
global $wpdb;
$wpdb->update(  'book',  array(  'bookname' => 'New Book', 'price' => 1200 ),
array( 'bookid' => 1 ),
array(  '%s', '%d' ),
array( '%d' ) );
?>

The first parameter is the table name, second parameter is an array of values that are to be updated, the next array contains the conditions. There are two more arrays containing the data types of the previous two arrays.

This snapshot shows the updated array.

Delete query is similar to the update query and simpler.

<?php
global $wpdb;
$wpdb->delete( 'book', array( 'bookid' => 1 ),array('%d') );
?>

To get the data we shall use the get_results of the $wpdb object. It returns an array of results. Each array element contains all the columns.
Here is the code:

<?php
global $wpdb;
if($wpdb)
echo "WPDB found";
else
echo "WPDB not found";
print("Search<br/>");
$results = $wpdb->get_results( "SELECT * FROM book" );
foreach($results as $result)
echo $result->bookname . "<br/>";
?>

Finally, we shall drop the table that we created.

<?php
global $wpdb;
try
{
$sql = "Drop TABLE `book`";
$result=$wpdb->query($sql);
}
catch(Exception $ex)
{
print ($ex);
}
?>

There is more to learn, especially the prepare function and prevention of sql injection attacks.

Leave a Reply