Jump to content

Pivot Table-like Output (indefinite rows and columns) From 'Flat Data'


harcheng

Recommended Posts

Hi guys, is there anyway to process this $result from a mysql query inside PHP so that the data below will be formatted to a pivot-like table? The number of rows and columns of the output 'table' will be indefinite. Thanks so much!

 

Data:

ID Row Col Name

1 1 A A1

2 2 A A2

3 3 A A3

4 1 B B1

5 2 B B2

6 3 B B3

7 1 C C1

8 2 C C2

9 3 C C3

 

 

Results:

A1(1) A2(2) A3(3)

B1(4) B2(5) B3(6)

C1(7) C2(8) C3(9)

Link to comment
Share on other sites

So you have a table where each record has an explicit value for the row and column? Then it's very simple:

 

$query = "SELECT * FROM table ORDER BY Row, Col";
$result = mysql_query($query);

echo "<table>\n";
$column = false;
while($row = mysql_fetch_assoc($result))
{
    //Detect change in column
    if($column != $row['Col'])
    {
        //If not first column, close the previous
        if($column != false)
        {
            echo "</tr>\n";
        }
        //Open new column
        echo "</tr>\n";
    }
    //Create data cell TD
    echo "<td>{$row['name']}({$row['id']})</td>\n";
}
//Close last row and table
echo "</tr>\n";
echo "</table>\n";

Link to comment
Share on other sites

Hey Psy, thanks for the really quick reply. I get what you mean and you're correct, that's exactly what I need. I'm trying to write it in a different way though - I don't know why this won't output a multi-row table:

	echo '<table>';
echo '<tr>';
while($row = mysql_fetch_assoc($result))
{
	if($lastrow = $row['row'])
	{
		echo '<td>'.$row['id'].'-'.$lastrow.$row['col'].'</td>';
	} else {
		echo '</tr><tr>';
	}
	$lastrow = $row['row'];
}
echo '</table>';

 

I thought that the '$lastrow = $row['row'];' line would help identify the change in the value of 'row'. Might you know what's wrong?

 

Thanks!  :D

Link to comment
Share on other sites

A little more complex but allows  for rows not having valuesfor all columns

<?php
include("testDBconnect.php");

/**
* get the column values
*/
$sql = "SELECT DISTINCT col FROM pivotsample ORDER BY col";
$res = mysql_query ($sql);
$empty = array();
while ($r = mysql_fetch_row($res)) {
    $empty[$r[0]] = ' ';
}

/**
* create table and headings
*/
echo "<table border='1' cellpadding='5'>
    <tr><th>Row</th><th>";
$heads = array_keys($empty);
echo join ('</th><th>', $heads)  . "</th></tr>\n";

/**
* get col data for each row and output
*/
$sql = "SELECT row, col, name FROM pivotsample ORDER BY row";
$res = mysql_query($sql);
$prevr = '';
while (list($r, $c, $n) = mysql_fetch_row($res))  {
    if ($lastr != $r ) {
        if ($lastr != '') {
            // output row
            echo "<tr><th>$lastr</th><td>";
            echo join('</td><td>', $rowdata) . "</td></tr>\n";
        }
        $rowdata = $empty;
        $lastr = $r;
    }
    $rowdata[$c]  = $n;
}
/**
* final row
*/
echo "<tr><th>$lastr</th><td>";
echo join('</td><td>', $rowdata) . "</td></tr>\n";
echo "</table>\n";
?>

Link to comment
Share on other sites

Oh wait, I got it! :D

Just changed the code to:

 

	echo '<table>';
echo '<tr>';
while($row = mysql_fetch_assoc($result))
{
	if($lastrow != $row['row'])
	{
		echo '</tr><tr>';
	}
	echo '<td>'.$row['id'].'-'.$row['row'].$row['col'].'</td>';
	$lastrow = $row['row'];
}
echo '</table>';

 

Thanks again!

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.