Jump to content

Display data in columns instead of rows?


Jonny125
Go to solution Solved by mac_gyver,

Recommended Posts

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:

showtablerows.jpgThis 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>";
};



?>

 

 

Link to comment
Share on other sites

  • Solution

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>";
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.