WordPress.org

Support

Support » How-To and Troubleshooting » [Resolved] Pros and cons of adding tables to the WP DB database?

[Resolved] Pros and cons of adding tables to the WP DB database?

  • flaminglogos
    Member

    @flaminglogos

    Hello all,

    I want to display content from an external database (same host and account) and this thread suggests adding my tables to the WP DB so that I can use the wpdb class to access them. Will having custom tables in the WP DB cause issues during upgrades?

    The wpdb docs say that I can instantiate my own object from the wpdb class if I give it the external database’s connection details. There’s no example given of doing that, and I’ve searched the forums with no luck. Where can I find the syntax for creating my own object?

    Thank you for any assistance you can provide,
    flaminglogos

Viewing 7 replies - 1 through 7 (of 7 total)
  • figaro
    Member

    @figaro

    Not sure about the second part of your question, but I’ve been running WP sites with additional tables for years with no problems. In fact, many plugins add additional tables…and, for what it’s worth, many times when the plugin is uninstalled the tables are left behind. Still, I’ve never encountered a problem with this.

    whooami
    Member

    @whooami

    regarding the second, something like :

    $wpdb = newwpdb(db_user, db_passwd, db_name, db_host); ...

    thats probably NOT quite exactly how it needs to go, but hopefully it will help.

    actually here, I found this,

    http://www.ilfilosofo.com/blog/2006/09/08/integrating-vanilla-forum-and-wordpress-themes/

    I was close. thats for going the other way, but its the same thing.

    flaminglogos
    Member

    @flaminglogos

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

    flaminglogos
    Member

    @flaminglogos

    @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!

    flaminglogos
    Member

    @flaminglogos

    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]

    danleroux
    Member

    @danleroux

    where do you define your new wpdb? in functions.php?

    frankpereiro
    Member

    @frankpereiro

    This is great stuff, really helpful, flaminglogos.

    I have a question, though. I have my blog hosted with dreamhost and for some reason I had to create a different database for some data I uploaded, but it assigned me a different host.

    Say that my blog is hosted at: http://example.com
    My wordpress database is at: http://mysql.example.com

    But, I would like to pull some data from my brand new database at: http://brandnewmysql.example.com

    Do you know how can I do that?

    Sorry for my english, it’s not my mother language.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘[Resolved] Pros and cons of adding tables to the WP DB database?’ is closed to new replies.