WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Pros and cons of adding tables to the WP DB database? (8 posts)

  1. flaminglogos
    Member
    Posted 4 years ago #

    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

  2. figaro
    Member
    Posted 4 years ago #

    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.

  3. whooami
    Member
    Posted 4 years ago #

    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.

  4. flaminglogos
    Member
    Posted 4 years ago #

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

  5. flaminglogos
    Member
    Posted 4 years ago #

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

  6. flaminglogos
    Member
    Posted 4 years ago #

    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]

  7. danleroux
    Member
    Posted 4 years ago #

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

  8. frankpereiro
    Member
    Posted 4 years ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Topic