Connecting to a different database

Question

In my site I'd like to pull in data from another table, without having to create an API from the other site. I have seen that you can do something like this:

$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 />';
}

However, I don't like the idea of having everything, including database credentials in one wedge.

What I've tried:

Added credentials to wp-config.php

define('DB_CAREERS_NAME', 'careers');
define('DB_CAREERS_USER', 'root');
define('DB_CAREERS_PASSWORD', '');
define('DB_CAREERS_HOST', 'localhost');
define('DB_CAREERS_CHARSET', 'utf8mb4');
define('DB_CAREERS_COLLATE', '');

Added a function to functions.php

function get_job_listings()
{
    // Create new wpdb instance
    $connection = new wpdb(DB_CAREERS_USER, DB_CAREERS_PASSWORD, DB_CAREERS_NAME, DB_CAREERS_HOST);

    // Assign a table prefix
    $connection->set_prefix('');

    // Status of jobs to show
    $status = "Open";

    // Perform query
    $query = $connection->prepare(
        "SELECT j.*
        FROM $connection->job_posts j
        WHERE j.status = '%s'
        ORDER BY j.created_at DESC",
        $status
    );

    $jobs = $connection->get_results($query);

    return $jobs;
}

But I would like to use my secondary database connection like a standard WordPress query, so that all of the query builder functions are available.

Answer 1

But I would like to use my secondary database connection like a standard WordPress query, so that all of the query builder functions are available.

Unless the other database is a WordPress database, with the exact same table structure, you can't.

The query builder functions in WordPress are designed and built only to work with WordPress' tables. Their internal logic all depends on the specific database structure of the tables that they query. WP_Query, for example, is designed only to query the wp_posts table, and it also depends on other classes that are designed to work the wp_postmeta table, wp_term_relationships table etc.

So not only would those classes and functions not work with your custom tables, they wouldn't even be useful, because they're so tightly coupled to those tables. Being able to query based on post meta, or post status isn't useful if your data isn't post meta or doesn't have a post status.

When dealing with custom tables you will need to build any functions or classes for querying them yourself. If the database is MySQL, then a wpdb() instance will give you access to helper methods like prepare(), insert(), get_results() etc., but that's as much help as WordPress is going to give you.