Jonny125 Posted April 9, 2013 Share Posted April 9, 2013 Hi there, I've currently got my PHP echoing HTML code displaying the results of a MySQL statement in tables, for every department that there is, the page will display results per department in an additional table / row, and then for each store that there is there will be an additional set of the department tables as shown below: This is all good but its clearly not necessary to display an entire table per record of department, per store, what I would really like is to be able to have a set of tables, just per store that there is (currently only 2 and 1 blank entry) rather than a table set per department. So ideally with the current data, there would be just 3 table sets (1 per store), but each table displaying the data per department in additional columns rather than sets of tables. The way I got around this before was to write an SQL statement per column, where I would specify in the statement which day to Group by. This was fine when I only needed 7 columns (for days of the week) as there wouldn't be anymore than 7 days or 7 SQL statements. However with departments, there could be anywhere between 30 & 999 departments, and as I am a beginner in the PHP & MySQL world I am quite stumped as to how to do this efficiently / properly. Can anyone advise me how to do this, or if I'm extremely fortunate show me an example? as I'm learning explanations are just as valuable as the answer. I hope how I've explained it isn't too long winded and makes sense, if anything doesn't please say so Thank you in advance for any help. My code so far: <?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 hour $sql ="SELECT storeid, dept, SUM( qty ) AS 'Weekly Total Quantity', SUM( value ) AS 'Weekly Total Value', AVG( avgqty ) AS 'Weekly Average Quantity Per Hour', AVG( avgvalue ) AS 'Weekly Average Value Per Hour', SUM( value ) / SUM( qty ) AS 'Avg Value Per Item' FROM depthour GROUP BY dept, storeid ORDER BY storeid, dept"; //echo "SQL Query used: "; echo $sql; $query = mysql_query($sql); //give resource the variables while ($row = mysql_fetch_array($query)) { //display results for hour entered by user if (!$query) { // add this check. die('Invalid query: ' . mysql_error()); } echo "<table border='1' cellpadding='2' cellspacing='3' width='70%'>"; echo "<tr><th colspan='2'>Weekly Statistics for Store: ".$row['storeid']; echo "</th></tr>"; echo "<tr><td width ='40%'>Department: </td><td width ='30%'>" .$row['dept']; echo "</td></tr>"; echo "<tr><td>Weekly Total Quantity: </td><td>" .$row['Weekly Total Quantity']; echo "</td></tr>"; echo "<tr><td>Weekly Total Value: </td><td>" .$row['Weekly Total Value']; echo "</td></tr>"; echo "<tr><td>Weekly Average Quantity Per Hour: </td><td>" .$row['Weekly Average Quantity Per Hour']; echo "</td></tr>"; echo "<tr><td>Weekly Average Value Per Hour: </td><td>" .$row['Weekly Average Value Per Hour']; echo "</td></tr>"; echo "<tr><td>Avg Value Per Item: </td><td>" .$row['Avg Value Per Item']; echo "</td></tr>"; echo "</table><br>"; }; ?> Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted April 9, 2013 Solution Share Posted April 9, 2013 this is one of those cases where explaining how to do it takes longer than writing the code. the key is to detect when the storeid changes and execute logic to finish one table and start the next one. <?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 hour $sql ="SELECT storeid, dept, SUM( qty ) AS 'Weekly Total Quantity', SUM( value ) AS 'Weekly Total Value', AVG( avgqty ) AS 'Weekly Average Quantity Per Hour', AVG( avgvalue ) AS 'Weekly Average Value Per Hour', SUM( value ) / SUM( qty ) AS 'Avg Value Per Item' FROM depthour GROUP BY dept, storeid ORDER BY storeid, dept"; //echo "SQL Query used: "; echo $sql; $query = mysql_query($sql); //give resource the variables if(!$query){ // add this check. die('Invalid query: ' . mysql_error()); } if(mysql_num_rows($query) < 1){ echo "There are no matching rows to display"; } else { $current_store = null; // start with none while($row = mysql_fetch_array($query)){ //display results for hour entered by user if($current_store !== $row['storeid']){ // the store changed if($current_store !== null){ // finish an existing table echo "</table><br>"; } // start a new table echo "<table border='1' cellpadding='2' cellspacing='3' width='70%'>"; echo "<tr><th colspan='6'>Weekly Statistics for Store: {$row['storeid']}</th></tr>"; echo "<tr><th>Department</th><th>Weekly Total Quantity</th><th>Weekly Total Value</th> <th>Weekly Average Quantity Per Hour</th><th>Weekly Average Value Per Hour</th> <th>Avg Value Per Item</th></tr>"; $current_store = $row['storeid']; // store the new storeid } // output the table row of data echo "<tr><td>{$row['dept']}</td><td>{$row['Weekly Total Quantity']}</td> <td>{$row['Weekly Total Value']}</td><td>{$row['Weekly Average Quantity Per Hour']}</td> <td>{$row['Weekly Average Value Per Hour']}</td><td>{$row['Avg Value Per Item']}</td></tr>"; } // finish the last table echo "</table><br>"; } Quote Link to comment Share on other sites More sharing options...
Jonny125 Posted April 9, 2013 Author Share Posted April 9, 2013 That looks even better than what I had in mind, thank you so much. You sir, are a legend. Now, to try and figure out how you did it lol. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.