0

How do I convert this:

MySQL table

Into a valid JSON file using PHP?

I tried:

$sql="SELECT ... more code here";

$result = $pdo->query($sql);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);

$real= implode(",",array_map(function($a) { return $a["real"]; }, $rows));
$orcamento = implode(",",array_map(function($a) { return $a["orcamento"]; }, $rows));
$desvio = implode(",",array_map(function($a) { return $a["desvio"]; }, $rows));


echo "{'name': 'orcamento', 'data': [$orcamento]},
        {'name': 'real', 'data': [$real]},
        {'name': 'desvio', 'data': [$desvio]}";

That returns:

{'name': 'orcamento', 'data': [14000.00,8500.00,0.00]},
    {'name': 'real', 'data': [2038.00,120.00,15000.00]},
    {'name': 'desvio', 'data': [-11962.00,-8380.00,15000.00]}

Which according to JSONLint is invalid (and I think the reason why the rest of my code isn't working. I get:

Parse error on line 1:
{    'name': 'orcamento',
-----^
Expecting 'STRING', '}'

So my question is: How do I fix my PHP code in order to get a valid JSON?

EDIT: I also tried:

$sql="SELECT ....";

$result = $pdo->query($sql);
$rows = $result->fetchAll(PDO::FETCH_ASSOC);

$registos= json_encode($rows);

echo $registos;

That returns a valid JSON, but with the wrong format:

[
    {
        "real": "2038.00",
        "orcamento": "14000.00",
        "desvio": "-11962.00"
    },
    {
        "real": "120.00",
        "orcamento": "8500.00",
        "desvio": "-8380.00"
    },
    {
        "real": "15000.00",
        "orcamento": "0.00",
        "desvio": "15000.00"
    }
]
Improve this question
3
  • echo json_encode($rows); ? Commented Mar 27, 2014 at 21:57
  • 4
    Don't ever write your own JSON. Always use json_encode(). Commented Mar 27, 2014 at 21:58
  • @Peter: yes, that was my first attempt, it returns a valid JSON but with the wrong structure. Please see my edit. Commented Mar 27, 2014 at 22:04

1 Answer 1

Reset to default
3

To be on the safe side, use json_encode like this:

$real = array_map(function($a) { return $a["real"]; }, $rows);
$orcamento = array_map(function($a) { return $a["orcamento"]; }, $rows);
$desvio = array_map(function($a) { return $a["desvio"]; }, $rows);

echo json_encode(array(
    array('name' => 'orcamento', 'data' => $orcamento),
    array('name' => 'real', 'data' => $real),
    array('name' => 'desvio', 'data' => $desvio)
));

(but in essence in your original output the [ and ] to surround the objects are missing and you need to use double quotes " for quoting the keys and strings)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks, that is returning the numbers in 'data' surrounded by quotes, like: [ { "name": "orcamento", "data": [ "14000.00", "8500.00", "0.00" ] }, { "name": "real", "data": [ "2038.00", "120.00", "15000.00" ] }, { "name": "desvio", "data": [ "-11962.00", "-8380.00", "15000.00" ] } ]
Add a floatval() like this: $real = array_map(function($a) { return floatval($a["real"]); }, $rows);
But mind you that this will strip the .00. It doesn't make much sense to have formatted numbers in JSON. But after all, if you modify your original code to use " instead of ' it should work, too, as I wrote in my answer already
That's important, since these are financial figures, we need the decimals. Your solution is returning "name" and "data" and it should be name and data without the quotes.
You misunderstand the concept of numeric values in programming languages. If you want to have a value of any kind in a specific format, you need to use a string, therefore it needs to be in quotes, there is no way around that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.