php - 在make时同时选择具有相同表的多个数据库

Question

This question already has an answer here:

SELECT 2 Identical databases to get 2 website orders on one list
                                
                                    （1个答案）
                                
                        
                                2年前关闭。
            
                    
我正在尝试将两个数据库连接到mysql查询中，这也很棒！

之后，我尝试在正确的数据库中查找有关订单的数据以从中获取meta_value。

但是它混合了meta_value $名字，有时还会在行中重复它们。

如果我仅连接1个数据库，它们将以应有的方式出现。有人可以看到哪里出了问题？

<?php
$servername = "xxx";
$username   = "xxx";
$password   = "xxx";


$v1  = "_wp282";
$v2  = "_wp111";
$v3  = "_wp72";
$v4  = "_wp193";
$v5  = "_wp555";
$v6  = "_wp366";
$v7  = "_wp74";
$v8  = "_wp721";
$v9  = "_wp924";
$v10 = "_wp253";


// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

//Here I connect to both databases
$query103 = mysqli_query($conn, "SELECT * FROM  
              `$v1`.`wpd2_posts`  
              where post_status='wc-processing' or post_status='wc-completed' 
                  or post_status='wc-failed' UNION 
       SELECT * FROM `$v2`.`wpd2_posts`  
        where post_status='wc-processing' or post_status='wc-completed' 
          or post_status='wc-failed' order by post_date DESC ") or die(mysqli_error($conn));

?> 

<br> 
      <h2>Database 3</h2> 

        <table style="width: 100%"> 
          <tr> 
           <td>ID</td> 
           <td>??</td> 
           <td>??</td> 
           <td>??</td> 
           <td>??</td> 
           <td>??</td> 


          </tr> 
      <?php
while ($row = mysqli_fetch_array($query103)) {


    // Here I try to find what database the customer is on
    $id2 = $row['ID'];

    if ($row['ID'] == '') {

        echo "intet id";
    } else {

        $query = mysqli_query($conn, "SELECT * FROM  `$v1`.`wpd2_posts` where ID = '$id2' ORDER BY id") or die(mysqli_error($conn));

        if (mysqli_num_rows($query) == '') {

            $query = mysqli_query($conn, "SELECT * FROM  `$v2`.`wpd2_posts` where ID = '$id2' ORDER BY id") or die(mysqli_error($conn));

            if (mysqli_num_rows($query) == '') {

            } else {
                $version = "coalsmil_wp111";

            }

        } else {
            $version = "coalsmil_wp282";

        }


    }

    echo "<tr>";
    echo "<td>" . $row['ID'] . "</td>";
    echo "<td>" . $row['post_date'] . "</td>";
    echo "<td>" . $row['post_status'] . "</td>";

    //And here I try to get the data out


    $querynavn = mysqli_query($conn, "SELECT meta_value FROM  `$version`.`wpd2_postmeta` where meta_key='_shipping_first_name' and post_id='$id2' ") or die(mysqli_error($conn));
    while ($row2 = mysqli_fetch_array($querynavn)) {
        $fornavn = urldecode($row2['meta_value']);
    }
    echo "<td>" . $fornavn . "</td>";
    echo "<td>" . $version . "</td>";
    echo "<td>6</td>";
    echo "</tr>";

}


?>
</table>

最佳答案

不要选择*，而是指定每列并用AS命名。喜欢

SELECT `ID` as v1_ID, `post_status` as v1_post_status, `field1` as v1_field1 FROM 
          `$v1`.`wpd2_posts` 
          where post_status='wc-processing' or post_status='wc-completed'
              or post_status='wc-failed' UNION
   SELECT ID` as v2_ID, `post_status` as v2_post_status, `field1` as v2_field1 FROM `$v2`.`wpd2_posts` 
    where post_status='wc-processing' or post_status='wc-completed'
      or post_status='wc-failed' order by post_date DESC

您的结果数组将包含您指定的所有字段名。

另外，请考虑@tadman对您的帖子的评论，并确保数据库安全。

关于php - 在make时同时选择具有相同表的多个数据库，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45513815/

上一篇：mysql - 如何将表的某些列移动到新表并将 ids 保存回原始表

下一篇：php - 使用分页搜索 PHP/MySQL

Answer 1

不要选择*，而是指定每列并用AS命名。喜欢

SELECT `ID` as v1_ID, `post_status` as v1_post_status, `field1` as v1_field1 FROM 
          `$v1`.`wpd2_posts` 
          where post_status='wc-processing' or post_status='wc-completed'
              or post_status='wc-failed' UNION
   SELECT ID` as v2_ID, `post_status` as v2_post_status, `field1` as v2_field1 FROM `$v2`.`wpd2_posts` 
    where post_status='wc-processing' or post_status='wc-completed'
      or post_status='wc-failed' order by post_date DESC

您的结果数组将包含您指定的所有字段名。

另外，请考虑@tadman对您的帖子的评论，并确保数据库安全。