PHP processing data and create array

Question

I have a mysql query with result like this:

ID | index | Mapping index |   Date   
1  |  27   |    value27    |   2019-04      
2  |  28   |    value28    |   2019-05       
3  |  28   |    value28    |   2019-05      
4  |  32   |    value32    |   2019-07    
5  |  32   |    value32    |   2019-05

The results should be prepared to display stacked charts. As result i need in php:

// array to display google chart
['2019-04', 1, 0, 0,],
['2019-05', 0, 2, 1,],
['2019-07', 0, 0, 1,],

// explanation
ID | value27 | value28 | value 32 |  Date   
1  |  1      |  0      | 0        |  2019-04      
2  |  0      |  2      | 1        |  2019-05   
2  |  0      |  0      | 1        |  2019-07

This is my php script:

$preparevar = array();
foreach($data["timechart"] as $date){
    array_push($preparevar,[$date->date, $date->count , '\''.$date->repcontent.'\'' ]);
} 

$googleChartArray = array(); //Use this array to group the results using date.
foreach( $preparevar as $d ) {
    $date = $d[0];
    $value = $d[1];

    if( !isset( $googleChartArray[$date] ) ) {
        $googleChartArray[$date] = array( "'". $date. "'" ); //Date needs to be enclosed in quote.
    }
    $googleChartArray[$date][] = $value; 
}

$f = array(); //Format the above array to split value in a comma separated format.
foreach( $googleChartArray as $g ) {
    $f[] = implode( ',' , $g );
}

$json_out = json_encode(array_values($googleChartArray));

The problem with this format is, that the zero values will be ignored:

[
['2019-04',1],
['2019-05',2,1],
['2019-07',1]
]

should be:

[
['2019-04',1,0,0],
['2019-05',0,2,1],
['2019-07',0,0,1]
]

Here an example of $data["timechart"]:

array(11) {
  [0]=&gt;
  object(stdClass)#43 (14) {
    ["id"]=&gt;
    string(2) "46"
    ["index"]=&gt;
    string(2) "31"
    ["index2"]=&gt;
    string(1) "0"
    ["keynr"]=&gt;
    string(2) "31"
    ["repcontent"]=&gt;
    string(41) "Value31"
    ["count"]=&gt;
    string(1) "1"
    ["date"]=&gt;
    string(7) "2007-06"
  }

And here an example of my query. I can´t use SUM(CASE) for example beacause index are variable.

SELECT
        orders.id,
        positions_list.index,
        RepK.keynr,
        RepK.content AS repcontent,
        RepK.p_company,
        COUNT(positions_list.index) AS count,
        DATE_FORMAT(orders.date_placement, '%Y-%m') AS date
        from orders
        JOIN tools
        ON tools.id=orders.tool_id
        JOIN positions_list ON positions_list.order_id = orders.id
        LEFT JOIN repkey as RepK
        ON   RepK.keynr=positions_list.index
        AND  RepK.p_company=orders.comp_id

        WHERE
        tools.id =:id
        AND RepK.keynr IS NOT NULL

        group by DATE_FORMAT(orders.date_placement, '%Y-%m'),positions_list.index

What zero values? You don't have any in your resultset for your query. Where do those come from? Also, please provide a full, useable sample of $data (or at least $data["timechart"]). — Patrick Q
– Patrick Q, Commented Aug 29, 2019 at 12:33
No there are no zero values, but this is the needed format to display stackes charts by columns. So i need a predefinied array and fill the values there the matching columns are. — Marco
– Marco, Commented Aug 29, 2019 at 12:55
You can probably get your data in your desired format straight from your query. Can you share table structure and the query? — Nick
– Nick, Commented Aug 29, 2019 at 12:56

mickmackusa · Accepted Answer · 2019-09-01 22:06:13Z

MySQL doesn't currently offer variable width pivots, so you can either:

make two queries, the first to collect the unique repcontent columns, then build a second query to implement a pivot technique by writing a SELECT clause with dynamic CASE WHEN statements for each column or
make one query, and let php prepare the results (this can be scripted up in a few different ways, but I'll recommend this one)

Code: (Demo)

$resultSet = [
    ['repcontent' => 'Value 27', 'date' => '2019-04'],
    ['repcontent' => 'Value 28', 'date' => '2019-05'],
    ['repcontent' => 'Value 28', 'date' => '2019-05'],
    ['repcontent' => 'Value 32', 'date' => '2019-07'],
    ['repcontent' => 'Value 32', 'date' => '2019-05'],
];

$columns = array_unique(array_column($resultSet, 'repcontent'));
$lookupKeys = range(1, count($columns));
$lookup = array_combine($columns, $lookupKeys);
$defaults = array_fill_keys($lookupKeys, 0);

foreach ($resultSet as $row) {
    if (!isset($result[$row['date']])) {
        $result[$row['date']] = array_merge([$row['date']], $defaults);
    }
    ++$result[$row['date']][$lookup[$row['repcontent']]];
}
echo json_encode(array_values($result));

Output:

[["2019-04",1,0,0],["2019-05",0,2,1],["2019-07",0,0,1]]

For simplicity, generate a result set as an array of arrays.

Extract the unique repcontent values
Generate an array with values ranging from 1 to the unique repcontent count
Forge a lookup array consisting of #1 as keys and #2 as values -- this will determine where each "count" will stored when looping later
Create a default array consisting of #2 as keys and zeros as values
Now, loop through the result set and if a given row has a repcontent value which is encountered for the first time, create a new row in the output array using the date as the first element and the elements from #4 to follow.
Unconditionally, add 1 to the row's column that corresponds with with the repcontent value

If you don't quite understand why any of the variables ($columns, $lookupKeys, $lookup, $defaults) are generated or what they contain, call var_export() on my variables before entering the loop -- that should clear up any confusion.

I have a feeling that I could refine your query, but I won't venture a guess without having some realistic sample data to play with.

I don't see why you would need to add additional quotes to your json for the Google chart to work. If the chart doesn't render without the additional quotes, this is probably a symptom that you are passing the php variable to javascript in an improper fashion.

p.s. I see that you some development with Joomla, if this is a Joomla script and you are not able to craft your query with Joomla's query building methods, please post your best effort on Joomla Stack Exchange and I'll see if I can help.

Thank you! Your answer helped me a lot to understand the single steps and get my code to work! It is right that I start also to develop in joomla but this code is for my php project.

Collectives™ on Stack Overflow

PHP processing data and create array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related