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.
>
> 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?

Ah, I see - your query-results do need to be ordered the same as the
table, yes.

Of course you should really have a data model that knows what it wants
to sort by and constructs the query appropriately. The table-drawing
code can then ask the data-model for heading-names and sort-order
details. It's more work up-front, but you only have to do it once and
then you can generate new table layouts very easily.

--
   Richard Huxton
   Archonet Ltd