I’ve gotten the $wpdb class to work for both of the scenarios above, so I’m sharing my code here for others who made need to do this.
Access new tables in the WP DB
For this scenario I added a new table city to the WP DB with the columns: city.cID, city.name, city.stadium. Use the $wpdb class to access this table and pass the access credentials to the class using the constants already in use in the application (DB_USER, etc.). The $wpdbtest_maindb->show_errors(); method makes any errors visible, which helped with debugging.
$wpdbtest_maindb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
$wpdbtest_maindb->show_errors();
$mycities = $wpdbtest_maindb->get_results("SELECT name
FROM city
WHERE city.stadium = 1");
foreach ($mycities as $mycity) {
echo $mycity->name . '<br />';
}
Access tables in a different DB
For this case I added a new database (same server, same account) called myaccount_cities with a table called city with the columns: city.cID, city.name, city.stadium. The database username is myaccount_cityuser with a password of mypassword. Use the $wpdb class to access this table and pass the access credentials to the class using the real values for the database. The $wpdbtest_maindb->show_errors(); method makes any errors visible, which helped with debugging.
$wpdbtest_otherdb = new wpdb('myaccount_cityuser', 'mypassword', 'myaccount_cities', 'localhost');
$wpdbtest_otherdb->show_errors();
$mycities = $wpdbtest_otherdb->get_results("SELECT name
FROM city
WHERE city.stadium = 1");
foreach ($mycities as $mycity) {
echo $mycity->name . '<br />';
}
I also got this to work when going after the data in the WP DB (information about your posts, pages, etc.). This is interesting but probably unneeded because you can just use Template Tags to achieve the same thing without the complexity of the SQL syntax. Here’s how to do it with $wpdb.
Access WP tables in the WP DB
This code will select all posts with a published status of “draft” and print rows of the post ID, the post title, and the post type. Note the use of the variable $wpdb->posts in the FROM clause, which is already set up in WP so that you don’t have to know that the real table is wp_posts (see list of the class variables and table name variables at the bottom of the $wpdb class reference document.
$mytitles = $wpdb->get_results("SELECT post_title, post_type
FROM $wpdb->posts
WHERE post_status = 'draft'");
foreach ($mytitles as $mytitle) {
echo $mytitle->post_title . ' ' . $mytitle->post_type . '<br />';
}
Thanks for all the help getting this to work!
[edit put . in echo MEH]
