php - 将 6 个查询合并为 1 个结果集以进行导出？

Question

好吧，这可能听起来有点奇怪，而且可能过于复杂。情况是这样的。我 2 组 3 个查询。我将尝试做一个简单的例子来准确解释我想要做什么:

查询:

//First set of queries
$query1 = "SELECT Name, Date FROM Table1";
$query2 = "SELECT Type, Place, Location FROM Table2";
$query3 = "SELECT One FROM Table3";

//Second set of queries
$query4 = "SELECT Name, Date FROM Table1 WHERE ID=1";
$query5 = "SELECT Type, Place, Location FROM Table2 WHERE ID=1";
$query6 = "SELECT One FROM Table3 WHERE ID=1";

当我告诉您我无法合并这两组查询时，您只需相信我即可。这些是过度简化的选择语句，以了解我想要做的事情的概念。

这是我的 php 代码:

//Set 1
$data1 = mysql_query($query1) or die(mysql_error());
$data2 = mysql_query($query2) or die(mysql_error());
$data3 = mysql_query($query3) or die(mysql_error());
while ($line1 = mysql_fetch_array($data1, MYSQL_ASSOC) &&
        $line2 = mysql_fetch_array($data2, MYSQL_ASSOC)) {
    while ($line3 = mysql_fetch_array($data3, MYSQL_ASSOC)) {
        //COMBINE $line1, line2, line3 into a single $lineSet1 -- HOW DO I DO THIS?
    }
}

//Set 2
$data4 = mysql_query($query4) or die(mysql_error());
$data5 = mysql_query($query5) or die(mysql_error());
$data6 = mysql_query($query6) or die(mysql_error());
while ($line4 = mysql_fetch_array($data4, MYSQL_ASSOC) &&
        $line5 = mysql_fetch_array($data5, MYSQL_ASSOC)) {
    while ($line6 = mysql_fetch_array($data6, MYSQL_ASSOC)) {
        //COMBINE $line4, line5, line6 into a single $lineSet2 -- HOW DO I DO THIS?
    }
}

//Append $lineset1 and $lineset2 so I have 1 resultset $results
$result = array_merge($lineSet1, $lineSet2);
//So now I can pass this $result array into my array2csv function that takes a multidimensional array:
function array2csv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("exportedLeads{$_SESSION['id']}.csv", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

我知道这看起来真的很复杂，但我很困惑并且不擅长 php。任何帮助，将不胜感激。谢谢!

TABLE1:

ID | Name | Date

TABLE2:

ID | Table1_ID | Type | Place | Location

TABLE3:

ID | Table1_ID | One

编辑:我一直在阅读 JOIN 语句。这可能是一种情况吗？

Answer 1

您可以将两个集合恢复到单个查询中，例如使用 JOIN 假设您的 ID 匹配。

首先设置为 1 个查询:

SELECT t1.Name, t1.Date, t2.Type, t2.Place, t3.One FROM Table1 t1
  JOIN Table2 t2
    ON t2.Table1_ID = t1.ID
  JOIN Table3 t3
    ON t3.Table1_ID = t1.ID

第二组设置为 1 个查询:

SELECT t1.Name, t1.Date, t2.Type, t2.Place, T2.Location, t3.One
  FROM Table1 t1
  JOIN Table2 t2
    ON t2.Table1_ID = t1.ID
  JOIN Table3 t3
    ON t3.Table1_ID = t1.ID
 WHERE t1.ID = 1