Brayn Brayn - 2 months ago 8
SQL Question

Echo two SQL queries in one HTML table

I'm trying to display information from two queries in one single table, but can't figure out how to make it work.

This is what I got working with one query:

SELECT Company,COUNT(*) as count FROM Employee_Table GROUP BY Company ORDER BY count DESC;

Company Employees
ABC 45
DEF 15
GHI 5


Now beneath that I'd like to have another query that simply counts all rows, giving me the total amount of Employees.

SELECT Company,COUNT(*) as count FROM Employee_Table GROUP BY Company ORDER BY count DESC;

SELECT COUNT(*) AS Total FROM Employee_Table;

Company Employees
ABC 40
DEF 15
GHI 5

Total 60


This is what my code looks like right now. I defined two extra variables for my extra query that I want to echo out, but believe this is not the proper way to do it as I get an sqlsrv_fetch_array error.

$query1 = "SELECT Company,COUNT(*) as count FROM Employee_Table GROUP BY Company ORDER BY count DESC;";
$query2 = "SELECT COUNT(*) AS Totaal FROM Employee_Table;";
$result1 = sqlsrv_query($conn, $query1);
$result2 = sqlsrv_query($conn, $query2);

echo "<table id='total'>";
echo "<tr><th>Company</th><th>Amount of employees</th></tr>";
while ($row=sqlsrv_fetch_array($result1, $result2)) {
echo "<tr><td>";
echo $row["Company"];
echo "</td><td>";
echo $row["count"];

echo "</td><td>";
echo $row["Total"];
echo "</td></tr>";
}
echo "</table>";


How can this be achieved? I'd appreciate any help

Answer

I think you can achieve this by using only first query

$query1 = "SELECT Company,COUNT(*) as count FROM Employee_Table GROUP BY Company ORDER BY count DESC;";

$result1 = mysql_query($conn, $query1);

$total_employee = 0;
echo "<table id='total'>";
echo "<tr><th>Company</th><th>Amount of employees</th></tr>";
while ($row=mysql_fetch_array($result1)) {
echo "<tr><td>";
echo $row["Company"];
echo "</td><td>";
echo $row["count"];
$total_employee += $row["count"];
echo "</td><td>";

echo "</td></tr>";
}
echo "<tr><td>Total</td><td>$total_employee</td></tr>";
echo "</table>";