Re: Database/Table Design for Global Country Statistics

Many, many thanks for that and the other advices from everybody.
You're great!

As I am neither expert in PHP, nor in Postgres, MySQL, Javascript,
HTML etc. etc., all I do in the forums is asking questions; and not
"adding value" by helping others (due to my limited knowledge). All I
can do is offer you a moment of relaxation by looking at some really
nice nature/landscape photos @ http://photoblog.la-famille-schwarzer.de

Cheers,

Stef


On Sep 12, 2007, at 11:45 AM, Richard Huxton wrote:

> 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