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