php - 如何使用单个查询从多个mysql表中获取数据

Question

我使用下面的代码使用 phpexcel 将列从两个 mysql 表导出到一个 excel 文件。

我通过运行查询两次来实现它。有人可以通过仅运行一个查询并使用 LIKE 和 UNION 来帮助我完成这项工作。

谢谢!

编辑:根据要求，表的数量将继续增加，但将具有相同的名称...因此必须找到一个解决方案，首先使用 LIKE 选择数据库中的所有表，然后查询来自所有表的列数据。然后将这些数据并排输出到 Excel 工作表。如表 1 col A1-AJ 上的数据，tab;e 2 来自 AL-AR 的数据等。

这是我的代码:

<?php

// connection with the database 
$dbhost = "localhost"; 
$dbuser = "root"; 
$dbpass = "redhat"; 
$dbname = "was"; 

mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname); 

// require the PHPExcel file 
require 'phpexcel/Classes/PHPExcel.php'; 

$query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report";
$query1 = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report"; 
$headings = array('Service Date','Rec. Date','DOS to Rec. Lag','Cases Rec.','scan Date','Cases Entered','Cases Pending','Cases Count Diff','Entry Date','Rec. to Entry Lag');

if ($result = mysql_query($query) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet()->setTitle('Report'); 
    $rowNumber = 8; 
    $col = 'A'; 

    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
    } 

    // Loop through the result set 
    $rowNumber = 9; 

    while ($row = mysql_fetch_row($result)) { 
       $col = 'A'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
    }

 } else {

 echo 'a problem has occurred... no data retrieved from the database';
 }  

 if ($result1 = mysql_query($query1) or die(mysql_error())) { 
    // Create a new PHPExcel object 
    $objPHPExcel = new PHPExcel(); 
    $objPHPExcel->getActiveSheet(); 
    $rowNumber = 8; 
    $col = 'L'; 

    foreach($headings as $heading) { 
       $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
       $col++; 
    }


    // Loop through the result set 
    $rowNumber = 9; 

    while ($row = mysql_fetch_row($result)) { 
       $col = 'L'; 
       foreach($row as $cell) { 
          $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
          $col++; 
       } 
       $rowNumber++; 
    }

 } else {

 echo 'a problem has occurred... no data retrieved from the database';
 }   

 // Freeze pane so that the heading line won't scroll 
    $objPHPExcel->getActiveSheet()->freezePane('A2'); 

    // Save as an Excel BIFF (xls) file 
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

   header('Content-Type: application/vnd.ms-excel'); 
   header('Content-Disposition: attachment;filename="report.xls"'); 
   header('Cache-Control: max-age=0'); 

   $objWriter->save('php://output'); 

?>

所有的表都会有相同的列但数据不同

Answer 1

使用联合尝试以下操作(阅读 https://dev.mysql.com/doc/refman/5.0/en/union.html )

$query = "
SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
union
SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report
";

编辑:鉴于您的更新声明“将这些数据并排输出到 Excel 工作表。就像 col A1-AJ 上的表 1 数据，选项卡；来自 AL-AR 的 e 2 数据等等”只是做“SELECT DISTINCT ... LEFT JOIN”而不是联合...您可以更改名称以反射(reflect)表，但这不是必需的，除非您在 WHERE 子句中添加条件...

    $query = "
    SELECT DISTINCT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
left join hos2report";

性能会很糟糕，如果你可以通过 AJ 显示 col A1 中的所有数据，那会简单得多......你甚至可以像这样添加一个列来指定哪个表是源

$query = "
SELECT 'hos1report' as tablename, servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report
union
SELECT  'hos2report' as tablename, servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos2report
";