How do I do this query?

am 12.01.2006 13:28:34 von 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

Re: How do I do this query?

am 12.01.2006 13:37:52 von Ewoud Dronkert

manny wrote:
> 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?

Create new table (name=key), insert old data in new table using "insert
.... select" and "on duplicate" clause, see
http://dev.mysql.com/doc/refman/4.1/en/insert.html

--
E. Dronkert

Re: How do I do this query?

am 12.01.2006 15:58:31 von Thomas Bartkus

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

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

Re: How do I do this query?

am 12.01.2006 16:01:17 von Thomas Bartkus

Re: How do I do this query?

am 12.01.2006 16:24:15 von Jon

To display it is going to be fairly easy with PHP and MySQL. Here's the
algorithm I'd use:

$sql = "SELECT * FROM table_name";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result)){
echo your table structure along with each field you need displayed
}

That what you were looking for?

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

Re: How do I do this query?

am 12.01.2006 18:17:07 von 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.database.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 ...

$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_data[$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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 12.01.2006 18:17:07 von Pedro Graca

Re: How do I do this query?

am 12.01.2006 18:57:10 von Bill Karwin

"manny" wrote in message
news:lvhcs1hjrv6r4m39dnl05sbad1cf67728p@4ax.com...
> 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/Programming:MySQL/Pivot_table
http://www.onlamp.com/pub/a/onlamp/2003/12/04/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.

Re: How do I do this query?

am 12.01.2006 18:57:10 von Bill Karwin

Re: How do I do this query?

am 13.01.2006 15:02:08 von 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

Re: How do I do this query?

am 13.01.2006 15:02:08 von Manny

Re: How do I do this query?

am 13.01.2006 17:17:03 von Pedro Graca

Pedro Graca wrote:
>

Updated version

Changes:
+ isolated the code inside a function
+ a lot more things :)

function pivotize($linear_table, $row, $col, &$pivot_table, &$columns, $callback) {
foreach ($linear_table as $val) {
if (!in_array($val[$col], $columns)) $columns[] = $val[$col];
if (!isset($pivot_table[$val[$row]][$val[$col]])) {
$pivot_table[$val[$row]][$val[$col]] = '';
}
call_user_func_array($callback, array(&$pivot_table[$val[$row]][$val[$col]], $val));
}
sort($columns);
}
?>

Example usage:

$data = array(
array('name'=>'joe', 'bio', 'grade'=>'a'),
array('name'=>'jill', 'chem', 'grade'=>'b'),
array('name'=>'john', 'psych', 'grade'=>'b'),
array('name'=>'john', 'chem', 'grade'=>'a'),
array('name'=>'joe', 'bio', 'grade'=>'a'),
array('name'=>'jill', 'psych', 'grade'=>'a'),
);

header('Content-Type: text/plain');

require_once 'pivot.php';
$new_data = array();
$classes = array();
pivotize($data, 'name', 0, $new_data, $classes, create_function('$x,$y', '$x.=$y[\'grade\'];'));

echo "name\t";
echo implode("\t", $classes);
echo "\n";

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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 13.01.2006 18:26:51 von john.d.mann

Pedro Graca wrote:
> Pedro Graca wrote:
>
>> >
>
>
> Updated version

Sorry to chime in here, but I just don't get why this is such a
complicated task. I could have the same result in far less code - and
even much less complex code.

However, your solution is very nice. I wanted commend you on a great,
fleshed-out solution.

Maybe I'm just old-fashioned, but I would have gone for the simpler
solution lol.

John D. Mann

Re: How do I do this query?

am 13.01.2006 19:17:05 von Pedro Graca

john.d.mann@sbcglobal.net wrote:
> Pedro Graca wrote:
>> Pedro Graca wrote:
>>
>>> >>
>>
>>
>> Updated version
>
> Sorry to chime in here, but I just don't get why this is such a
> complicated task. I could have the same result in far less code - and
> even much less complex code.

The first version was hard coded to the desired result.
What if, "out of the blue", you wanted to reverse the final table:

instead of you'd like

name bio psych chem grade joe jill john
joe a - - bio a - -
jill - a b psych - a b
john - b a chem - b a

If you got stuck with the simple version you'd have to change it (and
lose the original) or (worse???) make a copy to do about exactly the
same thing.

With the new version, just change the parameters to the function :)

> However, your solution is very nice. I wanted commend you on a great,
> fleshed-out solution.

Thank you.

> Maybe I'm just old-fashioned, but I would have gone for the simpler
> solution lol.

That's a valid point of vue :)
I tend to try and make functions (the ones I think deserve it) work for
everything you can throw at them.

If I need to make a pivot table next month about something, I just
include "pivot.php" and call the function. Hopefully it will work just
like expected on the first run.

As it stands now, it just needs a bit (a lot) more documentation, either
in comments or in a separate file.

--
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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 13.01.2006 23:52:01 von Rich

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

Re: How do I do this query?

am 13.01.2006 23:52:01 von Rich

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

Re: How do I do this query?

am 14.01.2006 01:17:02 von Pedro Graca

Rich wrote:

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

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_result($result);
>
> foreach (array_keys($grades) as $name) {
> $sql = "SELECT class, grade FROM grades where name=\"$name\"";
> $result = mysql_query($sql);

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_assoc($result)) {
> $grades[$name][$row['class']] = $row['grade'];
> }
> }
> mysql_free_result($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!

--
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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 14.01.2006 01:17:02 von Pedro Graca

Re: How do I do this query?

am 14.01.2006 07:07:47 von Rich

"Pedro Graca" wrote in message
news:slrndsgfi4.gck.hexkid@ID-203069.user.individual.net...

> 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!
>
>

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

Re: How do I do this query?

am 14.01.2006 07:07:47 von Rich

Re: How do I do this query?

am 14.01.2006 07:44:37 von Rich

"Pedro Graca" wrote in message
news:slrndsfj72.gck.hexkid@ID-203069.user.individual.net...

> Example usage:
>
> > $data = array(
> array('name'=>'joe', 'bio', 'grade'=>'a'),
> array('name'=>'jill', 'chem', 'grade'=>'b'),
> array('name'=>'john', 'psych', 'grade'=>'b'),
> array('name'=>'john', 'chem', 'grade'=>'a'),
> array('name'=>'joe', 'bio', 'grade'=>'a'),
> array('name'=>'jill', 'psych', 'grade'=>'a'),
> );

That's not the format of the original data, how did you get the data into
this convenient format?

prints:

name bio chem psych
joe aa - -
jill - b a
john - a b

Joe got an 'aa'?

Looks like you forgot to account for duplicate rows.

Rich

Re: How do I do this query?

am 15.01.2006 10:41:57 von Bent Stigsen

Rich wrote:
> "Pedro Graca" wrote in message
> news:slrndsfj72.gck.hexkid@ID-203069.user.individual.net...
>
>
>>Example usage:
>>
>> >>$data = array(
>> array('name'=>'joe', 'bio', 'grade'=>'a'),
>> array('name'=>'jill', 'chem', 'grade'=>'b'),
>> array('name'=>'john', 'psych', 'grade'=>'b'),
>> array('name'=>'john', 'chem', 'grade'=>'a'),
>> array('name'=>'joe', 'bio', 'grade'=>'a'),
>> array('name'=>'jill', 'psych', 'grade'=>'a'),
>>);
>
> That's not the format of the original data, how did you get the data into
> this convenient format?

Perhaps like so:
while ($row = mysql_fetch_assoc($result)) {
$data[] = $row;
}

>
>
> prints:
>
> name bio chem psych
> joe aa - -
> jill - b a
> john - a b
>
> Joe got an 'aa'?
>
> Looks like you forgot to account for duplicate rows.

Naaa, not his problem. If duplicate rows is a problem, then it is
either, a job better done by the database, or a data integrity
problem, which shouldn't be painted over by a hack in an application.

/Bent

Re: How do I do this query?

am 15.01.2006 22:17:03 von Pedro Graca

Rich wrote:
>
> "Pedro Graca" wrote in message
> news:slrndsfj72.gck.hexkid@ID-203069.user.individual.net...
>
>> Example usage:
>>
>> >> $data = array(
>> array('name'=>'joe', 'bio', 'grade'=>'a'),
>> array('name'=>'jill', 'chem', 'grade'=>'b'),
>> array('name'=>'john', 'psych', 'grade'=>'b'),
>> array('name'=>'john', 'chem', 'grade'=>'a'),
>> array('name'=>'joe', 'bio', 'grade'=>'a'),

Extra row inserted on purpose.

>> array('name'=>'jill', 'psych', 'grade'=>'a'),
>> );
>
> That's not the format of the original data, how did you get the data into
> this convenient format?

I changed the original format to show another way to call the function.
As Bent said it could be the result of mysql_fetch_assoc()

> prints:
>
> name bio chem psych
> joe aa - -
> jill - b a
> john - a b
>
> Joe got an 'aa'?
>
> Looks like you forgot to account for duplicate rows.

If I wanted to ignore duplicate rows, I'd change the callback function.
Instead of concatening values I'd do a simple assignment ...

--
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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 15.01.2006 22:17:06 von Pedro Graca

Rich wrote:
>
> "Pedro Graca" wrote in message
> news:slrndsgfi4.gck.hexkid@ID-203069.user.individual.net...
>
>
>
>> 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!
>>
>>
>
> 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" )
may bypass my spam filter. If it does, I may reply from another address!

Re: How do I do this query?

am 15.01.2006 22:17:06 von Pedro Graca

Re: How do I do this query?

am 16.01.2006 01:28:14 von Jerry Stuckle

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!
>
> 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

(Not tested)

Create an associative array (assuming you don't have two different joe's:

$result = mysql_query('SELECT name, class, grade FROM mytable');
if ($result) {
$grades = array();
while (list($name, $class, $grade) = mysql_fetch_row($result)) {
if (!exists($grades[$name])
$grades[$name] = array();
$grades[$name][$class] = $grade;
}
}
else
echo "MySQL error: " . mysql_error() . "\n";

Creates an array $grades with:

joe => (bio=>a)
jill=> (chem=>b, pysch=>a)
john=> (psych=>b, chem=>a)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: How do I do this query?

am 16.01.2006 01:28:14 von Jerry Stuckle

Re: How do I do this query?

am 17.01.2006 04:29:00 von 4partee

On Sun, 15 Jan 2006 19:28:14 -0500, Jerry Stuckle wrote:

> 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!
>>
>> 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
>

SELECT name, IF(class = 'bio', grade,'-') AS `bio`
, IF(class = 'psych', grade,'-') AS `psych`
, IF(class = 'chem', grade,'-') AS `chem`
from tablex

Actually, mysql really sucks at this!

John

Re: How do I do this query?

am 17.01.2006 04:29:00 von 4partee

Re: How do I do this query?

am 17.01.2006 04:44:08 von 4partee

create table x
> SELECT name, IF(class = 'bio', grade,'-') AS `bio`
> , IF(class = 'psych', grade,'-') AS `psych`
> , IF(class = 'chem', grade,'-') AS `chem`
> from tablex
>
>

select a.name, a.bio, b.psych, c.chem from x as a join x as b using(name)
join x as c using(name)

Re: How do I do this query?

am 17.01.2006 04:44:08 von 4partee

Re: How do I do this query?

am 18.01.2006 23:32:51 von Thomas Bartkus

"4partee" wrote in message
news:pan.2006.01.17.03.29.00.116088@voyager.starfleet...
> On Sun, 15 Jan 2006 19:28:14 -0500, Jerry Stuckle wrote:
>
> > 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!
> >>
> >> 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
> >
>
> SELECT name, IF(class = 'bio', grade,'-') AS `bio`
> , IF(class = 'psych', grade,'-') AS `psych`
> , IF(class = 'chem', grade,'-') AS `chem`
> from tablex
>
> Actually, mysql really sucks at this!

Actually, it's standard that SQL sucks at this.
You can't do the above unless the list of discreet values in [class] is
short and static.

Giving the devil his due - the Microsoft JET flavor of SQL has GROUP with
PIVOT that makes short work out of problems like this. I *think* Oracle has
similar extensions.

Wish we had that in MySQL :-(
Thomas Bartkus

Re: How do I do this query?

am 18.01.2006 23:32:51 von Thomas Bartkus

Re: How do I do this query?

am 19.01.2006 03:56:12 von 4partee

On Wed, 18 Jan 2006 16:32:51 -0600, Thomas Bartkus wrote:

> "4partee" wrote in message
> news:pan.2006.01.17.03.29.00.116088@voyager.starfleet...
>> On Sun, 15 Jan 2006 19:28:14 -0500, Jerry Stuckle wrote:
>>
>> > 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!
>> >>
>> >> 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
>> >
>>
>> SELECT name, IF(class = 'bio', grade,'-') AS `bio`
>> , IF(class = 'psych', grade,'-') AS `psych`
>> , IF(class = 'chem', grade,'-') AS `chem`
>> from tablex
>>
>> Actually, mysql really sucks at this!
>
> Actually, it's standard that SQL sucks at this.
> You can't do the above unless the list of discreet values in [class] is
> short and static.
>
> Giving the devil his due - the Microsoft JET flavor of SQL has GROUP with
> PIVOT that makes short work out of problems like this. I *think* Oracle has
> similar extensions.

Agree. MS Access is a good tool for ad-hoc reporting. I moved to LAMP
about 3 years ago. Not going back.

>
> Wish we had that in MySQL :-(
> Thomas Bartkus

The SQL could be generated, and executed' in line in PHP by looping
through a 'select distinct class'.....

This includes the rest of the solution that I posted in a followup post.

John

Re: How do I do this query?

am 19.01.2006 03:56:12 von 4partee