Re: Database/Table Design for Global Country Statistics

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