从多个数据库执行多个语句

Question

DB 1:

<?php
    $servername = "localhost";
    $dbname = "root";
    $dbpassword = "";
    $name = "one";

    $db_one = new mysqli($servername, $dbname, $dbpassword, $name);

    if ($db_one->connect_error) {
        die("We are likely experiencing a heavy traffic load, please come back later!");
    }
    ?>

DB 2:

<?php
$servername = "localhost";
$dbname = "root";
$dbpassword = "";
$name = "two";

$db_one = new two($servername, $dbname, $dbpassword, $name);

if ($db_one->connect_error) {
    die("We are likely experiencing a heavy traffic load, please come back later!");
}
?>

My Attempted Code:

<?php require("db.php"); ?>

<?php //Select All Orders
$stmt = $db_one->prepare("SELECT * FROM `one`");
$stmt = $db_one->prepare("SELECT * FROM `one_table2`");
$stmt = $db_two->prepare("SELECT * FROM `two`");
$stmt->execute();

$result = $stmt->get_result();
if($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {

    echo $one[] = "<tr><td>".$row["fromdbone"]."</td>";
    echo $two[] = "<td>".$row["fromdbtwo"]."</td>";
    echo $three[] = "<td>".$row["fromdbone_table2"]."</td></tr>";
  }
}
$stmt->close();
?>

Hey everyone I am at a loss here, how exactly would I go about executing multiple querys from different tables and databases in order to put it all into one table?

Thanks so much for your insight I trust you are all a lot smarter than me!

EDIT: Or if I was to do multipe querys, how would I get them all into one HTML table?

Thanks, I have been trying to figure this out for hours so I hope you can help!

Answer 1

Since the two databases are on the same server (localhost), you don't need to use different connections for them. Use a single connection, and join all the tables. The table that isn't in the default database can be accessed using the database prefix.

$stmt = $db_one->prepare("SELECT * FROM one AS o
    JOIN one_table2 AS o2 ON o.id = o2.one_id
    JOIN two.two AS t ON o.id = t.one_id");
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $one[] = "<tr><td>".$row["fromdbone"]."</td>";
    echo $two[] = "<td>".$row["fromdbtwo"]."</td>";
    echo $three[] = "<td>".$row["fromdbone_table2"]."</td></tr>";
  }
}
$stmt->close();

To do it with multiple statements, you need to bind parameters.

$stmt1 = $db_one->prepare("SELECT * FROM `one`");
$stmt2 = $db_one->prepare("SELECT * FROM `one_table2` WHERE one_id = ?");
$stmt3 = $db_two->prepare("SELECT * FROM `two` WHERE one_id = ?");

$stmt2->bind_param("i", $one_id);
$stmt3->bind_param("i", $one_id);

$stmt1->execute();
$result1 = $stmt1->get_result();
while ($row1 = $result1->fetch_assoc();
    $one_id = $row['id'];
    $stmt2->execute();
    $result2 = $stmt2->get_result();
    $row2 = $result2->fetch_assoc();
    $stmt3->execute();
    $result3 = $stmt3->get_result();
    $row3 = $result3->fetch_assoc();
    echo $one[] = "<tr><td>".$row1["fromdbone"]."</td>";
    echo $two[] = "<td>".$row2["fromdbtwo"]."</td>";
    echo $three[] = "<td>".$row3["fromdbone_table2"]."</td></tr>";
}
$stmt1->close();
$stmt2->close();
$stmt3->close();