How do I do this query?

[manny]

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
--
manny@don't spam on me

[Thomas Bartkus]

"manny" <ma***@dontspam onme.net> wrote in message
news:lv******** *************** *********@4ax.c om...

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?

This is called a "pivot" table. Your data row [class] becomes multiple
columns, one for each discrete data value contained in your original
[class].

And, yes, it can be done in MySQL flavor of sql although it is awkward.
Google on "pivot" and "mysql".
The query can be easy if you have a small fixed list of [class] values which
your example suggests might be the case. True pivot table facilities (which
MySQL lacks!) can manage arbitrary data in the pivot field.

The body of your "pivot" table - the [grade]s - needs to be aggregated in
some way. Each each [name]/[class] intersection can only contain a single
value. One might assume, for example, that Jill will have several bio
grades and these need to be summarized. You would show a single grade
average (or max, or min) value.

It would probably be easier to an initial query and then re-arrange the
table with php.

What os environment are you using? Interestingly, Microsoft Jet flavor of
sql has a PIVOT command that is perfect for this. This means that if you
can create a linked MySQL table in Access you can then avail yourself to
this facility. We frequently use Excel as a front end to MySQL and get to
use Excel's very excellent pivot table function with an initial query
against MySQL.

But, alas! AFAIK - MySQL doesn't have PIVOT which means you have to work
harder at it.

Someone can shout back at me if I am missing something.
Thomas Bartkus

[Thomas Bartkus]

"manny" <ma***@dontspam onme.net> wrote in message
news:lv******** *************** *********@4ax.c om...

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?

I almost forgat "crosstab" table!
Both "pivot" and "crosstab" are used to describe what you are trying to
achieve.

Thomas Bartkus

[Pedro Graca]

["Followup-To:" header set to comp.lang.php.]
manny wrote:

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!
Not sure if it's useful but, if I'm not mistaken,
Excel calls it a "pivot table".
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?
I don't know. Maybe someone on mailing.databas e.mysql will answer you.
.... you may want to try posting to comp.databases. mysql or some
newsgroup about sql in general.
Followups set to comp.lang.php because my answer is strictly php.
How about sql with php?

Easy :)
With no need for sql at all.
Hope I'm not doing your homework ...


<?php
$data = array(
array('joe', 'bio', 'a'),
array('jill', 'chem', 'b'),
array('john', 'psych', 'b'),
array('john', 'chem', 'a'),
array('jill', 'psych', 'a'),
); /* could come from a database */

/* restructure data and get classes */
$new_data = array();
$classes = array();
foreach ($data as $val) {
if (!in_array($val[1], $classes)) $classes[] = $val[1];
if (isset($new_dat a[$val[0]][$val[1]])) {
$new_data[$val[0]][$val[1]] .= $val[2];
} else {
$new_data[$val[0]][$val[1]] = $val[2];
}
}
sort($classes);

header('Content-Type: text/plain'); /* I'm lazy */

/* print header */
echo "name\t";
echo implode("\t", $classes);
echo "\n";

/* print data rows */
foreach ($new_data as $k=>$v) {
echo $k, "\t";
foreach ($classes as $class) {
echo isset($v[$class])?$v[$class]:'-', "\t";
}
echo "\n";
}
?>

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[Bill Karwin]

"manny" <ma***@dontspam onme.net> wrote in message
news:lv******** *************** *********@4ax.c om...

Is there a way to do this with sql? How about sql with php?

I've seen a few articles on achieving a pivot table result with MySQL, and
they made my head hurt.
http://www.wellho.net/mouth/59_MySQL-Pivot-tables.html
http://en.wikibooks.org/wiki/Program...QL/Pivot_table
http://www.onlamp.com/pub/a/onlamp/2...crosstabs.html

Joe Celko's "SQL for Smarties" also gives several options for producing
crosstabs purely in SQL.

In some cases, it may be the quickest solution to get the raw data out of
the database and into your application code, and then massage it until you
get the result you want.

Regards,
Bill K.

[manny]

Pedro Graca, Bill Karwin, Thomas Bartkus, and others wrote:

....various solutions and ideas.

Thanks to all. I've used pivot tables/crosstabs in Excel before, but somehow
didn't connect that this was what I wanted out of my sql query. I need to do
this directly on the server and not with results downloaded/manipulated in
Excel.

BTW, Pedro's Php code works great and that will probably be the method that I
use.

Again, much appreciation.

Manny

--
manny@don't spam on me

[Rich]

"manny" <ma***@dontspam onme.net> wrote in message
news:lv******** *************** *********@4ax.c om...

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_usern ame",
"my_mysql_passw ord");
if (!$conn || !mysql_select_d b("my_mysql_db" )) { echo mysql_error(); exit; }

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

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_resu lt($result);

foreach (array_keys($gr ades) as $name) {
$sql = "SELECT class, grade FROM grades where name=\"$name\"" ;
$result = mysql_query($sq l);
if (!$result) { echo mysql_error(); exit; }
while ($row = mysql_fetch_ass oc($result)) {
$grades[$name][$row['class']] = $row['grade'];
}
}
mysql_free_resu lt($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($gr ades) 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

[Pedro Graca]

Rich wrote:
<snip>

$sql = "SELECT DISTINCT name FROM grades order by name";
$result = mysql_query($sq l);
1 query
if (!$result || mysql_num_rows( $result) == 0) { echo mysql_error(); exit; } ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^
nice construct!
$grades = array();
while ($row = mysql_fetch_row ($result)) {
$grades[$row[0]] = array();
}
mysql_free_resu lt($result);

foreach (array_keys($gr ades) as $name) {
$sql = "SELECT class, grade FROM grades where name=\"$name\"" ;
$result = mysql_query($sq l);
a whole bunch of queries!!!
Don't do this if possible.
Avoid doing queries inside loops.

If your table has 1000 names you'd do 1001 queries (one for the
DISTINCT names and 1000 for the classes)
if (!$result) { echo mysql_error(); exit; }
while ($row = mysql_fetch_ass oc($result)) {
$grades[$name][$row['class']] = $row['grade'];
}
}
mysql_free_resu lt($result);
Why isn't this inside the foreach loop too?
print_r($grades );
?>

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

Yes, it gets the desired results, but at a *very large* cost!

<snip>

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[Rich]

"Pedro Graca" <he****@dodgeit .com> wrote in message
news:sl******** ***********@ID-203069.user.ind ividual.net...

<snip>

If your table has 1000 names you'd do 1001 queries (one for the
DISTINCT names and 1000 for the classes)
You're right. If there were 1000 names you should loop through the results
of 1 query. But there were only 5.

If there were 1000 names, the structure of the table given would make even
less sense (if that's possible)

Yes, it gets the desired results, but at a *very large* cost!

<snip>

I took a few minutes out of my busy day to give the OP some *ideas* on how
to handle his problem, not do it for him. I never stated that it was
production-quality code. It was a hack to deal with an improperly formatted
table of data.

It took all of 5-10 minutes to cut and paste, was very ugly, and there were
a lot of things wrong with the code but it worked and it showed the results
that *could* be accomplished.

Feel free to optimize, test, debug, and repost, as you (apparently) have
more free time on your hands than I.

Thanks for the critique,

Rich

[Pedro Graca]

Rich wrote:

"Pedro Graca" <he****@dodgeit .com> wrote in message
news:sl******** ***********@ID-203069.user.ind ividual.net...

<snip>

If your table has 1000 names you'd do 1001 queries (one for the
DISTINCT names and 1000 for the classes)

You're right. If there were 1000 names you should loop through the results
of 1 query. But there were only 5.

Usually people post a stripped-down example of their problem.
Just because the example posted by the OP contained 5 rows, that's no
reason to assume the real problem happens only with a small amount of
data.

Yes, it gets the desired results, but at a *very large* cost!

<snip>

I took a few minutes out of my busy day to give the OP some *ideas* on how
to handle his problem, not do it for him. I never stated that it was
production-quality code. It was a hack to deal with an improperly formatted
table of data.

It took all of 5-10 minutes to cut and paste, was very ugly, and there were
a lot of things wrong with the code but it worked and it showed the results
that *could* be accomplished.

Feel free to optimize, test, debug, and repost, as you (apparently) have
more free time on your hands than I.

Thanks for the critique,

Don't take my criticism personally, I never intended it that way.
I can't speak for anyone else on these groups, but I'm sure all of us
appreciate your posts.
--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!