Daniel Crocker Daniel Crocker - 6 months ago 40
SQL Question

MySQL to PHP arrays for phplot

I have the following query.

SELECT day, price, source FROM prices WHERE item_id = 5


Table looks like this

date | price | source
1 | 1 | source_1
2 | 2 | source_2
3 | 9 | source_1
3 | 7 | source_2
3 | 3 | source_3


Note that not every source has data for every "date". Some dates have multiple sources. This is for a price comparison graph powered by PHPlot.

The array for PHPlot needs to be like this.

$data = array(
array('1', 1, 4, 2),
array('2', 5, 7, 1),
array('3', 7, '', 6),
array('4', 8, 1, 4),
array('5', 2, 4, 6),
array('6', '', 4, 5),
array('7', 7, 2, 3));


Notice that some data is missing for some sources. This is intended.

So what I need to do (I think) is to group the data in some way.

The final icing on the cake is the data labels...

$labels = array('source_1', 'source_2', 'source_3');


I'm ashamed to admit this is going over my head quite a bit, but I'm keen to learn. I have this working perfectly with only one "source".

Answer

ok.. to make your data arrays, try this query:

SELECT `day`, `price`, `source` FROM prices WHERE `item_id` = 5 ORDER BY `day`, `source`

Then fetch an associative array from that result, and pass it to this function:

function makeArray($rows) {
    $rowCount = 0;
    $finalArray = array();
    $legendArray = array();

    foreach($rows as $row) {
        $thisDate = (int)$row['day'];
        if ($thisDate > $rowCount) {
            $rowCount++;
            $finalArray[$rowCount-1] = array($thisDate, '', '', '');
        }
        if (!in_array($row['source'], $legendArray)) {
            $legendArray[] = $row['source'];
        }
        $thisIndex = 1;
        switch($row['source']) {
            case 'source_1':
                $thisIndex = 1;
                break;
            case 'source_2':
                $thisIndex = 2;
                break;
            case 'source_3':
                $thisIndex = 3;
                break;
        }
        $finalArray[$rowCount-1][$thisIndex] = $row['price'];
    }
    return array($legendArray, $finalArray);
}

Using your example data, here is the output when print_r()'ing the returned array:

Array 
    (
        [0] => Array
            (
                Array 
                (
                    [0] => Array
                        (
                            [0] => 1
                            [1] => 1
                            [2] => 
                            [3] => 
                        )

                    [1] => Array
                        (
                            [0] => 2
                            [1] => 
                            [2] => 2
                            [3] => 
                        )

                    [2] => Array
                        (
                            [0] => 3
                            [1] => 9
                            [2] => 7
                            [3] => 3
                        )

                )
            )

        [1] => Array
            (
                [0] => 'source_1'
                [1] => 'source_2'
                [2] => 'source_3'
            )
    )
Comments