How to Construct a Cross Database Query in PHP

How do I construct a cross database query in PHP?

You will need your databases to run on the same host.

If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.

$db = mysql_connect($hots, $user, $password);
mysql_select_db('my_most_used_db', $db);

$q = mysql_query("
    SELECT *
    FROM   table_on_default_db a, `another_db`.`table_on_another_db` b
    WHERE  a.id = b.fk_id
");

If your databases run on a different host, you won't be able to join directly. But you can then make 2 queries.

$db1 = mysql_connect($host1, $user1, $password1);
$db2 = mysql_connect($host2, $user2, $password2);

$q1 = mysql_query("
    SELECT id
    FROM   table
    WHERE  [..your criteria for db1 here..]
", $db1);
$tmp = array();
while($val = mysql_fetch_array($q1))
    $tmp[] = $val['id'];

$q2 = mysql_query("
    SELECT *
    FROM   table2
    WHERE  fk_id in (".implode(', ', $tmp).")
", $db2);

How do I construct a cross database query in MySQL?

I've got two databases on the same server. ...How do I construct a cross database query in MySQL?

You access other databases on the same MySQL instance by prefixing the table with the appropriate database name. IE:

SELECT *
  FROM this_database.table_1 t1
  JOIN that_database.table_2 t2 ON t2.column = t1.column

Keep in mind

A query executes with the credentials of the authentication used to set up the
connection. If you want to query two tables simultaneously across two (or more)
databases, the user used to run the query will need SELECT access to all
databases involved.

Reference:

• Identity Qualifiers

PHP Mysql joins across databases

EDIT: So what becomes of these lines?:

$conn =
mysql_connect('localhost','username','password');
@mysql_select_db('database_name',$conn)

The mysql_select_db is optional. As it just selects the active db to use.
http://us3.php.net/manual/en/function.mysql-select-db.php

And yes.. this is an old app, hence
the mysql instead of mysqli. Suppose I
could convert it.

IMHO I don't think using MYSQL has anything to do with being slow, from my own research into it, there is not much speed improvement with MYSQLi vs MYSQL. Real world tests don't show that being a big performance driver. I've honestly stuck with MYSQL for all my apps.

Laravel 5.3 - How to perform a query across multiple databases?

Interesting.. As far as I know Laravel doesn't natively support this.

I can think of a way to make this work:

You can create multiple databases in your .env file:

DB_CONNECTION=region1
DB_HOST=adomain.com
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

And another one:

DB_CONNECTION=region2
DB_HOST=anotherdomain.com
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Then add it to your database config:

'region1' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Do the same for region2.

Now extend the Eloquent class and add a method like "setMultipleConnections":

public method setMultipleConnections($databases, Model $model) {

   $collection = new Illuminate\Database\Eloquent\Collection;
   // Loop over $databases array and for each one of them get the collection. 
   foreach ($databases as $database) {
       $model = new $model;
       $model->setConnection($database);
       $model->get();

       $collections->merge($model);
   }

   return $collection;

}

Then I think you can call methods like all and find on that collection.

None of this works, it's just a quick draft. I think you know what I'm trying to achieve here. Is this something that you're looking for?

Multiple Database Query MYSQL

It sounds like, based on what you're trying, the best solution would be a union:

SELECT a.* FROM pharmana_Hareket_db.`general_Table` a 
UNION
SELECT b.* FROM pharmana_urun_db.`general_Table` b

Building on the above example, you could do:

SELECT a.* FROM pharmana_Hareket_db.`general_Table` a 
WHERE a.barCodeField = 1234567980
UNION
SELECT b.* FROM pharmana_urun_db.`general_Table` b
WHERE b.barCodeField = 1234567980

You could of course a use JOIN, depending on the data set up, but it sounds like a UNION would work for you.

Edit: I have just read that you want both, so you could try a join

SELECT * FROM pharmana_Hareket_db.`general_Table` a 
INNER JOIN pharmana_urun_db.`general_Table` b
ON a.barCodeField = b.barCodeField

...which should only return rows that have matching barcodes in both DBs

Can you do queries against two databases if mysql_select_db was already used?

You can. Also check this out: How do I construct a cross database query in PHP?

Connecting multiple database and join query across database in php

You can do this by preceding the table name also with the database name, like you proposed in the example. But the logged on user needs to have access to both databases under the same credentials. This last part is very important, else you won't be able to do it.

This gives you an easy but straightforward example: link

Example taken from that link (will only work with same credentials):

SELECT
    c.customer_name,
    o.order_date
FROM
    db1.tbl_customers c LEFT JOIN
    db2.tbl_orders o ON o.customer_id = c.id

---

Related Topics