Multiple MySQL database connection

Question

User2009701734 posted

I have seen a few of these threads, but I cant seem to come up with a solid solution.

I have a MySQL server with 2 instances, one running on port 4309 and one running on 4308. I need information from a single column on one of those instances but cant find a good way to make that happen. What i have is something like this:

UserID	LogIn	LogOut
5	8:03
6	8:11
11	8:12
5		17:01
11		17:30
6		17:31

 and something like this:

UserID	UserName
5	JSmith
6	Bjones
7	MThomas
10	ACarnes
11	Plucas

And what i am hoping to end up with is something like this:

UserName	LogIn	LogOut
JSmith	8:03
Bjones	8:11
Plucas	8:12
JSmith		17:01
Plucas		17:30
Bjones		17:31

If these were 2 tables in the same DB this would have been a cakewalk, but i cant figure out how to do this in my current situation. I have tried creating 2 different DataTables with the data and merging them, but i couldnt get that to match the UserName with the ID on every record. And i dont think there is a way to specify port numbers directly in a query, so im at a loss here. 

Answer 1

User-359936451 posted

Looks pretty simple, you just need to replace UserID with UserName, right?

Don't each of your connections strings have distinct instance names?

So you return a data table from each database.

As you loop through the table with UserIds and times. Go get the UserName from the second table where the UserIDs match.

I would probably add a column to table 1 to hold the names, then just populate that cell when the UserId matches.

You could also use the Merge option to create a new third table, then not show the columns which are not needed.