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!


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: grouping query results

--=-GPs3EFmiBQzFpC2wq7i+
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Mon, 2005-02-28 at 10:48 +0100, Joolz wrote:
> Hello everyone,
>=20
> In a PHP / Postgresql application I have to make a lot of lists like
> this:
>=20
> THE DATA STRUCTURE
> employee_number, employee_name, department
>=20
> THE OUTPUT
> ------------------------------------
> employee_number employee_name
>=20
> department X
> 1 Johnson
> 22 Jackson
> subtotal: 2 employees
>=20
> department Y
> 222 Smith
> 3 Doe
> 44 Jameson
> subtotal: 3 employees
>=20
> grand total: 5 employees
> ------------------------------------
>=20
> I see 2 ways to solve this:
>=20
> - use SELECT for the detail lines and SELECT COUNT (*) for the (sub)
> totals
>=20
> - SELECT the whole lot and let PHP do the grouping and counting
>=20
> The second solution seems old fashioned to me and the first has a
> catch: all queries have to be made within a single transaction.
>=20
> 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 =3D 0;
$dtotal =3D 0;
$last_department =3D "no department";
for( $i=3D0 $i < rows; $row =3D pg_Fetch_Object(..., $i) {
if ( $row->department !=3D $last_department ) {
if ( $i > 0 ) {
print_total( "subtotal", $dtotal );
}
$dtotal =3D 0;
$last_department =3D $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/
------------------------------------------------------------ -------------


--=-GPs3EFmiBQzFpC2wq7i+
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQBCI16IjJA0f48GgBIRAqw2AJ9TTCTAKhXHj4wqDgL6EKFIbqBucACc DmUR
KtAqmbWt616BPdeJTAAnCFw=
=YXeT
-----END PGP SIGNATURE-----

--=-GPs3EFmiBQzFpC2wq7i+--

Re: grouping query results

On Mon, 28 Feb 2005 10:48:23 +0100 (CET), Joolz
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: grouping query results

On Mon, Feb 28, 2005 at 10:48:23 +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

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

PHP and Error Reporting

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

Re: PHP and Error Reporting

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@yahoo.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: PHP and Error Reporting

On Tue, 1 Mar 2005 11:27:15 -0800 (PST), operationsengineer1@yahoo.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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PHP and Error Reporting

--- Mitch Pirtle wrote:

> On Tue, 1 Mar 2005 11:27:15 -0800 (PST),
> operationsengineer1@yahoo.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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PHP and Error Reporting

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@postgresql.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@postgresql.org)

Re: PHP and Error Reporting

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@postgresql.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@postgresql.org)
>
>
>
>

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

Minna Antrim



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

PHP function error

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"














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 '\n";
if ($id == $item)
{
echo "<==";
}
echo "
\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@postgresql.org so that your
message can get through to the mailing list cleanly

Re: PHP function error

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

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"
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 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 ' > href="'.$PHP_SELF.'?id='.urlencode($item).'">'.$item."\n";
> if ($id == $item)
> {
> echo "<==";
> }
> echo "
\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@postgresql.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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: [**POSSIBLE SPAM**] PHP function error

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: PHP function error

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: PHP function error

Hello Mr. Bax,

Many thanks for your kind comments.

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

I guess you are saying any inquiry here should be PosgresSQL/php related.

I will be careful in future. :-)

Godfrey



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match