3

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!

  • 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 Aug 4 '16 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 Aug 4 '16 at 17:41
  • Would a possible alternative be to move the second database into additional tables in the WP DB? – Andy Macaulay-Brook Aug 4 '16 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 Aug 4 '16 at 18:39
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!

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.