CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 1015

How to exclude apostrophe from JSON array?

I am trying to output the following PHP script to output a JSON array that does not include apostrophes around the values because they are numbers.

Using the code below I keep getting the following output:

[{"name":"person1","sumValue":"200"},{"name":"person2","sumValue":"100"},{"sumTotal":"300"}]

The following is the PHP script I currently have that creates the array seen above. Numbers should not have "" around them and should instead look like this

[{"name":"person1","sumValue":200},{"name":"person2","sumValue":100},{"sumTotal":300}]

What can I change in the script to get this result?

if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql =  "SELECT name, SUM(value) as numvalue 
         FROM Table1
            LEFT JOIN Table2 USING(DevName)
         WHERE name <> '' 
         and name is not null
         GROUP BY name";

if ($result = mysqli_query($con, $sql)) {

    $resultArray = array();
    $tempArray = array();

    // you want an array of objects, so create an object to sum the sub totals
    $total = new stdClass;
    $total->sumTotal = 0;

    while($row = $result->fetch_object()) {
        $tempArray = $row;
        array_push($resultArray, $tempArray);

        $total->sumTotal = bcadd($total->sumTotal, $row->numvalue);
    }

    $resultArray[] = $total;
    echo json_encode($resultArray);
}

mysqli_close($con);

Upvotes: 1

Views: 286

Answers (4)

Hugo Dias
Hugo Dias

Reputation: 153

You can make use of the options parameters in your call to the json_encode method to change the encoding. According to the docs of json_encode:

The encoding is affected by the supplied options and additionally the encoding of float values depends on the value of serialize_precision.

So, by the docs we have the constant JSON_NUMERIC_CHECK, that:

Encodes numeric strings as numbers. Available as of PHP 5.3.3.

And your code would be: 

$resultArray[] = $total;
echo json_encode($resultArray,JSON_NUMERIC_CHECK);

Here you find the full documentation:

https://php.net/manual/en/function.json-encode.php

https://www.php.net/manual/en/json.constants.php

Upvotes: 2

aynber
aynber

Reputation: 23011

You want to add JSON_NUMERIC_CHECK to the json_encode:

$arr = [1,3,'abc', "34"];
json_encode($arr); // [1,3,"abc","34"]
json_encode($arr, JSON_NUMERIC_CHECK); // [1,3,"abc",34]

So you would use 

echo json_encode($resultArray, JSON_NUMERIC_CHECK);

Upvotes: 1

M. Eriksson
M. Eriksson

Reputation: 13635

MySQL (via PHP, maybe other languages as well) tends to return all values as strings, so if you want them to be numerics, you need to cast them as such.

while($row = $result->fetch_object()) {
    // Cast the numvalue
    $row->numvalue = (int)$row->numvalue;         

    $tempArray = $row;
    array_push($resultArray, $tempArray);

    // Summing the values is as easy as this (which also will return a numeric value)
    $total->sumTotal += $row->numvalue;
}

If you really need to use bcadd(), then you need to cast that result as well since that function returns strings.

$total->sumTotal = (int)bcadd($total->sumTotal, $row->numvalue);

If you're expecting to get a float, change it to: (float) instead of (int).

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

the select return the values as string so

try cast properly the string as a valid number .. (in this case i use int but if you need you can use floatval() for float )

 while($row = $result->fetch_object()) {
        $tempArray['name'] = $row['name'];
        $tempArray['numvalue '] = intval($row['numvalue']) ;
        array_push($resultArray, $tempArray);

        $total->sumTotal = bcadd($total->sumTotal, $row->numvalue);
    }

Upvotes: 0

Related Questions