How do I execute my own custom, ad-hoc SQL queries? WordPress
- SOLVED
I'm familiar with PHP and MySql and SQL but I'm not so familiar with WordPress. I'm now customizing a site. I need to write a lot of SQL queries, the kind of queries that fetch data specific to the site, but I'm not clear what is the proper "WordPress way" to execute these queries, nor am I clear what I get back. I assume that WordPress makes available to me some object that allows me to make what queries I see fit? For instance, surely WordPress has all the database username and password info stored away somewhere that I can use. And by the time the template is loaded, I assume there is already a database connection established. I assume I do <strong>NOT</strong> have to write code like [[LINK href="http://us2.php.net/manual/en/function.mysql-connect.php"]]this[[/LINK]]:
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
But assume I have some query like this:
$query = "SELECT * FROM wp_posts WHERE post_modified > '2008-12-23 23:40:39' ORDER BY post_name DESC";
Or assume I'm doing a custom JOIN between wp_posts and wp_postmeta.
How do I send this query to the database? And what do I get back? I'm assuming I get back either a PHP resource that points to the returned data from the database, which is what mysql_query returns, or I get back an array, but I'd like to be sure.
Answers (6)
Michael Fields answers:
Hello,
In response to your questions:
<blockquote>For instance, surely WordPress has all the database username and password info stored away somewhere that I can use. And by the time the template is loaded, I assume there is already a database connection established.</blockquote>
You are absolutely correct. There is no need to connect to MySql.
<blockquote>But assume I have some query like this:
$query = "SELECT * FROM wp_posts WHERE post_modified > '2008-12-23 23:40:39' ORDER BY post_name DESC";</blockquote>
I suggest that you use the WordPress function [[LINK href="http://codex.wordpress.org/Template_Tags/get_posts"]]get_posts()[[/LINK]]. This function allows you to retrieve results from the database anywhere that you need to as it is not tied to [[LINK href="http://codex.wordpress.org/The_Loop"]]The Loop[[/LINK]] like the [[LINK href="http://codex.wordpress.org/Template_Tags/query_posts"]]query_posts()[[/LINK]] function is. A similar query to that in your your example is rather easy to create with query_posts():
$my_posts = get_posts( 'm=20081223&orderby=title&order=DESC' );
If it is absolutely necessary to create the exact query, then you would need to use the $wpdb object like stated above.
global $wpdb;
$my_posts = $wpdb->get_results( "SELECT * FROM wp_posts WHERE post_modified > '2008-12-23 23:40:39' ORDER BY post_name DESC" );
While the $wpdb class gives you the most control over the queries that you can execute, it is possible that you may overwrite some of WordPress' built in features creating unintended result sets. For instance, if your query does not include the post status you run the risk of showing drafts, if your query does not include the post type, you will most likely return all of them including [[LINK href="http://codex.wordpress.org/Revision_Management"]]revisions[[/LINK]], which can get ugly, fast! However, if you use get_posts(), defaults are put in place to help you out.
<blockquote>Or assume I'm doing a custom JOIN between wp_posts and wp_postmeta.</blockquote>
This actually should not ever have to happen because WordPress queries the postmeta table for you automatically. You can access the data using the [[LINK href="http://codex.wordpress.org/Function_Reference/get_post_meta"]]get_post_meta()[[/LINK]] function.
WordPress actually does a lot for you behind the scenes. To get a glimpse into what is going on, just call print '<pre>' . print_r( get_defined_vars(), true ) . '</pre>'; and see what it prints to your screen. It was rather overwhelming the first time I did this.
To get a handle on all of the queries that WordPress executes by default, you can set up a query log by following the instructions in [[LINK href=""]]this post[[/LINK]]. When viewing the logs, take into consideration that different views ( post, page, category, index, etc... ) execute different query sets.
<blockquote>what do I get back? I'm assuming I get back either a PHP resource that points to the returned data from the database, which is what mysql_query returns, or I get back an array, but I'd like to be sure.</blockquote>
Using both get_posts() and $wpdb->get_results() will return an array of objects. That being said, here is a simple example looping over a custom query which shows the title for the 5 most recently created pages:
$my_posts = get_posts( 'numberposts=5&post_type=page&orderby=date' );
if( $my_posts ) {
foreach( $my_posts as $order => $my_post ) {
setup_postdata( $my_post );
print $my_post->post_title;
}
}
John Kolbert answers:
Yes, an edit button is needed! Ignore the extra code above the opening <?php code. I had pasted it in there for my reference and neglected to remove it before hitting submit.
Brandon Dove answers:
WordPress does have a database class that allows you to interact directly with mysql. Before
you go writing crazy queries to retrieve post data, you should look at the built in functions of WordPress. query_posts() for example is an amazingly robust way to acces
post information. By passing a string or array based query, you can retrieve almost any kind if post data you'd want including custom meta. WordPress also has built on filtering and action APIs that let you modify data before it is returned to the display. I've actually got a great post (shameless self promotion) on ordering post by custom meta key/value pairs at: http://www.think-press.com/tricks/order-posts-by-a-custom-key & http://www.think-press.com/tips/order-posts-by-custom-key-revisited.
What makes WordPress so awesome is it's built-in functionality. Before you try and invent a wheel, look at the codex to see if a wheel already exists.
Ben Huson answers:
Check out the wpdb class:
http://codex.wordpress.org/Function_Reference/wpdb_Class
The source code is here if you want to look further in depth:
http://core.svn.wordpress.org/trunk/wp-includes/wp-db.php
This contains the WordPress database abstraction methods and can be accessed via the $wpdb variable in templates and plugins.
Also have a look at the data validation methods:
http://codex.wordpress.org/Data_Validation#Database
You can also hook into database requests made by the loop and filter the request:
http://codex.wordpress.org/Plugin_API/Filter_Reference
Gilbert Pellegrom answers:
To do custom queries in Wordpress you can use the <strong>$wpdb</strong> object like this:
global $wpdb;
$showposts = 5;
echo '<ul class="popular_posts">';
$result = $wpdb->get_results("SELECT comment_count,ID,post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , ". $showposts);
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$commentcount = $post->comment_count;
if ($commentcount != 0) {
echo '<li><a href="'. get_permalink($postid) .'" title="'. $title .'">'. $title .'</a></li>';
}
}
echo '</ul>';
In this example we are getting popular posts by number of comments but $wpdb can be used to get almost any data from the MySQL database in a clean, safe way.
For more info check out the [[LINK href="http://codex.wordpress.org/Function_Reference/wpdb_Class"]]Official Wordpress documentation[[/LINK]].
Jen Tidwell answers:
How about something like this code -
<?php
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'tag'
AND wpostmeta.meta_value = 'email'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'
ORDER BY wposts.post_date DESC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>