flaminglogos

I’ve access data from a separate database using the $wpdb class that is included in the WP code. It takes the hassle out of manually setting up the database connections.

I’ve posted the details on my original thread.

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]

@henkholland: That’s interesting. I hadn’t thought about what the menu will look like with all of these pages in it. I’ll give this a try, as well.

@whooami: That’s exactly what I’m looking for. I’d like to keep them separate from the WP DB if I can, just to keep things clean and neat.
Thanks!

@figaro: Thanks! Good to know that keeping them separate is not required, especially if I can’t get the second DB connected.

That’s just what I need.
Thanks!

Thanks!
I will give this a try.

Where in the Docs would I find out more about this type of solution (or what do you call the use of these variables)?

Can this code be placed in the content section of my page (through the wp-admin interface, or would I add it one of my .PHP files?