Posted by Rich on 01/14/06 00:52

"manny" <manny@dontspamonme.net> wrote in message
news:lvhcs1hjrv6r4m39dnl05sbad1cf67728p@4ax.com...
>I wish I knew what this was called, and I could have made a more sensible
> subject. For that matter, I knew what it was called, I could have googled
> it!
>
> Anyway, let's I have a table with name, class, grade.
>
> joe bio a
> jill chem b
> john psych b
> john chem a
> jill pysch a
>
> and I want to display the results as
>
> name bio psych chem
> joe a - -
> jill - a b
> john - b a
>
> Is there a way to do this with sql? How about sql with php?
>
> Thanks much!
>
> Manny

You can create an associative array in PHP then use the values as you want,
ie. print them to a file or an HTML table etc.

note: the example code needs more/better error checking, comments
<?
// assumes a mysql db with a "grades" table containing name, class, and
grade columns

$conn = mysql_connect("localhost", "my_mysql_username",
"my_mysql_password");
if (!$conn || !mysql_select_db("my_mysql_db")) { echo mysql_error(); exit; }

$sql = "SELECT DISTINCT name FROM grades order by name";
$result = mysql_query($sql);

if (!$result || mysql_num_rows($result) == 0) { echo mysql_error(); exit; }
$grades = array();
while ($row = mysql_fetch_row($result)) {
$grades[$row[0]] = array();
}
mysql_free_result($result);

foreach (array_keys($grades) as $name) {
$sql = "SELECT class, grade FROM grades where name=\"$name\"";
$result = mysql_query($sql);
if (!$result) { echo mysql_error(); exit; }
while ($row = mysql_fetch_assoc($result)) {
$grades[$name][$row['class']] = $row['grade'];
}
}
mysql_free_result($result);

print_r($grades);
?>

prints:
Array (
[jill] => Array ( [chem] => b [psych] => a )
[joe] => Array ( [bio] => a )
[john] => Array ( [psych] => b [chem] => a )
)

You could also define defaults:

$classes = array('chem', 'bio', 'psych');

foreach ($classes as $class) {
foreach (array_keys($grades) as $name) {
$grades[$name][$class] = (
isset($grades[$name][$class])
&&
preg_match("/^(?:[a-f]-?|-)$/", $grades[$name][$class])
)
? $grades[$name][$class] : '-' ;
ksort($grades[$name]);
}}

print_r($grades);

Prints:

Array (
[jill] => Array ( [bio] => - [chem] => b [psych] => a )
[joe] => Array ( [bio] => a [chem] => - [psych] => - )
[john] => Array ( [bio] => - [chem] => a [psych] => b )
)


Rich

[Back to original message]