Recently I’ve been adapting a plug-in to suit my needs and for the first time, trying to write something that interacts with the wordpress database, step up wpdb class!
The WPDB Class – Make it Global
To use the wpdb class we need to access a global variable $wpdb which is simple enough, but what you’ve got to remember is that it needs to be globalized before you use it in your custom function: -
Once that’s in place you are free to use it to perform SQL queries and function on your database.
The first thing I needed to do was to query the “commentmeta” table in the database, and save the results for later use: -
$comment_meta = $wpdb->get_results( "SELECT mydata FROM mytable WHERE mycondition = 'myvalue' ");
The above code uses ‘get_results’ to query the database and loads in to the the variable $comment_meta so it can be used in a ‘foreach’ loop later. Now I wanted to grab the meta_key, meta_value from table ‘commentmeta’ that was named ‘wp_1_commentmeta’. I could have put that value into the query but if I want to use the function on a different site on a wordpress network I’m not going to know the database prefix so I can use $wpdb to replace it: -
$comment_meta = $wpdb->get_results( "SELECT meta_key, meta_value FROM wpdb->commentmeta ");
That way I can use the query in a function on any wordpress blog and it will always knows what table to look at.
It’s an Object by default
One last thing to remember is that the results are an object so you can access the like this: -
foreach ( $comment_meta as $comment_meta_data )
echo '< br />'
And that’s what I wanted but if you want something different like an array then you can use OBJECT_K for an associative array of row objects, using first column’s values as keys, ARRAY_A for a numerically indexed array of associative arrays, using column names as keys or ARRAY_N for a numerically indexed array of numerically indexed arrays like this: -
$comment_meta = $wpdb->get_results( "SELECT meta_key, meta_value FROM wpdb->commentmeta", ARRAY_N );
for more information on the wpdb class check out the wordpress codex