PHP PDO Convert array to different format

Question

I am trying to convert an array obtained by the code below using non-deprecated techniques with php pdo:

$stm = $conn->prepare("SELECT * FROM mysqltable");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
print_r($results);

to the following format required for fusioncharts to be used

[
{
label: "CJ Anderson",
value: "25"
},
{
label: "Imran Tahir",
value: "25"
},
...
...
]

The original array is as follows:

Array (
    [0] => Array (
        [Id] => 6 
        [Number] => 1234567890 
        [Visits] => 1 
        [Name] => John 
    )
    [1] => Array (
        [Id] => 7 
        [Number] => 1236549871 
        [Visits] => 9 
        [Name] => Jerry 
    )
    [2] => Array (
        [Id] => 8 
        [Number] => 2147483647 
        [Visits] => 3 
        [Name] => Jane 
    )
)

Any help would be appreciated, thanks.

EDIT: As I commented below. I have a full php file that works if you put data in manually. I can't get it to work though when I put the $jsonEncodedData in though. Thoughts?

<html>
   <head>
    <title>FusionCharts XT - Column 2D Chart - Data from a database</title>
    <link  rel="stylesheet" type="text/css" href="css/style.css" />

    <!-- You need to include the following JS file to render the chart.
    When you make your own charts, make sure that the path to this JS file is correct.
    Else, you will get JavaScript errors. -->

    <script src="fusioncharts/js/fusioncharts.js"></script>
  </head>

   <body>
 <?php

 try {

# MySQL with PDO_MYSQL
$mysql_host = 'host';
$mysql_database = 'table';
$mysql_username = 'user';
$mysql_password = 'pass';

    $conn = new PDO("mysql:host=$mysql_host; dbname=$mysql_database", $mysql_username, $mysql_password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

}
catch(PDOException $e) {
echo $e->getMessage();
}

        // Form the SQL query that returns the top 10 most populous countries

        // Execute the query, or else return the error message.
$stm = $conn->prepare("SELECT Name, Visits FROM mysqltable"); //WHERE Area :SelArea");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);

 include("fusioncharts.php");

$jsnarray = array();
foreach($results as $k => $v){
    $jsnarray[] = array('label' => $results[$k]['Name'], 'value' => $results[$k]['Visits']);
};
    $jsonEncodedData=json_encode($jsnarray);

    new FusionCharts("type of chart", 
            "unique chart id", 
            "width of chart", 
            "height of chart", 
            "div id to render the chart", 
            "type of data", 
            "actual data");
    $columnChart = new FusionCharts(
            "column2d", 
            "ex1" , 
            "600", 
            "400", 
            "chart-1", 
            "json", 
            '{  
               "chart":
               {  
                  "caption":"Harry\'s SuperMart",
                  "subCaption":"Top 5 stores in last month by revenue",
                  "numberPrefix":"$",
                  "theme":"ocean"
               },
               "data":  //$jsonEncodedData}'); <---I tried to insert this after "data":but no results unlike if you put raw data**
               [  
                  {  
                     "label":"Bakersfield Central",
                     "value":"880000"
                  },
                  {  
                     "label":"Garden Groove harbour",
                     "value":"730000"
                  },
                  {  
                     "label":"Los Angeles Topanga",
                     "value":"590000"
                  },
                  {  
                     "label":"Compton-Rancho Dom",
                     "value":"520000"
                  },
                  {  
                     "label":"Daly City Serramonte",
                     "value":"330000"
                  }
               ]
        }');
    // Render the chart
    $columnChart->render();
?>

    <div id="chart-1"><!-- Fusion Charts will render here--></div>

   </body>

</html>

==============Edit 12/28/15==========

Tried the following code with no results, Question I have is shouldn't we end in "}" as they require that:

    $columnChart = new FusionCharts(
            "column2d", 
            "ex1" , 
            "600", 
            "400", 
            "chart-1", 
            "json", 
            '{ 
               "chart":
               {  
                  "caption":"Harry\'s SuperMart",
                  "subCaption":"Top 5 stores in last month by revenue",
                  "numberPrefix":"$",
                  "theme":"ocean"
               },
               "data": ' . $jsonEncodedData);
               //}';
    // Render the chart
    print_r($columnChart);
    $columnChart->render();
?>

    <div id="chart-1"><!-- Fusion Charts will render here--></div>

   </body>

</html>

I wanted to post the array differences as well between the "manual" method and the "fetch method (above in this edit).

With fetch:

FusionCharts Object ( [constructorOptions:FusionCharts:private] => Array ( >[type] => column2d [id] => ex1 [width] => 600 [height] => 400 [renderAt] => >chart-1 [dataFormat] => json [dataSource] => { "chart": { >"caption":"Harry's SuperMart", "subCaption":"Top 5 stores in last month by >revenue", "numberPrefix":"$", "theme":"ocean" }, "data": >[{"label":"John","value":"125"},{"label":"Jerry","value":"125"},{"label":"Jane","value":"125"}] ) [constructorTemplate:FusionCharts:private] => >[renderTemplate:FusionCharts:private] => )

With Manual Method (that works):

FusionCharts Object ( [constructorOptions:FusionCharts:private] => Array ( >[type] => column2d [id] => ex1 [width] => 600 [height] => 400 [renderAt] => >chart-1 [dataFormat] => json [dataSource] => { "chart": { >"caption":"Harry's SuperMart", "subCaption":"Top 5 stores in last month by >revenue", "numberPrefix":"$", "theme":"ocean" }, "data": [ { >"label":"Bakersfield Central", "value":"880000" }, { "label":"Garden Groove >harbour", "value":"730000" }, { "label":"Los Angeles Topanga", >"value":"590000" }, { "label":"Compton-Rancho Dom", "value":"520000" }, { >"label":"Daly City Serramonte", "value":"330000" } ] } ) >[constructorTemplate:FusionCharts:private] => >[renderTemplate:FusionCharts:private] => )

I see two differences offhand, the manual inserts spaces around "data" and the ending } parameter.

easiest will be select whatever you want (don't use `SELECT *`) and [json_encode](http://php.net/manual/en/function.json-encode.php) — bansi, Dec 28 '15 at 04:29
_shouldn't we end in "}"_. Have you tried doing it? `$jsonEncodedData . '}');` to test? — FirstOne, Dec 28 '15 at 18:49
Looks like that did the trick! Thanks FirstOne, I will post an answer to summarize. I appreciate your patience in helping me out. — newpie, Dec 28 '15 at 19:51
No problem, I marked yours as the answer and +1, but you can edit how you like. Thanks again — newpie, Dec 28 '15 at 19:57

FirstOne · Accepted Answer · 2015-12-28T20:29:46.117

6

There is an automatic (and much much easier) way of doing this:

$stm = $conn->prepare('SELECT Name AS label, Visits AS value FROM mysqltable;');
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);
$jsonEncodedData = json_encode($results);
echo $jsonEncodedData;

Output (locally tested):

[{"label":"Foo","value":"5"},{"label":"Bar","value":"15"}]

That way you can just use it like this:

$columnChart = new FusionCharts('...
...
"data": ' . $jsonEncodedData . '}');

Note the . '}' in the end.

Before Edit:

You could do something like this:

// This part is just for running purposes
$foo = array (
    0 => Array (
        'Id' => 6,
        'Number' => 1234567890,
        'Visits' => 1,
        'Name' => 'John'
    ),
    1 => array (
        'Id' => 7,
        'Number' => 1236549871,
        'Visits' => 9,
        'Name' => 'Jerry'
    ),
    2 => array (
        'Id' => 8,
        'Number' => 2147483647,
        'Visits' => "3", // Example to output quoted
        'Name' => 'Jane'
    )
);

$bar = array();
foreach($foo as $k => $v){
    $bar[] = array('label' => $foo[$k]['Name'], 'value' => $foo[$k]['Visits']);
}

echo json_encode($bar);

Output:

[{"label":"John","value":1},{"label":"Jerry","value":9},{"label":"Jane","value":"3"}]

Compare with yours (from question) in one line:

[{label: "CJ Anderson",value: "25"},{label: "Imran Tahir",value: "25"},...]

Note: I assumed that value is represented by Visit and label by Name.

Read more about json_encode.

edited Dec 28 '15 at 20:29

answered Dec 28 '15 at 04:44

FirstOne

6,033
7
26
45

2

This can be achieved by the query `SELECT Name AS label, Visits AS value FROM mysqltable` and `json_encode($results)` much more efficient as there is less load on database transport and no additional iteration. – bansi Dec 28 '15 at 05:07
@bansi since you added this on the as comment on the question, I thought that you'd like to add that as an answer. Some people don't like to have their comment 'stolen', so I added mine as an alternative. **If it's ok to you**, I can edit and include your method by morning (it's really late now). That way, there is time for others to add it if they feel like it. – FirstOne Dec 28 '15 at 05:12
sure you add it to the answer. it will make it much better – bansi Dec 28 '15 at 05:16
Thank you for commenting so quickly. So I tried that alternative FirstOne. I actually did try to json_encode, but I noticed the output was not working or developing the chart. I then used your foreach method and still no results. Just to make sure it was not something else I put the original data inside to test. I will post the php below that does work if you use the sites data. Any help would be great. – newpie Dec 28 '15 at 05:47
@newpie, I've seen your update, have you tried instead of `"data": //$jsonEncodedData}');`, use: `"data": ' . $jsonEncodedData);` (Since it's single quotes, note the concatenation instead) – FirstOne Dec 28 '15 at 11:53
@newpie, just a note, I know the comment `//` is just you showing that it works with manual input, but please, test mys suggestion and let me know.... – FirstOne Dec 28 '15 at 11:59
Hello, I tried your suggestion and no dice. I thought maybe it would help if I do a print_r of the results from the manual option vs the "fetch" one that doesn't work. I will add it at the end of my above post. From what I can tell the Manual has extra spaces and other differences from the "fetch". Thank you for your help – newpie Dec 28 '15 at 17:56

score 2 · Answer 2 · answered Dec 28 '15 at 19:55

As a summary this is the piece that solved the issue including FirstOne's foreach statement:

$stm = $conn->prepare("SELECT Name, Visits FROM mysqltable"); //WHERE Area :SelArea");
$stm->execute();
$results = $stm->fetchAll(PDO::FETCH_ASSOC);

 include("fusioncharts.php");

$jsnarray = array();
foreach($results as $k => $v){
    $jsnarray[] = array('label' => $results[$k]['Name'], 'value' => $results[$k]['Visits']);
};

    $jsonEncodedData=json_encode($jsnarray);
    //print_r($jsonEncodedData);

    new FusionCharts("type of chart", 
            "unique chart id", 
            "width of chart", 
            "height of chart", 
            "div id to render the chart", 
            "type of data", 
            "actual data");
        $columnChart = new FusionCharts(
                "column2d", 
                "ex1" , 
                "600", 
                "400", 
                "chart-1", 
                "json", 
                '{ 
                   "chart":
                   {  
                      "caption":"Harry\'s SuperMart",
                      "subCaption":"Top 5 stores in last month by revenue",
                      "numberPrefix":"$",
                      "theme":"ocean"
                   },
                   "data": ' . $jsonEncodedData . '}');
        // Render the chart
        print_r($columnChart);
        $columnChart->render();
    ?>

        <div id="chart-1"><!-- Fusion Charts will render here--></div>

       </body>

    </html>

Thanks for everyone's help in solving the issue.

PHP PDO Convert array to different format

2 Answers2