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!

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!

• php
• database
• wp-admin
• mysql
• sql

Share Improve this question edited May 23, 2017 at 12:40 CommunityBot 1 asked Aug 4, 2016 at 17:25 KyferEzKyferEz 14133 bronze badges 4

• I'm not sure you can do a JOIN between tables on 2 different databases, maybe if you mean 2 databases on the same MYSQL instance, but certainly not if they require different connection details. All of this will require raw SQL assuming it's possible at all – Tom J Nowell ♦ Commented Aug 4, 2016 at 17:36
• Yes, in the same MYSQL instance. I can give the databases the same credentials if required, but was hoping to not have to do that. Alternatively is there any way to do a join on an SQL result and a new query? – KyferEz Commented Aug 4, 2016 at 17:41
• Would a possible alternative be to move the second database into additional tables in the WP DB? – Andy Macaulay-Brook Commented Aug 4, 2016 at 18:15
• No I cannot move those tables. That database is part of a complete separate system that I am writing a plugin for wordpress to tie into and manage. – KyferEz Commented Aug 4, 2016 at 18:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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!