How do I construct a cross database query in PHP?

Viewed 75 times

In our last episode (How I constructed a cross database query in MySQL) I learned how to construct a cross database query in MySQL. This worked great but when our hero tried to use this newfound knowledge in PHP he found his best friend FAIL waiting for him.

I took a look at mysql_select_db for PHP. This seems to imply that if I want to use MySQL with PHP, I have a couple of choices:

Use mysql_select_db but be stuck with only using one db at a time. This is our current setup and putting a database as a namespace identifier doesn't seem to work (it works fine in the MySQL shell so I know it's not a problem with our MySQL server setup).
Don't use mysql_select_db. From some of the examples I've seen, this seems to mean that I have to specify the db for every query that I make. This makes sense since I haven't used mysql_select_db to tell PHP what db I want to access. This also makes sad since I don't want to go through all my code and prepend a db name to every query.

Is there something better than this? Is there a way for me to do a cross db MySQL query in PHP without having to something crazy like (2)?

CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ... NOT just make different queries to different DBs. For what it's worth, (2) doesn't work for me.

Answers

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);

Wednesday, August 10, 2022

wjh-aa765f581578

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

klas_lindbäck

Perhaps something like this. (untested)

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";
$args=array();

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = :val1"
    $args[':val1']=$val1;
} else {
    $TempSQL = $TempSQL . " field2 = :val2 and field3 = :val3";
    $args[':val2']=$val2;
    $args[':val3']=$val3;
}

$stmt=$db->prepare($TempSQL);
$stmt->execute($args);

Tuesday, September 27, 2022

akshay_mulgavkar

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc

Wednesday, August 17, 2022

thecontstruct

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

Tuesday, November 29, 2022

lukewarm

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

How do I construct a cross database query in PHP?

Answers

Keep in mind

Reference:

php,mysql