How do I construct a cross database query in PHP?

by

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

More Related Contents:

  1. Can’t insert data into mysql database using PHP [closed]
  2. which is better every user have a table or just a row [closed]
  3. How can I use PDO to fetch a results array in PHP?
  4. Fixing broken UTF-8 encoding
  5. Insert multiple rows with one query MySQL
  6. Why is using a mysql prepared statement more secure than using the common escape functions?
  7. Export MySQL data to Excel in PHP
  8. MySQL – ignore insert error: duplicate entry
  9. How to write a stored procedure using phpmyadmin and how to use it through php?
  10. localhost vs. 127.0.0.1 in mysql_connect()
  11. “slash before every quote” problem [duplicate]
  12. Doing calculations in MySQL vs PHP
  13. Call to a member function execute() on boolean in [duplicate]
  14. SQLSTATE[HY000] [1045] Access denied for user ‘username’@’localhost’ using CakePHP
  15. How to store the data in unicode in hindi language
  16. PDO + MySQL and broken UTF-8 encoding
  17. Best practice: Import mySQL file in PHP; split queries
  18. Find Point in polygon PHP
  19. phpmyadmin automatic logout time
  20. unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING error [duplicate]
  21. Setting a connect timeout with PDO
  22. mysql_connect(): No connection could be made because the target machine actively refused it
  23. How to display a date as iso 8601 format with PHP
  24. Join two tables with comma separated values
  25. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  26. Remotely connecting to a MySQL database
  27. PDO bindParam into one statement?
  28. Is mysql_real_escape_string enough to Anti SQL Injection?
  29. How to make mysqli connect function?
  30. Using PHP to upload file and add the path to MySQL database

Leave a Comment