[thelist] PHP/SQL query & transformation problem

[Darrell King]

Can you order your query by country name, then year? In that was, you could
just cycle through the results.

Another technique I use, roughly illustrated below, is to retrieve the
results first into a multi-dimensional array, then cycle through that:

while ($row = mysql_fetch_assoc($result)) {

  $record[$row['name']][$row['year']] = $row['value']

}

This ends up with a sorted array:

foreach ($record as $name => $country_years) {

// Here is the logic to check if we've processed this
// year and to print the HTML table cell in the first
// column if we haven't.

...

  // Now step through the years for this country
  foreach ($country_years as $year => $value) {

    // Here we enter cells for each column in this
    // country's rows as we go


 }

}

Note that if any country is missing a value for any year, you would need to
work out some checks to ensure we kept everything in the proper row. This
isn't perfect, but it is solid and easily maintained.

D


On 9/12/07, Stefan Schwarzer <st.schwarzer at geois.de> wrote:
>
> Hi there,
>
> I want to redesign my database, as was recommended to me from
> different sides. Although design is pretty clear, the result - and
> the way to program it in PHP is really different.
>
> As a result from my PostGres query I get something like this:
>
>     year    |    value     |    name
> ---------------------------------------
>    2001    |     123       |   Afghanistan
>    2002    |     125       |   Afghanistan
>    2003    |     128       |   Afghanistan
>    2004    |     132       |   Afghanistan
>    2005    |     135       |   Afghanistan
>
>    2001    |     412       |   Albania
>    2002    |     429       |   Albania
>    2003    |     456       |   Albania
>    2004    |     465       |   Albania
>    2005    |     477       |   Albania
>
> .....
>
>
> The way it is displayed on the web (in table form) is the "usual" way:
>
>     name             2001    2002   2003   2004   2005
> -----------------------------------------------------------------
> Afghanistan    ....
> Albania            ....
>
>
> So, I wonder, how to I get from the above SQL result - via PHP - to
> the below table output? I can't imagine that I have to work with some
> IFs (IF name IS Afghanistan....). Is there any "simple" procedure?
>
> Thanks for any help!
>
> Stef
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



-- 
Darrell G. King
DarrellGKing at gmail.com
(C) 1/585.820.8231