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

CodePudding user response：

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>

CodePudding user response：

First of all your query does produces only one row for a table, not 281. The second - I omit usage of prepared SQL statements with placeholders, which should always be used in a real project whenever applyable.

$rows = [];
foreach(['Tasks', 'Quotes'] as $table ){
    $result = $conn->query("SELECT '$table' as 'table', count(*) as 'count' FROM $table");
    if( $result ) 
        $rows[] = $result->fetch_assoc();
}

if( empty( $rows ) )
    print "0 results";
else {?>
    <table style="width:100%">
        <tr><th>Table</th><th>Count</th></tr>
        <?=implode(
            "\n", 
            array_map(function($row){
                return "<tr><td>${row['table']}</td><td>${row['count']}</td></tr>";
            }, $rows)
        )?>
    </table>
<?php }