In Part One we discussed adding actions and filters to WordPress as an initial step in writing a WordPress plugin. These hooks into the WordPress API allow your code to be called by WordPress as certain events and code are generated.
However, your plugin may need to access the WordPress database to use WordPress’ data for outputting various pieces of information. WordPress’ $wpdb object is perfect for the job and easy to use.
The WordPress database interaction object is created in the WordPress code for you, so all you’ll need to do is import it into your functions that are called by WordPress like so:
function suckMyDatabasesBrainsOut(){
global $wpdb, $tableposts;
// other code here to make the world a better place
}
You’ll also need to import the database name that you’ll need access to because WordPress 1.2 uses variables to hold the database names. The database names are dynamic because you can have multiple WordPress installations and each installation has a different prefix. The list of default tables are found in the wp-settings.php file:
- $tableposts
- $tableusers
- $tablesettings
- $tablecategories
- $tablepost2cat
- $tablecomments
- $tablelinks
- $tablelinkcategories
- $tableoptions
- $tableoptiontypes
- $tableoptionvalues
- $tableoptiongroups
- $tableoptiongroup_options
- $tablepostmeta
You’ll have to determine what table has the data you’re after. I recommend using PHPMyAdmin to look at the information in your database.
OK, so you’ve got access in your plugin, but now you need data, because access is [only] half the battle (to mutilate G.I. Joe). You can use the following functions to get data from WordPress and to control how the WordPress Database object controls output [found in the wp-includes/wp-db.php file]:
$wpdb->escape($string)- Returns a string escaped for safety in MySQL queries.
show_errors()- Turns on WordPress returning MySQL error reporting when errors are encountered.
$wpdb->hide_errors()- Turns off WordPress’ returning MySQL errors when they are encountered.
$wpdb->flush()- Sets cached query results to null
$wpdb->query($SQLQuery)- Used to execute DELETE or UPDATE queries
$wpdb->get_var($SQLQuery, $x, $y)- Use this to get one value from the database, where $x and $y are specific data values. You can call this function with just the query if one result is expected from the query. I have found no documentation regarding the second and third parameter and no where are they used in the WordPress code for version 1.2.
$wpdb->get_row($SQLQuery)- Returns one database result row from a query.
$wpdb->get_col($SQLQuery)- Returns the values from a table column for use as a PHP array.
$wpdb->get_results($SQLQuery, $objectForValueToBeReturnedIn)- Returns the SQL query results in an object that can be accessed by the result column names. If you don’t care what object is used for access you can skip the second parameter.
$wpdb->get_col_info($informationType, $columnOffset)- Returns column meta information for the last column requested. $informationType defaults to 'name' and $columnOffset defaults to -1.
Remember that the data returned by the $wpdb->get_results($SQLQuery)
Notes:It should be noted that WordPress 1.3 changes database access by only requiring you to import the $wpdb global. Currently no information has been published that explains how to add external databases (for example the StatTraq statistics database).