grouping query results

Поиск
Список
Период
Сортировка
Искать

grouping query results

От:
"Joolz" <joolz@arbodienst-limburg.nl>
Дата:
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!

Re: grouping query results

От:
Andrew McMillan <andrew@catalyst.net.nz>
Дата:
Вложения:
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/
-------------------------------------------------------------------------

Re: grouping query results

От:
Mitch Pirtle <mitch.pirtle@gmail.com>
Дата:
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

Re: grouping query results

От:
Bruno Wolff III <bruno@wolff.to>
Дата:
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.

PHP and Error Reporting

От:
<operationsengineer1@yahoo.com>
Дата:
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 

Re: PHP and Error Reporting

От:
<operationsengineer1@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@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 

Re: PHP and Error Reporting

От:
Mitch Pirtle <mitch.pirtle@gmail.com>
Дата:
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

Re: PHP and Error Reporting

От:
<operationsengineer1@yahoo.com>
Дата:

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

Re: PHP and Error Reporting

От:
Thom Dyson <TDyson@sybex.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@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?

Re: PHP and Error Reporting

От:
graeme <graeme_foster@sherubtse.edu.bt>
Дата:
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


PHP function error

От:
"Sarah, Godfrey, Matthew & Vera" <kyedza@sdf.lonestar.org>
Дата:
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"






          <? show_title($id); ?> 







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 ''.$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); }

Re: PHP function error

От:
graeme <graeme_foster@sherubtse.edu.bt>
Дата:
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"
>
> 
> 
> 
> 
> 
>          <? show_title($id); ?> 
> 
> 
> 
> 
> 
> 
>
> 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

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

От:
"Sarah, Godfrey, Matthew & Vera" <kyedza@sdf.lonestar.org>
Дата:
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 


Re: PHP function error

От:
Frank Bax <fbax@sympatico.ca>
Дата:
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.

FAQ