Joining multiple DBs in mySql, user credential issue

Question

I am joining two DB together to complete a query.

$strSQL = "SELECT tbl.SecurityKey,
                  tbl.method,
                  tbl.amount,
                  tbl.TxType,
           CONCAT(u.firstname,' ',u.lastname) AS fullname 
           FROM $CFG->paymentdbname.tblPayment tbl 
           JOIN $CFG->dbname.vle5_user u 
           ON u.id = " . $uid . " 
           WHERE VendorTxCode='" . mysql_real_escape_string($strVendorTxCode) . "' and VPSTxId='" . mysql_real_escape_string($strVPSTxId) . "'";

The issue that I am having is that each DB has its own user and so when I try to run the query with either user it fails as the user dosen't have the right privileges.

I had a good hunt around in the Plesk control panel as I though the answer might be to have a DB user with access to both, but this don't seem to be an option.

Not sure if you've looked at it, but http://stackoverflow.com/questions/1565993/oracle-database-link-mysql-equivalent may give some direction to you... — Zack Macomber, Mar 14 '12 at 12:48

score 3 · Accepted Answer · answered Mar 14 '12 at 12:49

3

You can deal with multiple DB in single sql query only if user connected by mysql_connect is permitted to access both DB.

Go to your control panel and add all privilege to user X for DB A and B. after this you can use two different DB in single Sql

answered Mar 14 '12 at 12:49

Nimit Dudani

4,840
3
31
46

2

+1; @TheAlbear I don't know about Plesk, but you may directly use the MySQL functionality: http://kb.mediatemple.net/questions/788/HOWTO%3A+GRANT+privileges+in+MySQL – feeela Mar 14 '12 at 12:53
Thanks nDudani, yea its was a permission error, in the end needed to ssh in and then just followed this http://stackoverflow.com/questions/7414575/mysql-grant-for-more-than-one-database as that functionality is not supported via Plesk. – TheAlbear Mar 14 '12 at 14:44

Joining multiple DBs in mySql, user credential issue

1 Answers1