Search Postgresql Archives

Re: Database/Table Design for Global Country Statistics

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Stefan Schwarzer wrote:
Thanks for the feedback and the suggestions.

A problem I have now when using the new design is the following:

As a result from my PostGres query I get something like this:

    year    |    value     |    name
---------------------------------------
   2001    |     123       |   Afghanistan
   2002    |     125       |   Afghanistan
   2003    |     128       |   Afghanistan
[etc]
The way it is displayed on the web (in table form) is the "usual" way:

    name             2001    2002   2003   2004   2005
-----------------------------------------------------------------
Afghanistan    ....
Albania            ....


Is there any "simple", elegant solution for PHP, so that it does this transformation? I can't imagine that I would have to write a couple of IFs to achieve that. But I have no other idea. Or is it a question of writing a more elegant SQL query?

$curr_yr = -1
$cols = array();
while (<fetch rows>) {
  if ($row['year'] != $curr_yr) {
    if (sizeof($cols) > 0) { display_table_row($cols); }
    $cols = array();
    $curr_year = $row['year'];
  }
  $cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row($cols); }


Make sure your query is ordered properly and you don't have gaps in your years.

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux