Re: Database/Table Design for Global Country Statistics

Stefan Schwarzer wrote:
>> $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.

--
Richard Huxton
Archonet Ltd