WordPress Database Functions

If you’re going to be a WordPress plugin author, pretty soon you’ll have to get into the area of database operations, manipulating existing data or even adding your own tables – assuming you have a basic knowledge of PHP/MySQL, here’s how the fundamentals work

WordPress uses the ezSQL database wrapper – not a high-tech solution (WordPress isn’t high-tech, probably one of its strengths…), but simple, stable and useful. It wouldn’t be a bad idea here to grab a copy, read the FAQ and play around with the basic functions to understand the PHP syntax involved.

(I think) it’s safe to say that anything you can do with ezSQL, you can replicate inside the WordPress environment with $wpdb – but don’t quote me on that one….

The WordPress Database Object – $wpdb

$wpdb is the database object created by an instance of the ezSQL class – this part is already done for you.

Rule 1 for beginners – scope – remember to declare the object in any function in which you’re going to make use of $wpdb

<?php
	function my_wordpress_function(){
	global $wpdb;
		// etc....
	}
?>

Let’s jump in and try something – how to get the names and email addresses of the commenters on your blog:-

<?php
$commentEmails = $wpdb->get_results("SELECT comment_author, comment_author_email
FROM $wpdb->comments ORDER BY comment_date DESC LIMIT 20");
?>

You’ve got the raw data, as an object, you can dump it – print_r($commentEmails) – write it to a csv file and download it, whatever you need.

If you want the results as an associative array – maybe for further ordering, or some such – the method is to add ARRAY_A – see the ezSQL documentation.

<?php
$commentEmails = $wpdb->get_results("SELECT comment_author, comment_author_email
FROM $wpdb->comments ORDER BY comment_date DESC LIMIT 20", ARRAY_A);
?>

Most of this is straightforward MySQL syntax – the one significant point involves the specifying of the table, here, comments – there’s a database prefix involved, which will vary from one installation to another.

The current WordPress method is to use an associated variable of the database object – $wpdb->comments, and you’ll be selecting from the correct table. Again, this has already been declared for you. Similarly, $wpdb->posts will get you the posts table to work with, $wpdb->users, $wpdb->options etc…

It’s a better way than using $table_prefix from wp-config.php and adding this into the sql string. The slight complication comes when you start adding and using your own custom tables, not already a part of the $wpdb object – and that’s for later…

Meanwhile, try a few SELECTs and then maybe a few UPDATEs/INSERTs to see what’s going on.