Understand & Use $wpdb Global Object WordPress

SOME BASIC IDEA ABOUT $wpdb OBJECT AND ITS CLASS FILE

For performing database operations WordPress provides a class wpdb which is present in the file – wp-includes\wp-db.php. This class abstracts the database functions for WordPress
and most WordPress functions directly or indirectly use this class.We can create an object of this class to perform database operations but WordPress creates an object of this class
during the load of WordPress.

This object is $wpdb and is a global object. So in case we want to perform any database operation we should use this global $wpdb object and call functions on them.

By the help of $wpdb Object we can perform all types of operations like in CRUD

SYNTAX TO USE GLOBAL $wpdb OBJECT

function my_function() {
    global $wpdb;
    //………………
    //Some Operation on database using $wpdb
    //………………
}

Get Data from Database

There are a lot of functions on $wpdb to fetches values from the database. Some of these functions are specialized to get one value, one row or one column from the database table.

1. get_var()

Description

Executes a SQL query and returns the value from the SQL result.
If the SQL result contains more than one column and/or more than one row, the value in the column and row specified is returned. If $query is null, the value in the specified column and row from the previous SQL result is returned.


Parameters

$query string|null Optional

SQL query. Defaults to null, use the result from the previous query.

Default: null$x int Optional

Column of value to return. Indexed from 0. Default 0.$y int Optional

Row of value to return. Indexed from 0. Default 0.


Return

string|null Database query result (as string), or null on failure.

Example

get total posts from a user

$sum = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) from $wpdb->posts where post_status = 'publish' and user_id = %d", $user_id ) );

printf('This user has %d posts', $sum);

2. get_row()

This method is useful to get a complete row from a query. This method takes in a query and also takes a parameter to indicate in which format the output should be.

The first parameter to get_row is a query and the second parameter can be one of the following values

OBJECT ( this is the default value ) – In this case the object is an objects with the columns of the table as the members of the object.
ARRAY_A – In this case the result is returned in for of an associative array.
ARRAY_N – in this case the result is in for of an numerically indexed array.

3. get_col()

This method is useful to get one column from a query. The output of this function is an array.

4. get_results()

This method is used to get an output of a generic query which has output as multiple rows and multiple columns. The default output for this function is array of objects in
which each object represents one row of the result.

Prepared Method with $wpdb Query

Sometimes we might take some data from the user in case of a form or something else and make it as a part of the query. This can cause problems in case there is SQL
injection on the query. In SQL injection the attacker injects a query in a forms value which you will use to create a database query. This queries can be as hazardous
as dumping the complete database to deleting the database.

The wpdb prepare method should be used to protect oner against SQL injection. The prepare method is very similar to the printf method which takes in a query and then
parameter values to be substituted.

$wpdb-get_results(
    $wpdb-prepare("Query with placeholders %s %d", [value1], [value2])
);
  • %d (integer)
  • %f (float)
  • %s (string)
  • %i (identifier, e.g. table/field names)


Global $wpdb Insert, Update, Delete Methods

1. Insert Method

Syntax:

$wpdb-insert(table_name, array(values));

Example:

$wpdb-insert("[table_name]", array(
    "col1" = "val1",
    "col2" = "val2",
    "col3" = "val3",
   …
));

2. Update Method

Syntax:

$wpdb-update(table_name, array(values), array(conditions));

Example:

$wpdb-update("[table_name]", array(
    "col1" = "val1",
    "col2" = "val2",
    "col3" = "val3",
    …
), array("condition1" = "value1", "condition2" = "value2"));

3. Delete Method

Syntax:

$wpdb-delete(table_name, array(conditions));

Example:

$wpdb-delete("[table_name]", array(
    "condition1" = "value1",
    …
));

Don’t miss these tips!

Join us to get informed about our new posts and tips!

We don’t spam! Read our privacy policy for more info.

Leave a Comment

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

Scroll to Top