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

[Ewoud Dronkert]

Re: How do I do this query?

manny wrote:[color=blue]
> 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?[/color]

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

[Jon]

Re: How do I do this query?

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($sq l);

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

That what you were looking for?


"manny" <manny@dontspam onme.net> wrote in message
news:lvhcs1hjrv 6r4m39dnl05sbad 1cf67728p@4ax.c om...[color=blue]
>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[/color]

[Pedro Graca]

Re: How do I do this query?

["Followup-To:" header set to comp.lang.php.]
manny wrote:[color=blue]
> 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![/color]

Not sure if it's useful but, if I'm not mistaken,
Excel calls it a "pivot table".
[color=blue]
> 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?[/color]

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.
[color=blue]
> How about sql with php?[/color]

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]

Re: How do I do this query?

"manny" <manny@dontspam onme.net> wrote in message
news:lvhcs1hjrv 6r4m39dnl05sbad 1cf67728p@4ax.c om...[color=blue]
> Is there a way to do this with sql? How about sql with php?[/color]

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.

[manny]

Re: How do I do this query?

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

[Pedro Graca]

Re: How do I do this query?

Pedro Graca wrote:[color=blue]
> <?php[/color]
<snip content="old version">

Updated version

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



<?php // pivot.php
function pivotize($linea r_table, $row, $col, &$pivot_tabl e, &$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_t able[$val[$row]][$val[$col]], $val));
}
sort($columns);
}
?>

Example usage:

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

[john.d.mann@sbcglobal.net]

Re: How do I do this query?

Pedro Graca wrote:[color=blue]
> Pedro Graca wrote:
>[color=green]
>><?php[/color]
>
> <snip content="old version">
>
> Updated version[/color]

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

[Pedro Graca]

Re: How do I do this query?

john.d.mann@sbc global.net wrote:[color=blue]
> Pedro Graca wrote:[color=green]
>> Pedro Graca wrote:
>>[color=darkred]
>>><?php[/color]
>>
>> <snip content="old version">
>>
>> Updated version[/color]
>
> 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.[/color]

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 :)
[color=blue]
> However, your solution is very nice. I wanted commend you on a great,
> fleshed-out solution.[/color]

Thank you.
[color=blue]
> Maybe I'm just old-fashioned, but I would have gone for the simpler
> solution lol.[/color]

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

[Rich]

Re: How do I do this query?


"manny" <manny@dontspam onme.net> wrote in message
news:lvhcs1hjrv 6r4m39dnl05sbad 1cf67728p@4ax.c om...[color=blue]
>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[/color]

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]

Re: How do I do this query?

Rich wrote:
<snip>[color=blue]
> $sql = "SELECT DISTINCT name FROM grades order by name";
> $result = mysql_query($sq l);[/color]

1 query
[color=blue]
> if (!$result || mysql_num_rows( $result) == 0) { echo mysql_error(); exit; }[/color]
^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^
nice construct!
[color=blue]
> $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);[/color]

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)
[color=blue]
> if (!$result) { echo mysql_error(); exit; }
> while ($row = mysql_fetch_ass oc($result)) {
> $grades[$name][$row['class']] = $row['grade'];
> }
> }
> mysql_free_resu lt($result);[/color]

Why isn't this inside the foreach loop too?
[color=blue]
> print_r($grades );
> ?>
>
> prints:
> Array (
> [jill] => Array ( [chem] => b [psych] => a )
> [joe] => Array ( [bio] => a )
> [john] => Array ( [psych] => b [chem] => a )
> )[/color]

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]

Re: How do I do this query?


"Pedro Graca" <hexkid@dodgeit .com> wrote in message
news:slrndsgfi4 .gck.hexkid@ID-203069.user.ind ividual.net...

<snip>
[color=blue]
> If your table has 1000 names you'd do 1001 queries (one for the
> DISTINCT names and 1000 for the classes)[/color]

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)
[color=blue]
>
> Yes, it gets the desired results, but at a *very large* cost!
>
> <snip>[/color]

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

[Rich]

Re: How do I do this query?


"Pedro Graca" <hexkid@dodgeit .com> wrote in message
news:slrndsfj72 .gck.hexkid@ID-203069.user.ind ividual.net...
<snip>[color=blue]
> Example usage:
>
> <?php
> $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'),
> );[/color]

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

<snip>

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

[Bent Stigsen]

Re: How do I do this query?

Rich wrote:[color=blue]
> "Pedro Graca" <hexkid@dodgeit .com> wrote in message
> news:slrndsfj72 .gck.hexkid@ID-203069.user.ind ividual.net...
> <snip>
>[color=green]
>>Example usage:
>>
>><?php
>>$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'),
>>);[/color]
>
> That's not the format of the original data, how did you get the data into
> this convenient format?[/color]

Perhaps like so:
while ($row = mysql_fetch_ass oc($result)) {
$data[] = $row;
}
[color=blue]
> <snip>
>
> 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.[/color]

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

[Pedro Graca]

Re: How do I do this query?

Rich wrote:[color=blue]
>
> "Pedro Graca" <hexkid@dodgeit .com> wrote in message
> news:slrndsfj72 .gck.hexkid@ID-203069.user.ind ividual.net...
> <snip>[color=green]
>> Example usage:
>>
>> <?php
>> $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'),[/color][/color]

Extra row inserted on purpose.
[color=blue][color=green]
>> array('name'=>' jill', 'psych', 'grade'=>'a'),
>> );[/color]
>
> That's not the format of the original data, how did you get the data into
> this convenient format?[/color]

I changed the original format to show another way to call the function.
As Bent said it could be the result of mysql_fetch_ass oc()
[color=blue]
> 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.[/color]

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