A Challenge? converting mysql rows to very specific format with php

Question

how to format sql server rows using php that look like this:

id     company         value      monthyear
1      companyone      30         january2012
2      companytwo      20         february2012
3      companyone      10         february2012

into this:

monthyear: ['january2012', 'february2012']

and this:

company: 'companyone', value: [30, 10]
company: 'companytwo', value: [0, 20]

each instance of a month from the db is combined into one instance.

company one, which has two rows, is combined into one instance where each value is lined up in order of the month. company two, which only has one instance, has it's value defined as 0 where it has no instance in a month.

the farthest i've gotten is are two two dimensional array with array_merge_recursive and some conditional statements but then my head goes into knots.

@Vytautas i don't really have any, minus a simple array being created out of the loop. — weather, Mar 07 '12 at 06:59

Basti · Accepted Answer · 2012-03-07T08:19:19.080

SELECT
    company, 
    GROUP_CONCAT(value SEPARATOR ',') AS value, 
    GROUP_CONCAT(monthyear SEPARATOR ',') AS monthyear
FROM
    yourTable
GROUP BY
    company

Some Reference for GROUP_CONCAT.

PHP solution:

Select the to be grouped attribute sorted (company). Loop over them and open a new group every time you encounter a different value for company. As long as the current row has the same row as the previous, add value and monthyear to the current company.

You could do this even without sorting:

while($row = mysql_fetch_assoc($resource))
{
    $values[$row["country"]][] = $row["value"];
    $monthyear[$row["country"]][] = $row["monthyear"];
}

Some output example

foreach ($values as $country => $valuesOneCountry)
{
    // each country
    var_dump($country);

    foreach ($valuesOneCountry as $i => $value)
    {
        // value, monthyear for each original row

        var_dump($value, $monthyear[$country][$i]);
    }
}

Elegant way with OOP:

class Tuple
{
    public $country, $values, $monthyears;

    public function __construct($country, $values = array(), $monthyears = array())
    {
        $this->country = $country;
        $this->values = $value;
        $this->monthyears = $monthyears;
    }
}

$tuples = array();
while($row = mysql_fetch_assoc($resource))
{
    if (!isset($tuples[$row["country"]]))
         $tuples[$row["country"]] = new Tuple($row["country"]);

    // save reference for easy access        
    $tuple = $tuples[$row["country"]];

    // or some method like $tuple->addValue($row["value"]);
    $tuple->values[] = $row["value"];
    $tuple->monthyears[] = $row["monthyear"];
}

var_dump($tuples);

well double shit, i wrote in the original that it was mysql but in fact i am using sql server — weather, Mar 07 '12 at 06:55
I don't know sql server so I'm giving a road map for PHP. Try this second. http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Basti, Mar 07 '12 at 07:02
thanks! this php for sure the start of the answer, but how do i actually echo or show it? the sqlserver might be where i end up. — weather, Mar 07 '12 at 07:28

A Challenge? converting mysql rows to very specific format with php

1 Answers1