The wpdb Class

This global WordPress class is key for using queries. In fact, every function uses this class.

Using query:

The query function needs a string containing the custom query. The returning value is an integer corresponding to the number of rows affected/selected, and false when there is an error.

$query = "SELECT COUNT(apple) FROM fruits";
$wpdb->query($query);

get_results

This function gets multiple rows when executing a query. By default the result of the function is an array.

query = "
SELECT * FROM wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id WHERE wtt.taxonomy = 'post_tag' AND wtt.count = 0";

wpdb->get_results($query);

get_var

This will return one variable from the database, but the complete result of the query is cached for later use. Returns NULL if no result is found.

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

get_row

A complete row will be returned as a result of the function, which can be an object, an associative array, or a numerically indexed array. NULL is the result when no matching data is found. result_type can be OBJECT, ARRAY_A or ARRAY_N (object, associative array or numbered array). Offset is an integer with a default of 0.

$query = " SELECT * FROM wp_posts WHERE post_type = 'post'";

$wpdb->get_row($query, ARRAY_A, 3);

get_col

For getting a column, use this function. Output will be a dimensional array. An empty array will be returned if no result is found. The second parameter is the column offset.

$query = " SELECT * FROM wp_posts WHERE post_type = 'post'";

$wpdb->get_col($query, 3);

Inserting Data

The used parameters in order are:the name of the table to insert data into the data to insert (column => value pairs) without escaping an array of formats to be mapped to each of the values in $data. If not present, all values will be treated as strings.

$wpdb->insert($table, $data, $format);

$wpdb->insert(
'foods',
array(
'fruit' => 'apple',
'year' => 2012
),
array(
'%s',
'%d'
)
);

Updating Data

The used parameters in order are:

table name

Data

where conditions

Format

where_format

$wpdb->update(
'foods',
array(
'fruit' => 'apple', // string
'year' => 'value2' // integer (number)
),
array( 'ID' => 1 ),
array(
'%s', // value1
'%d' // value2
),
array( '%d' )
);

Column Information

You can get information about the columns of the most recent result with this function. When a function has returned an OBJECT and there are properties you don’t know much about, this can be useful.

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

Type: the information you want to retrieve, some examples are here

name – column name (this is the default)

table – name of the table the column belongs to

max_length – maximum length of the column

not_null – 1 if the column cannot be NULL

Create Table

To make sure that the tables exist when you activate the plugin, you need to use the hook register_activation_hook(), which takes two parameters: the file of the main plugin, and the function to run when the plugin is activated.

function SP_Prolicense_Db_activation() {
global $wpdb;
$query = "create table wp_SP_Pro_license
(LiID int,LiNo varchar(255));";
$wpdb->query($query);
dbDelta( $sql );
}

The dbDelta() function will examine the current database and compare it with the table structure that will be created from your SQL. This means that if you are using SQL to create a new table and the table already exists, it will not run your SQL query.