Member Avatar for magikman

Hello,

I need to select information from two different databases and join them into one web page. I am unsure how to go about doing this. I am not a PHP programmer, any help would be appreciated.

  • 3 Contributors
  • 4 Replies
  • 117 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by Shanti C

Dani AI

Generated

Short practical summary for : if both databases are hosted by the same MySQL server instance, a server-side JOIN using fully qualified identifiers is the simplest and most efficient option (as and were pointing toward). If the databases live on different MySQL servers, a plain single-statement JOIN is not available without extra infrastructure; common alternatives are the FEDERATED storage engine, pulling data into PHP and merging there, or replicating/syncing data into a central database.

Options and tradeoffs:

  • Same server: do the JOIN in SQL for best performance and transactional integrity (see MySQL JOINs).
  • Different servers: consider a FEDERATED table to make a remote table appear local (see FEDERATED storage engine) — this removes the need for app-level merging but has limitations (performance, transactions, setup).
  • Application-level merge: open two DB connections from PHP, fetch the rows needed, then join them in PHP. This works with any servers and gives full control over batching and error handling.

Example PHP pattern (application-level merge):

try {
    $opts = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
    $db1 = new PDO('mysql:host=hostA;dbname=dbA;charset=utf8mb4','user','pass',$opts);
    $db2 = new PDO('mysql:host=hostB;dbname=dbB;charset=utf8mb4','user','pass',$opts);

    $rowsA = $db1->query('SELECT id, colA FROM tableA')->fetchAll(PDO::FETCH_ASSOC);
    $rowsB = $db2->query('SELECT id, colB FROM tableB')->fetchAll(PDO::FETCH_ASSOC);

    $map = [];
    foreach ($rowsA as $r) { $map[$r['id']] = $r; }
    foreach ($rowsB as $r) {
        if (isset($map[$r['id']])) { $map[$r['id']] = array_merge($map[$r['id']], $r); }
        else { $map[$r['id']] = $r; }
    }
    // $map now holds merged rows keyed by id
} catch (PDOException $e) {
    error_log($e->getMessage());
}

Practical tips: use indexed join keys, match character sets, limit fetched rows (pagination/batching) for large datasets, use prepared statements to avoid injection (PDO docs), and ensure proper user privileges (MySQL GRANTs).

Recommended Answers

All 4 Replies

Member Avatar for NormandP

Not absolutely sure...

If the 2 databases are located on the same server (same "username", "password" and "localhost")...

I think you should try something like this:

$query="SELECT
first_database_name . first_table_name . optional_column_name ,
second_database_name . second_table_name . optional_column_name

FROM
first_database_name . first_table_name . optional_column_name

INNER JOIN
second_database_name . second_table_name . optional_column_name

etc...

Member Avatar for Shanti C

On the same server just do this:

SELECT dbname.tablename.columnname,db2name.table2name.column2name FROM 
dbname.tablename, db2name.table2name;

(I think you get the idea)

As far as I know you can't select data across multiple servers.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.