All those who own a website want to create a great experience for the users to ensure their website success. However, many of them are now aware of how to do it effectively by incorporating the necessary changes to deliver what they promise. In fact, by sticking to their individual point of views and likes, most of them do not even realize how bad their website experience can be.
WordPress development is common and widespread now, and many of the bigger corporations make use of this unique CMS to construct their primary sites. You may know how to use the major functions and features in WordPress for communicating with the backend database, but there are smarter and easier ways to do it directly using the options like $wpdb class.
It is built on the ezSQL class, which enables you to directly address the queries raised to any table in a standard database and also helps to handle the returned data easily. As this functionality is built into WordPress by default, you do not have to open another database connection with code duplication. There is also no need to practice any hacks to achieve this such as modifying the results sets after these are queried for.
Here in this excerpt, we will discuss the basics of how to start with $wpdb class to efficiently retrieve data from WordPress database and ways to run advanced DB queries, which can update, retrieve, or delete entries in the database. These techniques discussed here may effectively help you to remove many of the significant constraints you may confront with functions like ‘get_posts()’ or ‘wp_list_categories().’ It will let you tailor customize queries based on your practical needs. This approach will help make your website much faster and efficient by getting the exact data you need.
The $wpdb class helps to automate and modularize many of the DB tasks. If you have already explored how the MySQL and similar DBs work, you may feel at home with this class too and there are only a fewer function names you have to remember. Let’s explore the basic application of this class with the help of an example:
If the need is to query WordPress database for specific IDs and titles of 3 of the recent posts based on counts and order of the comments, then;
As you see, the query mentioned above is basic SQL wrapped in PHP. This $wpdb class is given a unique function and name, which are ideally put inside different categories. It is named as ‘get_results(),’ which will work to fetch the results and also to get them categorized into convenient objects.
You may have noted that we used $wpdb->posts instead of ‘wp_posts’ for the table name. It is actually put this way to help access the core tables in WordPress.
With this, you can get the $results object containing your data in the format given below.
Every company and website owner wants to offer a great experience to the customers. However, only a very few take the initiative to make the required changes to deliver what they promised. In fact, a lot of them do not even understand how bad the user experience may be.
If you have to extract any vital information to be availed for DBA consulting, you can easily use any of the helper functions to structure your data. There are four essential functions as discussed below.
1. get_results() Link
This is a primary function which we have already seen earlier. It is best used when you need to retrieve two-dimensional data as rows and columns (multiple). This feature can convert data into a logical array which contains different objects in each row.
2. get_row Link
If you need to find just a single information (one row) in a particular database, for example, the most popular post on a social media website with the most number of comments, you can use ‘get_row().’ This runs to pull the one-dimensional data as an object.
3. get_col Link
This one is much similar to the get_row() function, but it is not meant to grab a single row of the database. Instead, it retrieves a single column. For example, this feature is much helpful if you are trying to retrieve the IDs of only top 10 of the most commented posts. Similar to get_row(), get_col() also stores the results as a one-dimensional object.
4. get_var Link
In most of the cases, you may just require a single value from the database. For example, sometimes, you may just want to retrieve either the phone number or e-mail ID of one your customers in the database. In such cases, DBAs can ideally use get_var to get it out as the simple value. The data type of this particular value will be the same as its kind in the database, i.e., strings will remain as strings and integers will be kept as integers.
Inserting data into database link
Similar to retrieval of data, there are smart ways we can insert data as described below.
$wpdb->insert( $table, $data, $format);
This function takes three arguments. The first among these is to specify the names of the table in which the data is inserted. Next is the array which contains all the columns of data and their values interpreted as key-value pairs. Third one specifies a particular data type of your values in a specific order you have set. Find below example.
You have the option to specify the formatting part. All the values get treated by default as strings, but including it in this method is an ideal practice. The three distinct values DBA can use for strings, decimal number, and floats are respectively %s, %d, and %f.
Even though the helpers mentioned above do an excellent job for the DBAs, sometimes it may be necessary to perform more complex queries too. If you have to carry out some clauses containing complex logic, then you can make use of the ‘update()’ method. If you plan to perform actions like ‘delete row’ or so, you can use the ‘general query()’ method. For example:
Hope this helps the DBAs to perform better in the core database administration tasks. We will further discuss advanced DBA tricks and tips in the forthcoming articles.
Sujain Thomas is an expert in building plugins and apps for the CMS systems. She is also a DB expert who used to write blogs on DBA consulting to share smart tips for fresher to expert administrators.