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.
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.
Share Improve this question asked Mar 5, 2019 at 12:12 Jesse OrangeJesse Orange 1131 silver badge7 bronze badges 4 |1 Answer
Reset to default 1But 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.
WP_Query,get_postsetc. are going to be useless unless you're querying awp_poststable. 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 Commented Mar 5, 2019 at 12:20