WordPress provides well-optimized built-in methods for accessing the existing database tables. Therefore, accessing these tables becomes straightforward. Let's see how basic CRUD (Create, Read, Update, and Delete) operations are executed on existing tables.

Basically, there are no built-in methods for accessing custom tables using direct functions. So, it's a must to use the wpdb class for handling custom tables. Let's take a look at some of the functions provided by the wpdb class:

The complete list of the wpdb class functions can be accessed at http://codex.wordpress.org/Class_Reference/wpdb. When executing these functions, we have to make sure that we include the necessary filtering and validations as these are not built to directly work with the existing tables. For example, consider the following query for proper usage of these functions with the necessary filtering:

$wpdb->query(
  $wpdb->prepare("SELECT FROM $wpdb->postmetaWHERE post_id = %d AND meta_key = %s",1, 'book_title'
  )
);

Here, we are filtering the user input values through the prepare function for illegal operations and illegal characters. Similarly, you have to use functions such as escape and escape_by_ref for securing direct SQL queries.

Data validation is an important aspect of keeping the consistency of the database. WordPress offers the prepare function for formatting the SQL queries from possible threats. Usually, developers use the prepare function with direct queries including variables instead of using placeholders and value parameters. It's a must to use placeholders and value parameters to get the intended outcome of the prepare function. Therefore, WordPress Version 3.5 and onwards enforce a minimum of two arguments to prevent developers from misusing the prepare function.

WordPress posts act as the main module in web application development as well as content management systems. Therefore, WordPress comes with a separate class named WP_Query for interacting with the posts and pages. You can look into more details about the use of WP_Query at http://codex.wordpress.org/Class_Reference/WP_Query.

Up until now, we had a look at procedural database access functions using global objects. Web application developers are much more familiar with object-oriented coding. The WP_Query class is a good choice for such developers in querying the database. Let's find out the default usage of WP_Query using the following code:

$args = array(
  'post_type' => 'projects',
  'meta_query' => array(
    array(
    'language' => '',
    'value' => 'PHP'
    )
  )
);

$query = new WP_Query($args);

First, we need to add all the filtering conditions to an array. The WP_Query class allows us to include conditions on multiple tables, such as categories, tags, and postmeta. This technique allows us to create highly complex queries without worrying about the SQL code. The advantage of WP_Query comes with its ability to create sub classes to cater to project-specific requirements. In the next section, we are going learn how to extend the WP_Query class to create custom database access interfaces.

The default WP_Query class works similarly for all types of custom post types. In web applications, we can have different custom post types with different meanings. For example, developers can create services inside our portfolio application. Each service will have a price and process associated with it. There is no point retrieving these services without those additional details. Now, let's look at the default way of retrieving services with WP_Query using the following code:

$args = array(
  'post_type' => 'services',
  'meta_query' => array(
    array(
    'key' => 'price'
    ),
    array(
      'key' => 'process'
    )
  )
);

$query = new WP_Query( $args );

This query works perfectly in retrieving services from the database. However, each time we have to pass the price and process keys in order to join them while retrieving services. Since this is a services-specific requirement, we can create a custom class to extend WP_Query and avoid repetitive argument passing as it's common to all the services-related queries. Let's implement the extended WP_Query class as follows:

class WPWA_Services_Query extends WP_Query {

  function __construct( $args = array() ) {

  $args = wp_parse_args( $args, array(
    'post_type' => 'services',
    'meta_query' => array(
      array(
        'key' => 'price'
      ),
      array(
        'key' => 'process'
      )
    )
  ) );
  parent::__construct( $args );
  }
}

Now, all the common conditions are abstracted inside the WPWA_Services_Query class. So, we don't have to pass the conditions every time we want services. The preceding example illustrates the basic form of object inheritance. Additionally, we can use post filters to combine custom tables with services. Now, we can access services using the following code without passing any arguments:

$query = new WPWA_Services_Query();

The WP_Query class is going to play a vital part in our portfolio application development. In the upcoming chapters, we are going to explore how it can be extended in several ways using advanced post filters provided by WordPress. Until then, you can check out the available post filters at http://codex.wordpress.org/Plugin_API/Filter_Reference#WP_Query_Filters.