Multiple query Output into single Table

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:xampphtdocsreportnew2.php on line 58

Warning: Trying to access array offset on value of type null in C:xampphtdocsreportnew2.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>

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating