[SOLVED] Displaying SQL Query in HTML table (php)

Issue

Trying to get counts from 2 different tables into a simple HTML table.

This is my code:

$sql = "SELECT COUNT(*) FROM tasks";
$result = $conn->query($sql);
$rowcount=mysqli_num_rows($result);

if ($result->num_rows > 0) { ?>

<table style="width:100%">
  <tr>
    <th>Total</th> 
  </tr>
  <?php while($row = $result->fetch_assoc()) { ?>
  <tr>
    <td><?=$rowcount;?></td> 
  </tr>
  <?php } ?>
</table>

<?php } else { echo "0 results"; } ?>

When I run the code, it shows the amount of rows in the table, but it also creates the amount of rows with the number in it (i.e. 281 rows).

My idea was to copy and paste the above to show a second table set of results (if it was correct), but is there a better way of doing this? I’ve been looking at how I would display SELECT (select COUNT(*) from tasks) , (select count(*) from quotes) into the following format (HTML):

Solution

I’ve been looking at how I would display SELECT (select COUNT() from
tasks) , (select count() from quotes) into the following format
(HTML)

You can just run the queries query, and use the result of the first to create the first row of the table, then the result of the second to create the second row. Since COUNT queries always return exactly 1 row when there’s no GROUP BY, it’s quite simple to do really:

$sql1 = "SELECT COUNT(*) FROM tasks";
$result1 = $conn->query($sql1);
if ($row = $result1->fetch_array()) $taskCount = $row[0];
else $taskCount = "error";

$sql2 = "SELECT COUNT(*) FROM quotes";
$result2 = $conn->query($sql2);
if ($row = $result2->fetch_array()) $quoteCount = $row[0];
else $quoteCount = "error";

?>
<table style="width:100%">
  <tr>
    <th>Table</th> 
    <th>Count</th> 
  </tr>
  <tr>
    <td>Tasks</td>
    <td><?php echo $taskCount; ?></td>
  </tr>
  <tr>
    <td>Quotes</td>
    <td><?php echo $quoteCount; ?></td>
  </tr>
</table>

---

Another way, if you want the HTML structure to be less repetitive / dependent on the tables queries, is to UNION the SELECTs into a single query:

$sql = "SELECT 'Tasks' AS 'table', COUNT(*) as 'count' FROM tasks";
$sql .= " UNION ";
$sql .= "SELECT 'Quotes' AS 'table', COUNT(*) as 'count' FROM quotes";
$result = $conn->query($sql);
?>

<table style="width:100%">
  <tr>
    <th>Table</th> 
    <th>Count</th> 
  </tr>
<?php
while ($row = $result->fetch_assoc()) { ?>
  <tr>
    <td><?php echo $row["table"]; ?></td>
    <td><?php echo $row["count"]; ?></td>
  </tr>
<?php 
}
?>
</table>

Answered By – ADyson

Answer Checked By – Pedro (BugsFixing Volunteer)