0

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.

  • 1
    Is the second database a WordPress database? WP_Query, get_posts etc. are going to be useless unless you're querying a wp_posts table. That's all they're built to do. You might not be building a REST API to access the database externally, but you're still going to need to write a PHP API for doing anything with it. – Jacob Peattie Mar 5 '19 at 12:20
  • 2
    Possible duplicate of How can I connect to another WP database and use WP_Query? – Fabrizio Mele Mar 5 '19 at 12:21
  • Judging by the query in their question, the other database is not a WordPress database, so the question isn't quite the same, and the accepted solution wouldn't work. – Jacob Peattie Mar 5 '19 at 12:23
  • I'll update my question – Jesse Orange Mar 5 '19 at 12:25
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.

| improve this answer | |
  • So essentially I can just go forward as if I were dealing with vanilla PHP? Great, I'll stop getting bogged down in functions an just write it. – Jesse Orange Mar 5 '19 at 13:10
  • Basically, although you can still use the wpdb() methods, which will make things a bit easier. I'm actually working on a project with a second database right now, but it's SQL Server, so I can't even use wpdb(), and I'm basically in vanilla PHP world implementing everything myself. You could always pull in a library of some sort though, I suppose. – Jacob Peattie Mar 5 '19 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.