0
\$\begingroup\$

These arrays are the result of Store Procedure. They are to build a Google Chart, therefore I need transform the array to a specific format. I also want to remove the $col and $row elements that have a Null value. These arrays are dynamic to their length variable, but a $row element array always has the same length as a $cols array. I achieve that by using eval() to build the if statement dynamically.

Is there a better way to do this without using eval()? Are there any improvements that can be done?

I'm using PHP 5.4.

$cols = Array
    (
    0 => 'Name',
    1 => 'MCR',
    2 => 'MCD',
    3 => 'COM',
    4 => 'INV',
    5 => 'IDM',
    6 => 'PPO',
    7 => 'HKD',
    8 => 'ALL',
    9 => 'POS',
    10 => 'MCD-SP',
    11 => 'MCD-BH',
    12 => 'MCD-CL',
    13 => 'MCD-CO',
    14 => 'MCR-SP',
    15 => 'MCR-PO'
);

$rows = Array(
    0 => Array(
        0 => 'Primero',
        1 => 19683.43,
        2 => NULL,
        3 => 3757.38,
        4 => NULL,
        5 => NULL,
        6 => NULL,
        7 => NULL,
        8 => NULL,
        9 => NULL,
        10 => NULL,
        11 => NULL,
        12 => NULL,
        13 => NULL,
        14 => NULL,
        15 => NULL
    ),
    1 => Array
        (
        0 => 'Segundo',
        1 => 15340.5767,
        2 => NULL,
        3 => NULL,
        4 => NULL,
        5 => NULL,
        6 => NULL,
        7 => NULL,
        8 => NULL,
        9 => NULL,
        10 => NULL,
        11 => NULL,
        12 => NULL,
        13 => NULL,
        14 => NULL,
        15 => NULL
    ),
    2 => Array
        (
        0 => 'Tercero',
        1 => NULL,
        2 => NULL,
        3 => NULL,
        4 => NULL,
        5 => NULL,
        6 => NULL,
        7 => NULL,
        8 => NULL,
        9 => NULL,
        10 => 4969.65,
        11 => NULL,
        12 => NULL,
        13 => NULL,
        14 => 23695.39,
        15 => NULL
    ),
    3 => Array
        (
        0 => 'Cuarto',
        1 => NULL,
        2 => NULL,
        3 => NULL,
        4 => NULL,
        5 => NULL,
        6 => NULL,
        7 => NULL,
        8 => NULL,
        9 => NULL,
        10 => NULL,
        11 => 45974.86,
        12 => NULL,
        13 => NULL,
        14 => NULL,
        15 => NULL
    ),
    4 => Array
        (
        0 => 'Quinto',
        1 => NULL,
        2 => NULL,
        3 => NULL,
        4 => NULL,
        5 => NULL,
        6 => NULL,
        7 => NULL,
        8 => NULL,
        9 => NULL,
        10 => 1405.8,
        11 => 39244,
        12 => NULL,
        13 => NULL,
        14 => NULL,
        15 => NULL
    )
);

$qty_col = count($cols) - 1;
$qty_rows = count($rows);
//echo $qty_rows;

$ifScript = '';
for ($a = 1; $a <= $qty_col; $a++) {
    $unsetScript = 'unset(';
    $ifScript .='if(';
    for ($b = 0; $b <= $qty_rows; $b++) {
        $ifScript .= 'empty($rows[' . $b . '][' . $a . '])';
        $unsetScript .='$rows[' . $b . '][' . $a . ']';
        if ($b == $qty_rows) {
            $ifScript .='){';
            //$ifScript .='){<br>';
            $unsetScript .=', $cols[' . $a . ']);';
        } else {
            $ifScript .= ' && ';
            $unsetScript .=', ';
        }
    }
    $ifScript .= $unsetScript . '}';
    //$ifScript .= $unsetScript . '<br>}<br>';
}


//echo $ifScript;
eval($ifScript);
$cols = array_values($cols);
$rows = array_map('array_values', $rows);

foreach ($cols as $key => $value) {
    $cols[$key] = array(
        'id' => NULL,
        'label' => $value
    );
    $type = ($key == 0) ? 'string' : 'number';
    $cols[$key]['type'] = $type;
}

foreach ($rows as $key1 => $value1) {
    foreach ($value1 as $key2 => $value2) {
        $val = (empty($value2)) ? '0.00' : $value2;
        $format = ($key2 == 0) ? strtoupper(trim($value2)) : '$' . number_format($value2, '2', '.', ',');
        $value1[$key2] = array(
            'v' => $val,
            'f' => $format
        );
    }
    $rows[$key1] = array(
        'c' => $value1
    );
}
$newArray = array(
    'cols' => $cols,
    'rows' => $rows
);
echo '<pre>';
print_r($newArray);
echo '</pre>';

Output

Array
(
    [cols] => Array
        (
            [0] => Array
                (
                    [id] => 
                    [label] => Name
                    [type] => string
                )

            [1] => Array
                (
                    [id] => 
                    [label] => MCR
                    [type] => number
                )

            [2] => Array
                (
                    [id] => 
                    [label] => COM
                    [type] => number
                )

            [3] => Array
                (
                    [id] => 
                    [label] => MCD-SP
                    [type] => number
                )

            [4] => Array
                (
                    [id] => 
                    [label] => MCD-BH
                    [type] => number
                )

            [5] => Array
                (
                    [id] => 
                    [label] => MCR-SP
                    [type] => number
                )

        )

    [rows] => Array
        (
            [0] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Primero
                                    [f] => PRIMERO
                                )

                            [1] => Array
                                (
                                    [v] => 19683.43
                                    [f] => $19,683.43
                                )

                            [2] => Array
                                (
                                    [v] => 3757.38
                                    [f] => $3,757.38
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [4] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [5] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [1] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Segundo
                                    [f] => SEGUNDO
                                )

                            [1] => Array
                                (
                                    [v] => 15340.5767
                                    [f] => $15,340.58
                                )

                            [2] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [4] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [5] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [2] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Tercero
                                    [f] => TERCERO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [2] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 4969.65
                                    [f] => $4,969.65
                                )

                            [4] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [5] => Array
                                (
                                    [v] => 23695.39
                                    [f] => $23,695.39
                                )

                        )

                )

            [3] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Cuarto
                                    [f] => CUARTO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [2] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [4] => Array
                                (
                                    [v] => 45974.86
                                    [f] => $45,974.86
                                )

                            [5] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [4] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Quinto
                                    [f] => QUINTO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [2] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 1405.8
                                    [f] => $1,405.80
                                )

                            [4] => Array
                                (
                                    [v] => 39244
                                    [f] => $39,244.00
                                )

                            [5] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

        )

)
\$\endgroup\$
  • \$\begingroup\$ Every heard of RecursiveFilterIterator (part of the SPL)? \$\endgroup\$ – kaiser Jan 8 '14 at 0:58
  • 3
    \$\begingroup\$ Iterator garden by hakre. But please just search the web. That's how I came across it as well :) \$\endgroup\$ – kaiser Jan 8 '14 at 1:42
  • 1
    \$\begingroup\$ If you're using eval that much, the question isn't "is there a better way", but "how many alternative ways are there, and how much better are they" ;-P \$\endgroup\$ – Elias Van Ootegem Jan 8 '14 at 7:13
  • 1
    \$\begingroup\$ I just erased two attempts at making this better as I didn't like my own results. When you do get this solved, please post the improved code. \$\endgroup\$ – dotancohen Jan 8 '14 at 13:29
  • \$\begingroup\$ @dotancohen could you please share your idea in ideone.com two heads are better than one \$\endgroup\$ – Emilio Gort Jan 8 '14 at 15:11
1
\$\begingroup\$

Why not do something like this?

$output = array();

if((bool)$cols && (bool)$rows) {
    foreach($rows as $row_index => $row) {
        foreach($cols as $column_index => $column_name) {
            $column_has_atleast_one_value = false;
            if(isset($row[$column_index]) && (bool)$row[$column_index]){
                $output["cols"][$column_index] = array(
                    "id" => NULL,
                    "label" => $column_name,
                    "type" => ($column_index == 0) ? 'string' : 'number'
                );
            }
        }
    }
}

if((bool)$rows) {

    foreach($rows as $row_index => $columns) {
        foreach($columns as $column_index => $column_data){
            if(!isset($output["cols"][$column_index])){
                continue;
            }
            $column_formatted = "";
            if($column_index == 0) {
                $column_formatted = strtoupper($column_data);
            } else {
                if(!(bool)$column_data) {
                    $column_data = "0.00";
                }
                $column_formatted = '$' . number_format($column_data, '2', '.', ',');
            }
            $output["rows"][$row_index]["c"][$column_index] = array(
                "v" => $column_data,
                "f" => $column_formatted
            );
        }
    }
}

echo "<pre>";
print_r($output);
echo "</pre>";

Output

Array
(
    [cols] => Array
        (
            [0] => Array
                (
                    [id] => 
                    [label] => Name
                    [type] => string
                )

            [1] => Array
                (
                    [id] => 
                    [label] => MCR
                    [type] => number
                )

            [3] => Array
                (
                    [id] => 
                    [label] => COM
                    [type] => number
                )

            [10] => Array
                (
                    [id] => 
                    [label] => MCD-SP
                    [type] => number
                )

            [14] => Array
                (
                    [id] => 
                    [label] => MCR-SP
                    [type] => number
                )

            [11] => Array
                (
                    [id] => 
                    [label] => MCD-BH
                    [type] => number
                )

        )

    [rows] => Array
        (
            [0] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Primero
                                    [f] => PRIMERO
                                )

                            [1] => Array
                                (
                                    [v] => 19683.43
                                    [f] => $19,683.43
                                )

                            [3] => Array
                                (
                                    [v] => 3757.38
                                    [f] => $3,757.38
                                )

                            [10] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [11] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [14] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [1] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Segundo
                                    [f] => SEGUNDO
                                )

                            [1] => Array
                                (
                                    [v] => 15340.5767
                                    [f] => $15,340.58
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [10] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [11] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [14] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [2] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Tercero
                                    [f] => TERCERO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [10] => Array
                                (
                                    [v] => 4969.65
                                    [f] => $4,969.65
                                )

                            [11] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [14] => Array
                                (
                                    [v] => 23695.39
                                    [f] => $23,695.39
                                )

                        )

                )

            [3] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Cuarto
                                    [f] => CUARTO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [10] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [11] => Array
                                (
                                    [v] => 45974.86
                                    [f] => $45,974.86
                                )

                            [14] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

            [4] => Array
                (
                    [c] => Array
                        (
                            [0] => Array
                                (
                                    [v] => Quinto
                                    [f] => QUINTO
                                )

                            [1] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [3] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                            [10] => Array
                                (
                                    [v] => 1405.8
                                    [f] => $1,405.80
                                )

                            [11] => Array
                                (
                                    [v] => 39244
                                    [f] => $39,244.00
                                )

                            [14] => Array
                                (
                                    [v] => 0.00
                                    [f] => $0.00
                                )

                        )

                )

        )

)
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ thanks, but I have to remove the $col and $row elements that have a Null value... see my output it has only 6 elements for each col...that's the part I made with eval() \$\endgroup\$ – Emilio Gort Jan 9 '14 at 21:28
  • \$\begingroup\$ Hi, I updated my code to get rid of the columns that have null values. When you are dealing with array or databases, try your best to keep the index keys same across all datasets. Because one misleading number can throw the whole structure off. \$\endgroup\$ – roosevelt Jan 9 '14 at 22:27
  • \$\begingroup\$ Thanks, Could we have a chat?, I see new things for me in your script that I want to learn if you can clarify. the continue and the (bool)$cols. Also I reset the array because google chart doesn't work if the array isn't reset \$\endgroup\$ – Emilio Gort Jan 9 '14 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.