reconfigure a select 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
Bob-- Red ---1
Sue --Blue ---1
Jim--Yellow --2
Bob- Green --2
Sue -Purple-- 2

I want to reconfigure the above results to display in a table format as follows:
Rank- Jim -Bob -Sue
1 - Orange--Red- --Blue
2 --Yellow -Green- Purple

Notice that I want the individual names to be the column headings.

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!

[bradgrafelman]

As the other form suggests, an array is your best bet here. I would say do something like this when you're looping through the SQL result set to build the array:

$query = 'SELECT name, color, rank FROM myTable';
$exec = mysql_query($query);
$data = $names = array();
while($result = mysql_fetch_assoc($exec)) {
    $data[$result['rank']][$result['name']] = $result['color'];
    $names[] = $result['name'];
}

Then, you'd do something like this to build the first row of names:

echo '<tr>';
foreach($names as $name)
    echo '<td>' . $name . '</td>';
echo '</tr>';

And then something like this to build the rest of the rows:

foreach($data as $rank => $array) {
    echo '<tr><td>' . $rank . '</td>';
    foreach($names as $name)
        echo '<td>' . $array[$name] . '</td>';
    echo '</tr>';
}

Yeah, the HTML isn't pretty and the code is untested, but in my head it all seems to work! :p

[jtsymbo]

Thanks Bradgrafelman. Like I said I'm just learning, but this gets me started in the right direction. I'll give it a shot and I'll report back. Much gratitude.
jtsymbo

[jtsymbo]

Tried the above code and this is what I get:
jim--jim--jim
bob
bob
bob
sue
sue
sue
123
Any other ideas? Thanks

[damienmcd]

I had the same sort of problem and it worked with:

// sending query
$result = mysql_query("SELECT Name, Color, rank FROM table_name");
if (!$result) {
die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1' align='center'><tr>";

// printing table headers
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";

// $row is array... foreach( .. ) puts every element
// of $row to $cell variable

foreach($row as $cell)
    print "<td>$cell</td>";
echo "</tr>\n";

}

/ GOOD LUCK!! /:p

[jtsymbo]

Thanks for the reply Damienmcd. I am still unable to get it to work. Heres my code. I think I typed it in just as directed:

mysql_select_db($database_colors, $colors);
$result = mysql_query("SELECT name, color, rank FROM colors");
if (!$result) {
die("Query to show fields from table failed");
}

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";
echo "<table border='1' align='center'><tr>";

// printing table headers
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<td>{$field->name}</td>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";

// $row is array... foreach( .. ) puts every element
// of $row to $cell variable

foreach($row as $cell)
print "<td>$cell</td>";
echo "</tr>\n";
}


?>

And heres the output, all on one row:

namecolorrank jimorange1 jimyellow2 bobred1 bobgreen2 sueblue1 suepurple2

Where am I going wrong? I'm tearing my hair out, and there's not much to tear out. Thanks for any help!

[bpat1434]

Have you tried viewing the source? Does it show printing the HTML table, or not?

By viewing the source I mean looking at the HTML source of the PHP rendered page.

[jtsymbo]

Thanks for all your help. My problem was that I was looking at the output through Dreamweaver Live, but when I view the ouput through a browser, this is now the output that I'm getting:

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

Were getting close, but this is what I'm looking for ::

Rank-------Jim---------Bob---------Sue
1---------orange------brown--------gray
2---------blue----------green---------black
3---------red-----------purple--------white

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

Also, is there any way to copy the html from the browser source and paste it into this thread box so viewers can actually see the table as it is outputed?

Thanks for any help

[damienmcd]

Looks like u should prob recreate your table and as each new user is added then update the table accordingly.

once that's done it should be easy enough to do the rest, if needs be I'll be back on to you when you have that done

Good Luck!:p