WordPress Database Operations

You will need a little knowledge of PHP and MySQL to get the hang of this - but it is perhaps easier than it looks at first sight, and it’s also a good way of expanding your skills in this area…

You want to get some info from the database, and you want it more flexibly than built-in functions like, say, wp_list_pages - this is the general how-to …

WordPress has a database connection object - $wpdb - use it for anything and everything but if you’re going to use it in a function it must be declared as a global,

function myListPages(){
global $wpdb;
…..
}

Next, we need a SQL query….

$pages = $wpdb->get_results(”SELECT ID, post_title as title, guid FROM “. $table_prefix .”posts WHERE post_type=’page’ AND post_status=’publish’ ORDER BY ID ASC”);

This returns an array of objects, so to access each object we can loop through in the usual way:

foreach ($pages as $page){
// operations here
}

Inside this loop, we have as the individual object
$page->title (because we said fetch the post_title as title in the original SQL select)

$page->ID (note that case matters, $page->id isn’t the same thing at all)

$page->guid

Pages are slightly more complicated than posts in the WP database in the way the permalink is handled. Now, if you never plan to use mod_rewrite for your SEO-friendly URLS you could construct the URL immediately:

$pageUrl = get_option(’siteurl’) . ‘/?page_id=’ . $page->ID ;

or if you’re always going to use mod_rewrite

$pageUrl = $page->guid;

but hard coding is a sin and there’s a simple test:

$pageUrl = (empty($page->guid)) ? get_option(’siteurl’) . ‘/?page_id=’ . $page->ID : $page->guid;

It is also possible to do this directly in the SQL with IF ELSE’s but unless you have more 1000 pages, you won’t be speeding things up too much, and it’s clearer here to show the steps in full.

So now - put the function in functions.php of your WordPress theme:-

function myListPages(){
global $wpdb;
$pagesString = ”;

//the SQL query
$pages = $wpdb->get_results(”SELECT ID, post_title as title, guid FROM “. $table_prefix .”posts WHERE post_type=’page’ AND post_status=’publish’ ORDER BY ID ASC”);

//a loop…
foreach($pages as $page){

$pageUrl = (empty($page->guid)) ? get_option(’siteurl’) . ‘/?page_id=’ . $page->ID : $page->guid;
$pagesString .= sprintf(’<a href=”%s”>%s</a>’, $pageUrl, $page->post_title);
}
return sprintf(’<ul class=”mylistpages”>%s</ul>’, $pagesString);
}

And you use it like this, for example

echo (myListPages());

Further… WordPress makes use of the ezSQL database wrapper for MySQL, so get a copy of that and have a look in the help files for all the available possibilities. What it basically means is that you don’t have to do all that sql result, fetch, object, stuff, which here can be condensed to a single line of code.

If you cut and paste this code - make sure you return all curly quotes to straight quotes.