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