How to Join two tables from separate databases within WordPress

by

Using wpdb to connect to a separate database I have connected to my other database with this code

$mydb = new wpdb('username','password','database','localhost');

Now I need to do a Join of a table called ‘records’ on it’s ‘userid’ column in my 2nd database ($mydb), with the WordPress users table on the user ID (wp_users.ID).

To clarify, what I need as the end result of the join is for each line in $mydb ‘record’ to have it’s corresponding WordPress user info in the result.

How can I do this? I’m open to any method that will accomplish the goal, but would prefer to use WordPress database functions as much as possible, if possible.

If my only option is to do something like explained here how would I accomplish this using the connections made by the above code? Would WordPress simply understand the reference to the other database in the SQL?

Thanks!

1 Answer
1

So I added permissions for my $mydb user to have SELECT priviledges to my WordPress database.

I then used this code to perform the JOIN: 

$sql="SELECT * FROM `mydb`.`records` rec LEFT JOIN `wpdb`.`wp_users` usr ON rec.`mydb`=usr.`ID`";
$records = $mydb->get_results($sql, ARRAY_A);

Worked great!

Leave a Comment