[SOLVED] Restructure a query???

[jtsymbo]

Bear with me, I'm new at this. I have a query that I want to display in a table format on a web page. I call up the following recordset. Name, Color and Rank are the fields. The results of the query are:

name color rank

jim orange 1

jim blue 2

jim red 3

bob brown 1

bob green 2

bob purple 3

sue gray 1

sue black 2

sue white 3

 

I want to reconfigure the above results to display in a table format as follows:

 

rank jim bob sue

1 orange brown gray

2 blue green black

3 red purple white

 

Jim, Bob, Sue and rank now become the table headings and the colors associated with those people are in rows ordered by rank.

 

I'm not sure how to approach it. Another forum recommended converting the query into an array, but after much head banging and research I'm not sure how to do it. Thanks for any help!

[Barand]

try this

<?php 
$colors = array();

$sql = "SELECT name,color,rank FROM color";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($n, $c, $r) = mysql_fetch_row($res)) {
    $colors[$n][$r] = $c;              // store in array
}

// inspect array of stored data
echo '<pre>', print_r($colors, true), '</pre>';

// output the results
echo "<table border ='1'>";
echo "<tr><th>Rank</th>";
// heading row
foreach ($colors as $name => $v) {
    echo "<th>$name</th>";
}
echo "</tr>";

for ($r=1; $r<=3; $r++) {
    echo "<tr><th>$r</th>";
    foreach ($colors as $prefs) {
        echo "<td>$prefs[$r]</td>";
    }
    echo "</tr>";
}
echo "</table>";
?>

[jtsymbo]

Barand, you are the man! Boy, am I glad I found this site. You don't know how long this has stumped me. I'm a newbie, so I'll have to study your code to understand how it works . Thanks again!!