Wordpress Tutorials

Wordpress setting

Wordpress Function

wordpress top 10 problems

Important Links

Home » Wordpress »

The wpdb Class

WordPress defines a class called wpdb, which contains a set of functions used to interact with a database.

the $wpdb Object

Warning: Methods in the wpdb() class should not be called directly. Use the global $wpdb object instead!

WordPress provides a global object variable, $wpdb, which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php. By default, $wpdb is instantiated to talk to the WordPress database. To access $wpdb in your WordPress PHP code, declare $wpdb as a global variable using the global keyword, or use the superglobal $GLOBALS in the following manner:

// 1st Method - Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object

global $wpdb;
$results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

// 2nd Method - Utilizing the $GLOBALS superglobal. Does not require global keyword ( but may not be best practice )

$results = $GLOBALS['wpdb']->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

The $wpdb object is not limited to the default tables created by WordPress; it can be used to read data from any table in the WordPress database

$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

SELECT a Variable :

The get_var function returns a single variable from the database. Though only one variable is returned, the entire result of the query is cached for later use. Returns NULL if no result is found.
<?php $wpdb->get_var( 'query', column_offset, row_offset ); ?>

query : (string) The query you wish to run. Setting this parameter to null will return the specified variable from the cached results of the previous query.

column_offset :- (integer) The desired column (0 being the first). Defaults to 0.

row_offset :- (integer) The desired row (0 being the first). Defaults to 0.

Example

Retrieve and display the number of users.

<?php
$user_count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->users" );
echo "<p>User count is {$user_count}</p>";
?>

Example

Retrieve and display the sum of a Custom Field value.

<?php
// set the meta_key to the appropriate custom field meta key
$meta_key = 'miles';
$allmiles = $wpdb->get_var( $wpdb->prepare(
"
SELECT sum(meta_value)
FROM $wpdb->postmeta
WHERE meta_key = %s
",
$meta_key
) );
echo "<p>Total miles is {$allmiles}</p>";
?>

SELECT a Row

To retrieve an entire row from a query, use get_row. The function can return the row as an object, an associative array, or as a numerically indexed array. If more than one row is returned by the query, only the specified row is returned by the function, but all rows are cached for later use. Returns NULL if no result is found, consider this when using the returned value in arguments, see example below.
<?php $wpdb->get_row('query', output_type, row_offset); ?>

query :- (string) The query you wish to run.

output_type :- One of three pre-defined constants. Defaults to OBJECT.
OBJECT - result will be output as an object.
ARRAY_A - result will be output as an associative array.
ARRAY_N - result will be output as a numerically indexed array.

row_offset :- (integer) The desired row (0 being the first). Defaults to 0.

Example

Get all the information about Link 10.

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10" );
The properties of the $mylink object are the column names of the result from the SQL query (in this example all the columns from the $wpdb->links table, but you could also query for specific columns only).

echo $mylink->link_id; // prints "10"
In contrast, using

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_A );
would result in an associative array:

echo $mylink['link_id']; // prints "10"
and

$mylink = $wpdb->get_row( "SELECT * FROM $wpdb->links WHERE link_id = 10", ARRAY_N );
would result in a numerically indexed array:

echo $mylink[1]; // prints "10"
If there is no record with ID 10 in the links table, null will be returned. The following would then be false:

if ( null !== $mylink ) {
// do something with the link
return true;
} else {
// no link found
return false;
}

SELECT a Column

To SELECT a column, use get_col. This function outputs a one dimensional array. If more than one column is returned by the query, only the specified column will be returned by the function, but the entire result is cached for later use. Returns an empty array if no result is found.
<?php $wpdb->get_col( 'query', column_offset ); ?>

query :- (string) the query you wish to execute. Setting this parameter to null will return the specified column from the cached results of the previous query.

column_offset :- (integer) The desired column (0 being the first). Defaults to 0.

For this example, assume the blog is devoted to information about automobiles. Each post describes a particular car (e.g. 1969 Ford Mustang), and three Custom Fields, manufacturer, model, and year, are assigned to each post. This example will display the post titles, filtered by a particular manufacturer (Ford), and sorted by model and year.

The get_col form of the wpdb Class is used to return an array of all the post ids meeting the criteria and sorted in the correct order. Then a foreach construct is used to iterate through that array of post ids, displaying the title of each post. Note that the SQL for this example was created by Andomar.

Example

<?php
$meta_key1 = 'model';
$meta_key2 = 'year';
$meta_key3 = 'manufacturer';
$meta_key3_value = 'Ford';

$postids=$wpdb->get_col( $wpdb->prepare(
"
SELECT key3.post_id
FROM $wpdb->postmeta key3
INNER JOIN $wpdb->postmeta key1
ON key1.post_id = key3.post_id
AND key1.meta_key = %s
INNER JOIN $wpdb->postmeta key2
ON key2.post_id = key3.post_id
AND key2.meta_key = %s
WHERE key3.meta_key = %s
AND key3.meta_value = %s
ORDER BY key1.meta_value, key2.meta_value
",
$meta_key1,
$meta_key2,
$meta_key3,
$meta_key3_value
) );

if ( $postids )
{
echo "List of {$meta_key3_value}(s), sorted by {$meta_key1}, {$meta_key2}";
foreach ( $postids as $id )
{
$post = get_post( intval( $id ) );
setup_postdata( $post );
?>
<p>
<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
<?php the_title(); ?>
</a>
</p>
<?php
}
}
?>

SELECT Results

multiple row results can be pulled from the database with get_results. The function returns the entire query result as an array. Each element of this array corresponds to one row of the query result and, like get_row, can be an object, an associative array, or a numbered array. If no matching rows are found, or if there is a database error, the return value will be an empty array. If your $query string is empty, or you pass an invalid $output_type, NULL will be returned.
<?php $wpdb->get_results( 'query', output_type ); ?>

Example

Get the IDs and Titles of all the Drafts by User 5 and echo the Titles.

$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;
}

Example

Get all information on the Drafts by User 5.

<?php
$fivesdrafts = $wpdb->get_results(
"
SELECT *
FROM $wpdb->posts
WHERE post_status = 'draft'
AND post_author = 5
"
);

if ( $fivesdrafts )
{
foreach ( $fivesdrafts as $post )
{
setup_postdata( $post );
?>
<h2>
<a href="<?php the_permalink(); ?>" rel="bookmark" title="Permalink: <?php the_title(); ?>">
<?php the_title(); ?>
</a>
</h2>
<?php
}
}
else
{
?>
<h2>Not Found</h2>
<?php
}
?>

INSERT row

Insert a row into a table.,br/> <?php $wpdb->insert( $table, $data, $format ); ?>

table :- (string) The name of the table to insert data into.

data (array) :- Data to insert (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).

format :- (array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types. Possible format values: %s as string; %d as integer (whole number); and %f as float.

Example

Insert two columns in a row, the first value being a string and the second a number:

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

REPLACE row

Replace a row in a table if it exists or insert a new row in a table if the row did not already exist.

<?php $wpdb->replace( $table, $data, $format ); ?>

table
(string) The name of the table to replace data in.
data
(array) Data to replace (in column => value pairs). Both $data columns and $data values should be "raw" (neither should be SQL escaped).
format
(array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

Example

Replace a row, the first value being the row id, the second a string and the third a number:

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

UPDATE rows

Update a row in the table. Returns false if errors, or the number of rows affected if successful.

<?php $wpdb->update( $table, $data, $where, $format = null, $where_format = null ); ?>

Example

Update a row, where the ID is 1, the value in the first column is a string and the value in the second column is a number:

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

DELETE Rows

The delete function was added in WordPress 3.4.0, and can be used to delete rows from a table. The usage is very similar to update and insert. It returns the number of rows updated, or false on error.

Usage

<?php $wpdb->delete( $table, $where, $where_format = null ); ?>

Example

// Default usage.
$wpdb->delete( 'table', array( 'ID' => 1 ) );

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

Protect Queries Against SQL Injection Attacks

All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. The prepare method performs this functionality for WordPress, which supports both a sprintf()-like and vsprintf()-like syntax.

<?php $sql = $wpdb->prepare( 'query' , value_parameter[, value_parameter ... ] ); ?>

Example

$metakey = "Harriet's Adages";
$metavalue = "WordPress' database interface is like Sunday Morning: Easy.";

$wpdb->query( $wpdb->prepare(
"
INSERT INTO $wpdb->postmeta
( post_id, meta_key, meta_value )
VALUES ( %d, %s, %s )
",
10,
$metakey,
$metavalue
) );

Show and Hide SQL Errors

You can turn error echoing on and off with the show_errors and hide_errors, respectively.

<?php $wpdb->show_errors(); ?>
<?php $wpdb->hide_errors(); ?>

You can also print the error (if any) generated by the most recent query with print_error.

<?php $wpdb->print_error(); ?>

Note: If you are running WordPress Multisite, you must define the DIEONDBERROR constant for database errors to display like so:

<?php define( 'DIEONDBERROR', true ); ?>

Getting Column Information

You can retrieve information about the columns of the most recent query result with get_col_info. This can be useful when a function has returned an OBJECT whose properties you don't know. The function will output the desired information from the specified column, or an array with information on all columns from the query result if no column is specified.

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

Clearing the Cache

You can clear the SQL result cache with flush.

<?php $wpdb->flush(); ?>

This clears $wpdb->last_result, $wpdb->last_query, and $wpdb->col_info.

Itechtuto

Wordpress Tutorials

Wordpress setting

Wordpress Function

wordpress top 10 problems

Important Links

The wpdb Class

the $wpdb Object

$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

SELECT a Variable :

Example

Example

SELECT a Row

Example

SELECT a Column

Example

SELECT Results

Example

Example

INSERT row

Example

REPLACE row

Example

UPDATE rows

Example

DELETE Rows

Example

Protect Queries Against SQL Injection Attacks

Example

Show and Hide SQL Errors

Getting Column Information

Clearing the Cache

Connect Us Socially:

Quick Links