Introducing how to complete table joint query of two different server databases in PHP

For developers using PHP, a common scenario is the need to jointly query data from the databases of two different servers. This scenario may be due to business needs or because the data is distributed on different servers. This article will introduce how to complete this data federation query operation in PHP.

1. Determine the data required for query

Before we start writing code, we need to determine the data we want to query. Suppose we have two database services, named A and B. We need to jointly query customer information from these two databases, including customer ID, name, age, and gender. Both databases A and B have customer tables, which are customers_a and customers_b respectively. These tables include basic information about customers.

Our query goal is to obtain the information of all customers in customers_a and customers_b, so we need to perform JOIN operations on the two tables.

2. Connecting two databases

It is easy to connect two databases using PHP. The following is a code example to connect to database A:

<?php
$host = &#39;localhost&#39;;
$username = &#39;db_user&#39;;
$password = &#39;db_password&#39;;
$dbname = &#39;database_a&#39;;

$db_a = new mysqli($host, $username, $password, $dbname);
if ($db_a->connect_error) {
    die('Connect Error (' . $db_a->connect_errno . ') '
            . $db_a->connect_error);
}

Similarly, we can connect to database B:

<?php
$host = &#39;localhost&#39;;
$username = &#39;db_user&#39;;
$password = &#39;db_password&#39;;
$dbname = &#39;database_b&#39;;

$db_b = new mysqli($host, $username, $password, $dbname);
if ($db_b->connect_error) {
    die('Connect Error (' . $db_b->connect_errno . ') '
            . $db_b->connect_error);
}

Now we have successfully connected to two different databases.

3. Perform a joint query operation

Next, we need to perform a JOIN operation on the two databases to obtain the data we need. In order to ensure the readability and maintainability of the code, we encapsulate the query operation into a function.

<?php
function get_customers($db_a, $db_b) {
    $sql = "SELECT id, name, age, gender FROM customers_a
            UNION
            SELECT id, name, age, gender FROM customers_b";
    $result = $db_a->query($sql);
    if (!$result) {
        die('Query Error: ' . $db_a->error);
    }
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    $result->free();

    return $rows;
}

In this function, we use the UNION operation to merge the data in customers_a and customers_b together. After executing the query, we put the records in the result set into an array and return the array.

4. Call the query function

Now we have completed all the code writing work. We can call the get_customers function in the code to obtain the customer information jointly queried from the two databases A and B.

<?php
$rows = get_customers($db_a, $db_b);
foreach ($rows as $row) {
    echo $row[&#39;id&#39;] . &#39; &#39; . $row[&#39;name&#39;] . &#39; &#39; . $row[&#39;age&#39;] . &#39; &#39; . $row[&#39;gender&#39;] . &#39;<br>';
}

5. Complete code example

The following is the complete code example:

<?php
$host_a = &#39;localhost&#39;;
$username_a = &#39;db_user&#39;;
$password_a = &#39;db_password&#39;;
$dbname_a = &#39;database_a&#39;;

$host_b = &#39;localhost&#39;;
$username_b = &#39;db_user&#39;;
$password_b = &#39;db_password&#39;;
$dbname_b = &#39;database_b&#39;;

$db_a = new mysqli($host_a, $username_a, $password_a, $dbname_a);
if ($db_a->connect_error) {
    die('Connect Error (' . $db_a->connect_errno . ') '
            . $db_a->connect_error);
}

$db_b = new mysqli($host_b, $username_b, $password_b, $dbname_b);
if ($db_b->connect_error) {
    die('Connect Error (' . $db_b->connect_errno . ') '
            . $db_b->connect_error);
}

function get_customers($db_a, $db_b) {
    $sql = "SELECT id, name, age, gender FROM customers_a
            UNION
            SELECT id, name, age, gender FROM customers_b";
    $result = $db_a->query($sql);
    if (!$result) {
        die('Query Error: ' . $db_a->error);
    }
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    $result->free();

    return $rows;
}

$rows = get_customers($db_a, $db_b);
foreach ($rows as $row) {
    echo $row['id'] . ' ' . $row['name'] . ' ' . $row['age'] . ' ' . $row['gender'] . '<br>';
}

$db_a->close();
$db_b->close();
?>

Summary

In PHP, connect two different databases And performing joint query operations is a very common scenario. This article describes how to connect two different database services in PHP and use the UNION operation to complete the joint query task. Through this article, we hope readers can better understand how to use multiple database services in PHP.

The above is the detailed content of Introducing how to complete table joint query of two different server databases in PHP. For more information, please follow other related articles on the PHP Chinese website!