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

[harcheng]

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( C3(9)

[Psycho]

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

[harcheng]

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! 

[Barand]

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

[harcheng]

Oh wait, I got it!

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!

[Barand]

It will not work unless every row has a value for every column