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",
…
));