php - 如何通过多个查询将多个表数据提取到一个数据表

Question

如何通过多个查询将多个表数据提取到一个 jquery dataTable。我想将多个查询的多个表数据提取到一个数据表中。我可以根据我的代码用一个查询来完成吗，我试过 MySQL UNION，不适合我。

Fetch.php

$query1 = "SELECT SUM(score) AS marks, from_date FROM table1 WHERE stat = 0 GROUP BY name ";
$statement = $db->prepare($query1);
$statement->execute();
$output = array('data' => array());
$count = $statement->rowCount();
if($count > 0) {

while($row = $statement->fetch(PDO:: FETCH_OBJ)) {

    $score = $row->marks;
    $date = $row->from_date;
    $Team = "Team 1";

$output['data'][] = array( $score,$date,$Team);     
 } // /while 
}// if num_rows

$query2 = "SELECT SUM(marks) AS marks, from_date FROM table2 WHERE stat = 0 GROUP BY name ";
$statement = $db->prepare($query2);
$statement->execute();
$output = array('data' => array());
$count = $statement->rowCount();
if($count > 0) {

while($row = $statement->fetch(PDO:: FETCH_OBJ)) {

    $score = $row->marks;
    $date = $row->from_date;
    $Team = "Team 2";

$output['data'][] = array( $score,$date,$Team);     
}
}// if num_rows
echo json_encode($output);

html

<table id="Table">
    <thead>
      <tr>
        <th>Score</th>
        <th>Start Date</th>
        <th>Team</th>
      </tr>
    </thead>
    <tbody>

    </tbody>
</table>

<script>    
var score;
$(document).ready(function(){
score = $('#Table').DataTable({
    'ajax': 'fetch.php',
    'order': []
    });
});
</script>

Answer 1

您可以使用UNION ALL 来获取结果。

Note:- Both table column should be the same, else you will get an error.

$result = [];
try {
  $conn = new PDO('mysql:host=HOST;dbname=DB;charset=utf8mb4', 'USERNAME', 'PASSWORD');
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $conn->beginTransaction();

  $stmt1 = $conn->prepare("SELECT SUM(score) AS marks, from_date FROM table1 WHERE stat = 0 GROUP BY name UNION ALL SELECT SUM(marks) AS marks, from_date FROM table2 WHERE stat = 0 GROUP BY name ");
  $stmt1->execute();
  $result[]  = $stmt1->fetchAll(PDO::FETCH_ASSOC);
  $conn->commit();

} catch (PDOException $e) {
   echo $e->getMessage();
}   
echo json_encode($result);

对于团队变量，可以试试

SELECT SUM(score) AS marks, from_date,'Team1' AS team FROM table1 WHERE stat = 0 GROUP BY name 

和另一个查询

SELECT SUM(marks) AS marks, from_date, 'Team2' AS team FROM table2 WHERE stat = 0 GROUP BY name