Dec 112013
 
Article PHP

The base functionality of the WordPress CMS (Content Management System) can be expanded developing themes and plugins that get “hooked” at different points of the process performed by WordPress to serve incoming requests.

The functionality of a given plugin may require adding tables to the database, or performing queries to the existing tables, in a non-standard way that cannot be carried out with the functions available in the base WordPress distribution.

WordPress provides a “wpdb” class that can be used to execute any kind of query against the database, to retrieve or modify data stored in it.

This post explains how to work with the $wpdb object.

1. Performing a simple query to retrieve data from the database

The method get_results() in the $wpdb object is the simplest way to retrieve information from a table. For instance, to retrieve the records from a table “mytable” in the WP DB:

By default, get_results() returns a numerically indexed array of objects. The objects returned have attributes names after the field names specified in the SELECT statement. Optionally, get_results() accepts a second argument to request a different format of the data returned. The predefined constants that can be used for this second argument are:

  • OBJECT (default if not specified) – result will be output as a numerically indexed array of row objects.
  • OBJECT_K – result will be output as an associative array of row objects, using first column’s values as keys (duplicates will be discarded).
  • ARRAY_A – result will be output as an numerically indexed array of associative arrays, using column names as keys.
  • ARRAY_N – result will be output as a numerically indexed array of numerically indexed arrays.

Example:

2. Inserting a new record in a table

A new record can be inserted in a table with a call to the insert() method in the $wpdb object:

Where the arguments passed to insert() are:

  • $table (string) – The name of the table where the record is to be inserted
  • data (array) – Associative array with the data to be inserted (in column => value pairs).
  • format (array|string) (optional) – Array of format specifiers for each of the values in the $data array. If the argument is a single string, the format specifier in it will be used for all the values in the $data array. If omitted, all values will be treated as strings, unless something else has been specified in wpdb::$field_types ($field_types is an instance field of the $wpdb object, initialized during WP load. It is an array of default format specifiers, where the keys are column names, and the values are the format specifiers)

The format specifiers that can be used in the $format argument are: %s (string); %d (integer); %f (float).

If there is an AUTO_INCREMENT field in the table, it is possible to access the value assigned to it using the expression:

insert() returns “false” if the record could not be inserted for some reason.

Example:

Insert a record of two columns, where the value of the first column is a number, and the value of the second column is a string.

3. replace() method

The replace() method in the $wpdb object is similar to the insert() method. The main difference is that replace() deletes from the table any previously existing record that has the same values as the record to be inserted, for one or more of the unique indexes defined for the table.

replace() returns the number of affected records, or else the value “false” if the record could not be inserted.

Thus, if the returned value is 1, it means that the new record has been successfully inserted, and no other records where deleted.

4. update() method

There is also an update() method in the $wpdb object to perform the update of the records in a table. The method is called as:

The arguments passed to the update() method are:

  • table (string) –  The name of the table where the record is to be inserted
  • data (array) – Associative array with the data to be inserted (in column => value pairs). As happens in the insert() method, the special characters that could be present in the values should not be escaped. This means that if data to be inserted comes from a GET or POST, it might be necessary to use the stripslashes() function to avoid that escape characters be inserted in the database.
  • where (array) –  An associative array of WHERE clauses (in column => value pairs). Clauses in this array are joined with the AND operator.
  • format (array|string) (optional) – Array of format specifiers for each of the values in the $data array. If the argument is a single string, the format specifier in it will be used for all the values in the $data array. If omitted, all values will be treated as strings, unless something else has been specified in wpdb::$field_types ($field_types is an instance field of the $wpdb object, initialized during WP load. It is an array of default format specifiers, where the keys are column names, and the values are the format specifiers).
  • where_format (array|string) (optional) An array of format specifiers for each of the values in the $where array. If a single string is passed, the format specifier in it will be used for all the values in the $where array.

Example:

Update a record in table “table”, where the value of the ID column is 1. The value of the first column to be updated is numeric, and the value of the second column is a string:

5. Delete records from a table

The delete() method in the $wdb object deletes records from a table. This method is called in a very similar way to the update() method:

Examples:

6. Executing other types of SQL sentences

Any other type of SQL sentences can be executed using the generic method query():

7. Get protected against SQL injection attacks with prepare()

There is also a method named prepare() in the $wpdb object. This method preprocesses the sentences to be executed, escaping any special character that could be present in the values to be inserted, such as single quotes or underscores.

prepare() retuns a string with the preprocessed SQL sentence. This string can be passed as argument to any of the other methods (insert, update, delete,…) in the $wpdb object.

NOTE: The functionality of this method has nothing to do with the concept of  “prepared statements” used in many database access libraries available for most programming languages (including mysqli for PHP)

 

Using prepare(), the application is protected against “SQL injection” type of attacks, where the values passed are designed to modify the statement, allowing unauthorized accesses, etc

prepare() receives as first argument a template of the sentence to be generated, whith placeholders for the values to be used, in the sprintf style: “%s”, %d” y “%f”. The following arguments are the variables holding the values to be used in executing the sentence:

Example:

 Posted by at 4:08 pm

 Leave a Reply

(required)

(required)