Database Class in WordPress

WordPress CMS is very organized in core so it has features an object class with method functions for working with the database directly.

For the database class wpdb is used and this class is located in the wp-includes/wp-db.php. When you want to querying or fetch the data from database then you should use the wpdb class. This class main purpose is that this class is allowing to WordPress to execute your queries in the safest way possible.

Simple Database Queries in WordPress by Web Design Agency London

In WordPress when you work with queries and use the wpdb class then you must first define the $wpdb as a global variable for use. Just write this line of of code directly preceding any $wpdb function call:

global $wpdb;

The prepare() function is one of the most important functions in the wpdb class. The purpose of this function is use for escaping the variables which passed to your SQL queries. For hacking the site SQL injection is mostly use so this is preventing SQL injection attacks on your website by hackers. Every query in WordPress before being executed or run it should be passed through the prepare function. Below is an example of this:

<?php

$field_key = “address”;

$field_value = “57 Elm St”;

$wpdb->query( $wpdb->prepare( “INSERT INTO $wpdb->my_new_table( id, field_key, field_value ) VALUES ( %d, %s, %s )”, 7,$field_key, $field_value ) );

?>

The above example inserts data into a non-default, in the custom table of WordPress which you have created previously. Remember that whenever use prepare() function, then you should to replace any variables in your query with %d for integers and %s for strings. Use same order when use the prepare() function of the list variable. Above example, %d represents 7, %s represents $field_key, and the second %s represents $field_value. Have you notice that in the above example $wpdb->my_custom_table uses to reference the table in WordPress. In this query if we translate wp_ is the table prefix. Here you learn another thing that use of prefix in WordPress table so here is the proper way to determine the correct table prefix when working with tables in the WordPress database.

NOTE remember that when you install the WordPress CMS then it has set a custom database table prefix. Wp_ is the By default prefix in wordpress, so many developer choose to change this prefix for security Purposes and web design agency London use this practice. The table prefix is for any WordPress installation Use $wpdb-> is the correct way to determine.

 

For execute the simple query wpdb query() method is used. For SELECT and DELETE statements this function is primarily is used. It can execute any SQL statement against the database and not only for SQL SELECT queries. Here is a basic example of query function:

<?php

$wpdb->query( $wpdb->prepare( ” DELETE FROM $wpdb->my_new_table WHERE id = ‘7’ AND field_key = ‘address’ ” ) );

?>

In the above example you can see that you execute your query using the wpdb class query() function to delete the field “address” with an ID of 7. This query() function is also allows you to execute any SQL query on the WordPress database, More appropriate for SELECT queries are other database object class functions. For retrieve a single variable from database use the get_var() function.

<?php

global $wpdb;

$total_comment_count = $wpdb->get_var( $wpdb->prepare( “SELECT COUNT(*) FROM $wpdb->comments;” ) );

echo ‘<p>Total comments: ‘ . $total_comment_count . ‘</p>’;

?>

Here is another example retrieves or fetch a count of all comments and displays the total number. There is only one scalar variable is returned, the query is cached of the entire result. So this is the best practice That to try and limit the result set returned from your queries and use WHERE clause to only get the Records which you exactly needed. You display the total count of comments but all comment record rows are returned.