Re: grouping query results

Hello everyone,

In a PHP / Postgresql application I have to make a lot of lists like
this:

THE DATA STRUCTURE
employee_number, employee_name, department

THE OUTPUT
------------------------------------
employee_number employee_name

department X
1 Johnson
22 Jackson
subtotal: 2 employees

department Y
222 Smith
3 Doe
44 Jameson
subtotal: 3 employees

grand total: 5 employees
------------------------------------

I see 2 ways to solve this:

- use SELECT for the detail lines and SELECT COUNT (*) for the (sub)
totals

- SELECT the whole lot and let PHP do the grouping and counting

The second solution seems old fashioned to me and the first has a
catch: all queries have to be made within a single transaction.

I have the feeling that this is a very common question, how do you
people handle it? I looked at a lot of script sites, but no luck.

Thanks for any ideas / feedback!

On Mon, 2005-02-28 at 10:48 +0100, Joolz wrote:
> Hello everyone,
>
> In a PHP / Postgresql application I have to make a lot of lists like
> this:
>
> THE DATA STRUCTURE
> employee_number, employee_name, department
>
> THE OUTPUT
> ------------------------------------
> employee_number employee_name
>
> department X
> 1 Johnson
> 22 Jackson
> subtotal: 2 employees
>
> department Y
> 222 Smith
> 3 Doe
> 44 Jameson
> subtotal: 3 employees
>
> grand total: 5 employees
> ------------------------------------
>
> I see 2 ways to solve this:
>
> - use SELECT for the detail lines and SELECT COUNT (*) for the (sub)
> totals
>
> - SELECT the whole lot and let PHP do the grouping and counting
>
> The second solution seems old fashioned to me and the first has a
> catch: all queries have to be made within a single transaction.
>
> I have the feeling that this is a very common question, how do you
> people handle it? I looked at a lot of script sites, but no luck.

I would tend to use the second solution purely for performance reasons
since the first solution will require a select plus one select per
department, which won't scale well to lots of departments.

function print_total( $label, $total ) {
echo "%s: %d employees";
}

SELECT employee_number, employee_name, department FROM xxx ORDER BY
department

$gtotal = 0;
$dtotal = 0;
$last_department = "no department";
for( $i=0 $i < rows; $row = pg_Fetch_Object(..., $i) {
if ( $row->department != $last_department ) {
if ( $i > 0 ) {
print_total( "subtotal", $dtotal );
}
$dtotal = 0;
$last_department = $row->department;
}
printf( "%5d %s", $row->employee_number, $row->employee_name );
$dtotal++;
$gtotal++;
}

print_total( "subtotal", $dtotal );
print_total( "grand total", $gtotal );

Cheers,
Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
What are they doing now? http://schoolreunions.co.nz/
-------------------------------------------------------------------------

On Mon, 28 Feb 2005 10:48:23 +0100 (CET), Joolz
<joolz(at)arbodienst-limburg(dot)nl> wrote:
>
> I have the feeling that this is a very common question, how do you
> people handle it? I looked at a lot of script sites, but no luck.

What I do is run a single query for all employees, then iterate over
the array in PHP for the counts.

The alternative of running several queries seems a bit heavy-handed to
me, but that is because I am always trying to make life easier for the
database server :-)

Note that many database libraries (PEAR::DB, ADOdb) also have
capabilities of returning counts of recordsets, so with two queries
you would be done. But I still like the first approach, as it is not
tied to the database or require external libraries to support.

-- Mitch

On Mon, Feb 28, 2005 at 10:48:23 +0100,
Joolz <joolz(at)arbodienst-limburg(dot)nl> wrote:
> Hello everyone,
>
> In a PHP / Postgresql application I have to make a lot of lists like
> this:
>
> THE DATA STRUCTURE
> employee_number, employee_name, department
>
> THE OUTPUT
> ------------------------------------
> employee_number employee_name
>
> department X
> 1 Johnson
> 22 Jackson
> subtotal: 2 employees
>
> department Y
> 222 Smith
> 3 Doe
> 44 Jameson
> subtotal: 3 employees
>
> grand total: 5 employees
> ------------------------------------
>
> I see 2 ways to solve this:
>
> - use SELECT for the detail lines and SELECT COUNT (*) for the (sub)
> totals
>
> - SELECT the whole lot and let PHP do the grouping and counting

You can use ORDER BY to do most of the grouping work. The app just
needs to check when the department changes and keep counters.

> The second solution seems old fashioned to me and the first has a
> catch: all queries have to be made within a single transaction.

I think more than that. I think you also want the transaction isolation
level set to serializeable if you want a consistant report.

> I have the feeling that this is a very common question, how do you
> people handle it? I looked at a lot of script sites, but no luck.

I would suggest method 2 using an ORDER BY to produce the detail lines
in the correct order.

i'm running php with postgresql. i just finished
polishing off a pretty long query sequence. my page
returns a blank page.

how do i turn on error reporting when running php as a
cgi on my development xp / cygwin box?

i tried including...

ini_set('error_reporting', E_ALL);

and then i tried including...

error_reporting(E_ALL);

no error was reported - just a blank screen.

i'm sure it is a simple oversite, but it is so much
easier when php tell you where it breaks than to have
to find waldo by searching the entire script.

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

i also have the following set in my php.ini file...

display_errors = On

error_reporting = ~E_ALL

still no error is being reported...

--- operationsengineer1(at)yahoo(dot)com wrote:

> i'm running php with postgresql. i just finished
> polishing off a pretty long query sequence. my page
> returns a blank page.
>
> how do i turn on error reporting when running php as
> a
> cgi on my development xp / cygwin box?
>
> i tried including...
>
> ini_set('error_reporting', E_ALL);
>
> and then i tried including...
>
> error_reporting(E_ALL);
>
> no error was reported - just a blank screen.
>
> i'm sure it is a simple oversite, but it is so much
> easier when php tell you where it breaks than to
> have
> to find waldo by searching the entire script.
>
> tia...
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Tue, 1 Mar 2005 11:27:15 -0800 (PST), operationsengineer1(at)yahoo(dot)com
<operationsengineer1(at)yahoo(dot)com> wrote:
> i also have the following set in my php.ini file...
>
> display_errors = On
>
> error_reporting = ~E_ALL

Doesn't the tilde mean 'everything but E_ALL'?

-- Mitch

--- Mitch Pirtle <mitch(dot)pirtle(at)gmail(dot)com> wrote:

> On Tue, 1 Mar 2005 11:27:15 -0800 (PST),
> operationsengineer1(at)yahoo(dot)com
> <operationsengineer1(at)yahoo(dot)com> wrote:
> > i also have the following set in my php.ini
> file...
> >
> > display_errors = On
> >
> > error_reporting = ~E_ALL
>
> Doesn't the tilde mean 'everything but E_ALL'?
>
> -- Mitch

could be - i had it both with and without the tilde
and still come up with nothing. i have to upload the
file to my online webserver and run it in order to get
error messages. i can't win for losing on this one.

i'm wondering is something special has to be done for
a cgi and/or windowsxp and/or cygwin install that
isn't required for another type of install.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

I use Gyozo Papp's error handler class. Very useful because you can set
errors to go to a pop-up window or to a log depending on where you are in
the testing process.

http://freshmeat.net/projects/errorhandler/

It hasn't been updated in a couple years, but don't let that put you off.
It works just fine.

Thom Dyson
Director of Information Services
Sybex, Inc.

pgsql-php-owner(at)postgresql(dot)org wrote on 03/01/2005 11:14:35 AM:

> i'm running php with postgresql. i just finished
> polishing off a pretty long query sequence. my page
> returns a blank page.

> how do i turn on error reporting when running php as a
> cgi on my development xp / cygwin box?

You want:
error_reporting(E_ALL);
ini_set("display_errors", "1");

but first check the ini file with phpinfo()
what happens if you throw in an error, such as a syntax error?

graeme.

Thom Dyson wrote:

>I use Gyozo Papp's error handler class. Very useful because you can set
>errors to go to a pop-up window or to a log depending on where you are in
>the testing process.
>
>http://freshmeat.net/projects/errorhandler/
>
>It hasn't been updated in a couple years, but don't let that put you off.
>It works just fine.
>
>Thom Dyson
>Director of Information Services
>Sybex, Inc.
>
>
>
>pgsql-php-owner(at)postgresql(dot)org wrote on 03/01/2005 11:14:35 AM:
>
>
>
>>i'm running php with postgresql. i just finished
>>polishing off a pretty long query sequence. my page
>>returns a blank page.
>>
>>
>
>
>
>>how do i turn on error reporting when running php as a
>>cgi on my development xp / cygwin box?
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
>

--
Experience is a good teacher, but she sends in terrific bills.

Minna Antrim

I am trying to run the following code but get the following error:

Fatal error: Call to undefined function show_title() in
d:\webhost\build\index.php on line 13

This file is called "index.php"

<? include "dynamic.php"; ?>
<? if (empty($id)) $id = "Home"; ?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title> <? show_title($id); ?> </title>
</head>
<body>
<? show_navigation($id); ?>
<? show_content($id); ?>
</body>
</html>

The other file "dynamic.php" has the following functions

/* dynamic.inc.php */

function show_title($id)
{
$fp = fopen(get_filename($id), "r");
if (!$fp) return;
$line = trim(fgets($fp, 255));
fclose($fp);
echo $line;
}

function get_filename($id)
{
$name = "file_$id.txt";
if (file_exists($name))
{
return $name;
} else {
return "error.txt";
}
}

function show_navigation($id)
{
global $PHP_SELF, $SCRIPT_NAME;
if (trim($PHP_SELF) == "") $PHP_SELF = $SCRIPT_NAME;
$dir = opendir('.');
if (!$dir) return;
while ($file = readdir($dir))
{
if ( (ereg("^file_.*\.txt$", $file)) and (is_file($file)) )
{
$item = ereg_replace("^file_(.*)\.txt$", "\\1", $file);
echo '<a href="'.$PHP_SELF.'?id='.urlencode($item).'">'.$item."</a>\n";
if ($id == $item)
{
echo "<==";
}
echo "<br>\n";
}
}
}

function show_content($id)
{
$fp = fopen(get_filename($id), "r");
if (!$fp) return;
$first = true;
while (!feof($fp))
{
if ($fp)
{
$line = fgets($fp, 1024);
if ($first)
{
$first = false;
} else {
echo $line;
}
}
}
fclose($fp);
}

Is dynamic.php in the same directory as index.php, switch your stetement
from include to require if it fails to find it them you'll learn where
it is looking for the file.

Just for future reference some servers require you php tags to be
written as <?php ... ?>

graeme.

Sarah, Godfrey, Matthew & Vera wrote:

> I am trying to run the following code but get the following error:
>
> Fatal error: Call to undefined function show_title() in
> d:\webhost\build\index.php on line 13
>
>
> This file is called "index.php"
>
> <? include "dynamic.php"; ?>
> <? if (empty($id)) $id = "Home"; ?>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <html>
> <head>
> <title> <? show_title($id); ?> </title>
> </head>
> <body>
> <? show_navigation($id); ?>
> <? show_content($id); ?>
> </body>
> </html>
>
> The other file "dynamic.php" has the following functions
>
> /* dynamic.inc.php */
>
>
> function show_title($id)
> {
> $fp = fopen(get_filename($id), "r");
> if (!$fp) return;
> $line = trim(fgets($fp, 255));
> fclose($fp);
> echo $line;
> }
>
>
>
> function get_filename($id)
> {
> $name = "file_$id.txt";
> if (file_exists($name))
> {
> return $name;
> } else {
> return "error.txt";
> }
> }
>
>
> function show_navigation($id)
> {
> global $PHP_SELF, $SCRIPT_NAME;
> if (trim($PHP_SELF) == "") $PHP_SELF = $SCRIPT_NAME;
> $dir = opendir('.');
> if (!$dir) return;
> while ($file = readdir($dir))
> {
> if ( (ereg("^file_.*\.txt$", $file)) and (is_file($file)) )
> {
> $item = ereg_replace("^file_(.*)\.txt$", "\\1", $file);
> echo '<a
> href="'.$PHP_SELF.'?id='.urlencode($item).'">'.$item."</a>\n";
> if ($id == $item)
> {
> echo "<==";
> }
> echo "<br>\n";
> }
> }
> }
>
>
>
> function show_content($id)
> {
> $fp = fopen(get_filename($id), "r");
> if (!$fp) return;
> $first = true;
> while (!feof($fp))
> {
> if ($fp)
> {
> $line = fgets($fp, 1024);
> if ($first)
> {
> $first = false;
> } else {
> echo $line;
> }
> }
> }
> fclose($fp);
> }
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Experience is a good teacher, but she sends in terrific bills.

Minna Antrim

At 02:47 PM 3/2/2005, you wrote:

>the file name in your include is wrong please use dynamic.inc.php instead of
>dynamic.php
>
>:)

Thanks but that is what I started with and still it could not work. Let me
do that again.

Godfrey

At 04:43 AM 3/2/05, Sarah, Godfrey, Matthew & Vera wrote:

>I am trying to run the following code but get the following error:
>
>Fatal error: Call to undefined function show_title() in
>d:\webhost\build\index.php on line 13
>
>
>This file is called "index.php"
>
><? include "dynamic.php"; ?>
><? if (empty($id)) $id = "Home"; ?>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
>< <? show_navigation($id); ?> <? show_content($id); ?>
>The other file "dynamic.php" has the following functions
>
>/* dynamic.inc.php */

Five (at least) problems:

1) you error message says that error occured on line 13 of index.php, but
the index.php file you show us does not have 13 lines in it.

2) You say the include file is called "dynamic.php", but the first line of
that file has "dynamic.inc.php" - which is it.

3) You should normally use "require" instead of "include" in this situation.

4) I'm guessing you are running with "register_globals" on, which not a
good idea; if it is off, the above code (line wid $id) will not work.

5) These problems have nothing to do with PostgresSQL, so this post is
off-topic. I suggest you find a php newbie list.