Echo repeating a table header that it shouldn't be

[Jonny125]

Hi there,

 

I'm trying to display the results of a MySQL query in PHP and HTML, however for some reason, it is creating a table for each row of results. When it should be just one table, containing the results for each row returned. At the moment its just dummy data so theres only 3 hours, but with real data would show 24 hours (or 24 rows, not including the table header). 

 

This is how it looks:

 

 

And this is the code:

 

<?php
$connection = mysql_connect("localhost", "username", "password"); //connect to server with these creds, store in $connection variable
if (!$connection)
{die('Could not connect: ' . mysql_error());} //if $connection can not connect give error
mysql_select_db("db_name", $connection); //select database name for $connection

//-------------sql select query for daily stats
$sql ="SELECT storeid, HOUR, SUM( qty ) AS 'Total Quantity', SUM( value ) AS 'Total Value', AVG( qty ) AS 'Average Quantity', AVG( value ) AS 'Average Value', SUM( value ) / SUM( qty ) AS 'Average Value Per Item'
FROM depthour
GROUP BY HOUR"; 
//echo "SQL Query used: "; echo $sql;

$query = mysql_query($sql); //give resource the variables

while ($row = mysql_fetch_array($query)) {  //display results for hour defined by SQL
if (!$query) { // add this check.
    die('Invalid query: ' . mysql_error());
} //-------------------End of SQL for daily stats

echo "<table border='1' cellpadding='2' cellspacing='3' width='100%'>";
echo "<tr><th>Hour</th><th>Total Quantity</th><th>Total Value</th><th>Average Quantity</th><th>Average Value</th><th>Average Value per Item</th></tr>";
echo "<tr><td>" .$row['HOUR'];
echo "</td><td>" .$row['Total Quantity'];
echo "</td><td>" .$row['Total Value'];
echo "</td><td>" .$row['Average Quantity'];
echo "</td><td>" .$row['Average Value'];
echo "</td><td>" .$row['Average Value Per Item'];
echo "</td></tr></table><br>";
}
?>

 

I'm quite new to the MySQL and PHP world so if you can see what I'm doing wrong, an explanation is just as valuable as a fix is. Any coding tips are also very much appreciated, thank you in advance for any help!

[peppericious]

Only the table rows should be echoed within the loop. The opening and closing table tags should be outside. So, you should have:

 

$query = mysql_query($sql); //give resource the variables

echo "<table border='1' cellpadding='2' cellspacing='3' width='100%'>"; // << table top *outside* the loop
while ($row = mysql_fetch_array($query)) {  //display results for hour defined by SQL
if (!$query) { // add this check.
    die('Invalid query: ' . mysql_error());
} //-------------------End of SQL for daily stats


echo "<tr><th>Hour</th><th>Total Quantity</th><th>Total Value</th><th>Average Quantity</th><th>Average Value</th><th>Average Value per Item</th></tr>";
echo "<tr><td>" .$row['HOUR'];
echo "</td><td>" .$row['Total Quantity'];
echo "</td><td>" .$row['Total Value'];
echo "</td><td>" .$row['Average Quantity'];
echo "</td><td>" .$row['Average Value'];
echo "</td><td>" .$row['Average Value Per Item'];
echo "</td></tr>";
}
echo "</table>"; // << table bottom *outside* the loop
?>

[Jonny125]

Ah i see, I also needed to put the table header row outside of the loop so that wouldn't repeat either. Thanks for the help!