Display data in columns instead of rows?

[Jonny125]

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>";
};



?>

 

 

[mac_gyver]

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>";

}

[Jonny125]

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.