-1

I have created a MySQL join between two tables in two different databases on our account. The query works fine on my local server, but the host server doesn't like it. How can I make a connection to the second database, so the query works? Below is the error message with my code below that.

Query failed SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'someone'@'localhost' for table 'products'

Query

try {
    $getPurch_info = $conn->prepare("
         SELECT M.carrier, M.order_number, M.serial_number, M.tracking, 
                M.carrier, M_2.model FROM  myDB_beta.purchases AS M
         JOIN myDB2.products AS M_2 ON 
             M.product_ID = M_2.product_ID WHERE M.product_ID = ?");
    $getPurch_info->execute(array($product_ID));
} catch (Exception $ex) {
    die();
}
Shadow
  • 33,525
  • 10
  • 51
  • 64
RWRuth
  • 9
  • 2

1 Answers1

1

I think on your local server you have both databases on a single machine and you use "root" as user which has access to both of the databases.

On the live server your problem should be a task for you dev-ops / sysadmin. He must configure a database user which has access to both databases.

You should do the PDO connect like this:

$conn = new PDO('mysql:host=your_host', your_user, your_pass);

And after in the SQL you must name the database directly, not with aliasses.

Marco
  • 3,470
  • 4
  • 23
  • 35
  • Marco, yes I checked with the sysadmin and I now have access to both dbs but the query still doesn't work. The issue is how to write the syntax to connect with both databases. In separate queries the var $conn has the connection to the first db and $conn2 has the connection to the second. But how do I write the syntax using $conn2 in the second half of the join to connect to the second db? I really appreciate any help you can give!!! – RWRuth Apr 27 '22 at 22:32