Interaction with the database is one of the key aspects of the work of almost any modern site. Popular engines in some cases have built-in APIs for working with tables, and WordPress is no exception. This feature allows developers to significantly expand the functionality of the site, and use tables in the database more flexibly.
Before making queries to the database, you must first connect to it. In the case of using WordPress, the $ wpdb variable becomes available to us - it already contains a class object, the methods of which allow us to work with tables in the database.
Before using the class, you need to globalize the $wpdb variable.
global $wpdb;
Using the query () method
To execute a specific query, we can use the query() method:
$wpdb->query("DELETE FROM $wpdb->posts WHERE post_type = 'draft'"); // we overwrite all draftsA fairly simple example that contains all the parameters for its execution. This method is quite safe, right up to the moment when it comes to passing any parameters in the request. Here it becomes necessary to screen variables from SQL injection in order to secure the data and unauthorized manipulations. For these purposes, it is convenient to use the prepare() method in conjunction with the query() method.
Using the prepare() method
In this example, we will add a name for the user whose ID is 10. This is an example of data escaping. The peculiarity of this example is that each variable is passed separated by commas, in the appropriate order as indicated in the request.
$id = 10;
$key = "first_name";
$value = "John";
// %s reports that a string is expected,%d is a number
$wpdb->query(
$wpdb->prepare(
"INSERT INTO $wpdb->usermeta ( user_id, meta_key, meta_value ) VALUES ( %d, %s, %s )",
$id, $key, $value
));This is a fairly simple example, but in practice it is necessary to transfer a large number of fields; for these purposes it is convenient to use arrays.
$arIns = [];
$arIns[] = 10;
$arIns[] = "first_name";
$arIns[] = "John";
$wpdb->query(
$wpdb->prepare(
"INSERT INTO $wpdb->usermeta ( user_id, meta_key, meta_value ) VALUES ( %d, %s, %s )",
$arIns
));To refer to standard tables, there are special variables in the class that contain their names.
$wpdb->commentmeta
$wpdb->comments
$wpdb->links
$wpdb->options
$wpdb->postmeta
$wpdb->posts
$wpdb->terms
$wpdb->term_relationships
$wpdb->term_taxonomy
$wpdb->usermeta
$wpdb->usersFor example, let's remove the first record from the wp_post table using this variable.
$wpdb->query("DELETE FROM $wpdb->posts WHERE id = '1'")The table prefix and its name are substituted from a variable, which is great, of course, but there are times when you need to refer to tables that are not available by default in WordPress. To get the table prefix from the configuration file, you can use the value of the $ wpdb-> prefix variable. Thus, the table prefix and its name are indicated separately.
$wpdb->query("DELETE FROM ".$wpdb->prefix."posts WHERE id='1'");Fetching data from tables in WordPress
In the examples, it was clearly shown how you can delete records in the table. It is not uncommon to make a selection of data from tables using the SELECT statement. There is a get_result() method to get the result of a query.
Get_result () method parameters
$wpdb->get_results('query', $result_type);The first parameter is the SELECT query string itself, the second parameter $ result_type is optional, it determines what data type the method will return as a result.
- OBJECT - As an object (default);
- OBJECT_K - Associative array, values from the first column will be used as keys, duplicates will be ignored;
- ARRAY_A - An array with a numeric index, consisting of associative arrays, which, in turn, will use the column names as indexes;
- ARRAY_N - Array with a numeric index.
As an example of use, let's select the titles of the 10 most recently published pages from the database in descending order by publication date.
$posts = $wpdb->get_results(
"SELECT post_title FROM $wpdb->posts
WHERE post_status = 'publish' AND post_type = 'page'
ORDER BY post_date DESC LIMIT 10");
if ($posts){
foreach ($posts as $page){
echo $page->post_title;
}
}As you can see, the second parameter was not specified, which means that the selection result will be returned as an object. You don't always have to select records as a list. Using this method in case you need the value of one field is not always convenient. There is another get_var() method for these purposes .
The get_var () method allows you to get a single value as a result. It can be the value of one field, or the value of the sum or the number of records.
Get_var() method parameters
$wpdb->get_var('query',$col_offset ,$row_offset);$col_offset- column number (default 0);$ow_offset- row number (default 0).
Now let's take a look at some examples. As a first example, let's get the registration date of a user with an ID of 10.
$date = $wpdb->get_var("SELECT user_registered FROM $wpdb->users WHERE ID=10");
echo $date;If a user with this ID exists, then as a result we will receive the user's registration date. Now let's try to count the number of active pages on the site.
$page_count = $wpdb->get_var(
"SELECT COUNT(*) FROM $wpdb->posts
WHERE post_status = 'publish' AND post_type = 'page';");
echo "Total pages on the site {$page_count}.";As a result, we will receive a message with the number of pages that have the "Published" status. This example returns one value as the result. Sometimes you need to get the value of one row that matches the selection condition. The get_row() method is well suited for this purpose.
Get_row() method parameters
$wpdb->get_row('query', $output_type, $row_offset);As the first parameter, as in the previous examples, a query string is passed. The second parameter is $output_type, the type of data output, can be values:
- OBJECT - As an object (default);
- ARRAY_A - As an associative array;
- ARRAY_N - Array with a numeric index.
The ordinal number of the required row is passed as the third parameter $ row_offset, the default value is 0.
For example, let's get the title of the most recent post.
$post = $wpdb->get_row(
"SELECT post_title, post_content FROM $wpdb->posts
WHERE post_status = 'publish'
ORDER BY post_date DESC LIMIT 0,1"
);
echo $post->title;As a result of executing this request, one record will be received, the date of the post's publication will be used as the sort field. We can also get a record from one column using the get_col() method .
Get_col () method parameters
$wpdb->get_col('query', $col_offset);Similarly, a query string is passed as the first parameter. The number of the required column is passed as the second parameter of $col_offset, the default is 0. Let's look at an example with this method.
Let's get the title of the last post; as a result, the method should return a one-dimensional object.
$posts = $wpdb->get_col(
"SELECT post_title FROM $wpdb->posts
WHERE post_status = 'publish' AND post_type='page' ORDER BY post_date DESC"
);
echo $posts[0];We display the value by referring to the key of the one-dimensional object.
Writing data (INSERT) to WordPress tables
Now it is worth considering the possibility of adding a record to the database using the insert () method .
Insert() Method Parameters
$wpdb->insert($table, $data, $format)As the first parameter $table - the name of the table to which the recording will be performed is passed. The second parameter is $ data, this is an associative array, in the form of a key-value, contains data to be written to the table. The third parameter, $format, contains the formats of the values that are passed in the second parameter. Now, for clarity, let's look at an example of adding a record to a table.
$wpdb->insert(
$wpdb->prefix.'usermeta', // name of the table
array( // 'key' => 'value'
'user_id' => 1,
'meta_key' => 'first_name',
'meta_value' => 'John'
),
array(
'%d', // %d - numerical value
'%s', // %s - string value
'%s'
)
);Thus, a record with the corresponding field values from the array will be added to the wp_usermeta table.
Updating (UPDATE) records in WordPress tables
Finally, let's look at a method to update values in a table. The update () method is called and receives five parameters as input.
Update() method parameters
$wpdb->update($table, $data, $where, $format, $where_format);The name of the table is passed as the first parameter - $table. An array with new values for the fields that will be updated is passed as the second parameter. The third parameter ($ where) is the filter according to which the fields will be updated. The fourth parameter ($format) is the data format of the fields that will be updated. The fourth parameter ($ where_format) is the format of the fields for the selection condition. The description of the parameters turned out to be a little confusing, so let's look at an example of updating a record in the database.
$wpdb->update(
$wpdb->prefix . 'usermeta', // table name
array('meta_value' => 'Smith'), // change the value
array( // according to the filter
'user_id' => 1,
'meta_key' => 'first_name'
),
array( '%s' ), // the format of the field being updated
array( // filter fields format
'%d',
'%s'
)
);As a result of the method, the value of the meta_value field in the wp_usermeta table will be updated, the record corresponding to the parameters of the fields in the filter. This, perhaps, is all. We have considered all the basic methods that are sufficient in most cases to work with a database.
