Home > Back-end >  Multiple query Output into single Table
Multiple query Output into single Table

Time:10-19

Here is my current code. I'm trying to display data from two separate queries into a single table with multiple columns. What is wrong here? I get bellow error,

Warning: Undefined array key 71 in C:\xampp\htdocs\report\new2.php on line 58

Warning: Trying to access array offset on value of type null in C:\xampp\htdocs\report\new2.php on line 58

echo "<td>" . $data['row2'][$i]['Entity_Name'] . "</td>";

In this line I get error.

<?php

$sql = mysqli_query(
  $connection, 
  "SELECT Entity_Name, Count(Sid)
   FROM mq_active_sep21 
   WHERE Plan_Name != 'Complementary_Package'
   GROUP BY Entity_Name
   ORDER BY Entity_Name"
  );

$sql2 = mysqli_query(
  $connection,
 "SELECT Entity_Name, Count(Sid)
 FROM mq_active_sep21
 WHERE Plan_Name = 'Complementary_Package'
 GROUP BY Entity_Name
 ORDER BY Entity_Name"
);


?>
    <html>
    <head>
        <title>Report Details </title>
    </head>
    <body>
    <h1>Report</h1>
    <hr>
    <table border = '2'>
        <tr>
            <th>Entity Name</th>
            <th>Total SID</th>
            <th>Entity Name</th>
            <th>Total SID</th>
        </tr>


        <?php

        $data = array();

        while($row = mysqli_fetch_assoc($sql)) {$data['row'][] = $row;}
        while($row = mysqli_fetch_assoc($sql2))  {$data['row2'][] = $row;}

        $count = count($data['row']);

        for($i=0;$i<$count;$i  )
        {
            echo '<tr>';
            if(($i % 2) == 1)
            {
                echo "<td>" . $data['row2'][$i]['Entity_Name'] . "</td>";
                echo "<td>" . $data['row2'][$i]['Count(Sid)'] . "</td>";
            }else
            {
                echo "<td>" . $data['row'][$i]['Entity_Name'] . "</td>";
                echo "<td>" . $data['row'][$i]['Count(Sid)'] . "</td>";
            }
            echo '</tr>';
        }
        ?>

    </table>

    </body>
    </html>

CodePudding user response:

I can advise you to use single query:

SELECT 
    Entity_Name,
    Count(*) Count_All,
    Count(NULLIF(Plan_Name, 'Complementary_Package')) Not_Complementary_Package
FROM mq_active_sep21 
GROUP BY Entity_Name 
ORDER BY Entity_Name;

and code like:

$sql = mysqli_query($connection, "
    SELECT 
        Entity_Name,
        Count(*) Count_All,
        Count(NULLIF(Plan_Name, 'Complementary_Package')) Not_Complementary_Package
    FROM mq_active_sep21 
    GROUP BY Entity_Name 
    ORDER BY Entity_Name;
");

while($row = mysqli_fetch_assoc($sql)) {
    printf(
        "Entity_Name: %s, Total: %d, Complementary_Package: %d " . PHP_EOL, 
        $row['Entity_Name'],
        $row['Count_All'],
        $row['Count_All'] - $row['Not_Complementary_Package']
    );
}

Test PHP & SQL code online

  • Related