Store result of a query in an array using PHP and use the array in Javascript

Question

I am trying to store the result of an SQL query to in array using PHP

 <?php   
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$ser="********";
$db="********"; 
$user="********"; 
$pass="********";

$dbDB = new PDO("odbc:Driver=ODBC Driver 13 for SQL Server;Server=********;Database=********;Port=1456", $user, $pass);

$sth = $dbDB->prepare("SELECT top 10 [Country],[Customer Name],[Purchase Number],[Part Number],[Qty],[Local Revenue] FROM ********");
$sth->execute();

$result = $sth->fetchAll(PDO::FETCH_NUM);
 ?>

Now I want to use the variable $result in JavaScript instead of defining it manually this way

    var result = [
            ["Austria", "Tech Data Service GmbH", "3508224010", "01HV707", 1, 1558.40],
            ["Austria", "Tech Data Service GmbH", "3508314557", "40M7578", 1, 102.40]
            ,...
            ];

My problem is, the variable $result is taking all the elements inside the array as a string.

I am using this variable to create a pivot table and it's causing me issue with the numbers, instead of making the SUM for example, it's concatenating them as one string. Here is my script I added in the same file after the php part

<script>
var data1 = <?php echo json_encode($result); ?>;
console.log("data is:",data1);

var data2 = [
        ["Austria", "Tech Data Service GmbH", "3508224010", "01HV707", 1, 1558.40],
        ["Austria", "Tech Data Service GmbH", "3508314557", "40M7578", 1, 102.40]
        ];

console.log("data is:",data2);       
</script>

Here is the difference between was I expect and what I am getting

is there a way to store the result of the query in an array with keeping the type of element ? Or anything I should add to the script part so that it will work.

So instead of

["Austria", "Tech Data Service GmbH", "3508224010", "01HV707", "1", "1558.400000"]

I will have

["Austria", "Tech Data Service GmbH", "3508224010", "01HV707", 1, 1558.40]

Thank you.

are you sure that the values returned from your database are actually numeric (I mean in PHP, regardless of the column type in MySQL)? AFAIK json_encode does not have a problem. However, some database drivers though (libmysql I think does this) return everything from the DB as strings regardless of the column type. Are you using libmysql or mysqlnd as the underlying MySQL driver? (This is part of your PHP config, run phpinfo() and it should tell you). What version of PHP are you running? See https://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo — ADyson, Oct 14 '17 at 18:09
Use `JSON_NUMERIC_CHECK` option in `json_encode()` to force numeric values where applicable. As for number of decimal points that could related to your float settings in db config — charlietfl, Oct 14 '17 at 18:32

score 2 · Accepted Answer · answered Oct 14 '17 at 21:10

json_encode() can accept additional parameters. The one that will help you out is JSON_NUMERIC_CHECK

JSON_NUMERIC_CHECK

Encodes numeric strings as numbers. Available since PHP 5.3.3.

Here's an example usage based on your code:

<?php
$array = ["Austria", "Tech Data Service GmbH", "3508224010", "01HV707", "1", "1558.400000"];

echo json_encode($array, JSON_NUMERIC_CHECK);

// result:
// ["Austria","Tech Data Service GmbH",3508224010,"01HV707",1,1558.4]

Store result of a query in an array using PHP and use the array in Javascript

1 Answers1