Re: Database/Table Design for Global Country Statistics

From: Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Database/Table Design for Global Country Statistics
Date: 2007-09-13 12:43:19
Message-ID: A00B0CCB-38F2-40F6-9856-37EF91B86950@grid.unep.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


>>> $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); }
>>>
>> Thanks for the code. I got it working with a couple of changes.
>> But then I realized that with the new table design I can't anymore
>> easily sort by a given year (1970 or 2000). This is surely one of
>> the advantages of the "old" design, that the use via PHP was quite
>> straight forward.
>> Do I have to transfer the query results into a PHP array to sort
>> it in there, then?
>
> Umm - not sure what you're after. What's wrong with one of:
> SELECT ... ORDER BY year, value
> SELECT ... ORDER BY value, year
>
> Or did you want a particular year pulled out of the general list,
> in which case try something like:
> SELECT ... ORDER BY (year = 1970), year, value
> SELECT ... ORDER BY (year <> 1970), year, value
> This works because booleans are considered sortable too.

Wow, didn't know about the "(year = 1970)" thing. Cool. But
nevertheless, the problem is then with the PHP code above; a
different sorting in the query result, means as well a different
coding. Or I have completely miscoded your draft. But I don't see how
it would handle a resulting array of any order - by year, by name,
ascending, descending... I guess I need to go with the PHP array, no?

$current_country = -1;
$count = -1;
$cols = array();

while ($row = pg_fetch_array($result))
{
if ($row['name'] != $current_country)
{
if (sizeof($cols) > 0)
{
$count++;
$code7 .= display_table_row($current_country, $cols, $count,
$selectedDataset -> name, $selectedID, $selectedTime);
}
$cols = array();
$current_country = $row['name'];
}
$cols[] = $row['value'];
}

// handle possible last row of table
if (sizeof($cols) > 0)
{
$code7 .= display_table_row($current_country, $cols, ($count + 1),
$selectedDataset -> name, $selectedID, $selectedTime);
}

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2007-09-13 13:01:35 Re: Database/Table Design for Global Country Statistics
Previous Message Richard Huxton 2007-09-13 12:21:57 Re: Database/Table Design for Global Country Statistics