How to fetch multiple table data with multiple queries to one data-table

Question

How can I fetch multiple table data with multiple queries to one jquery dataTable. I want to fetch multiple table data of multiple queries to one data-table. can i do it with one query according to my code, I tried MySQL UNION, Not suitable for me.

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>

`from_date` is not aggregated. Can you show us tables structures, data sample and expected output ? — Cid, Apr 20 '19 at 06:48
@Cid i want to fetch only first date of the group, how can i do it. — Mili, Apr 20 '19 at 06:49

score 2 · Accepted Answer · edited Apr 20 '19 at 21:09

2

You can use UNION ALL to fetch the result.

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);

For the team variable, you can try

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

and another query

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

edited Apr 20 '19 at 21:09

Mili

238
1
10

answered Apr 20 '19 at 06:59

Rakesh Jakhar

6,380
2
11
20

Can you convert it to PHP PDO. – Mili Apr 20 '19 at 07:00
@Mili refer to this link https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Rakesh Jakhar Apr 20 '19 at 07:03
I am placing `$Team = "Team 2";` to every query, How could i manage it. – Mili Apr 20 '19 at 11:22
@Mili use bindValue, check https://www.php.net/manual/en/pdostatement.bindvalue.php – Rakesh Jakhar Apr 20 '19 at 11:24

How to fetch multiple table data with multiple queries to one data-table

1 Answers1