doujing2497
2017-12-30 00:25 阅读 39
已采纳

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

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!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    douyong7199 douyong7199 2017-12-30 00:41

    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();
    
    点赞 评论 复制链接分享

相关推荐