I am trying to plot bar graph using chart.js and from mssql database.I am able to retrieve data from the database in array.I have used following code,
<?php
$serverName = "PC";
$uid = "sa";
$pwd = "PC#1234";
$databaseName = "climate";
$dsn = "sql";
$conn= odbc_connect ($dsn ,$uid ,$pwd);
if(!$conn){
echo('Connection Failed');
}
$sql="select convert(nvarchar(100), DVCDTxp_Datetime,106) )";
$rs=odbc_exec($conn,$sql);
if (!$rs){
exit("Error in SQL");
}
echo odbc_result_all($rs);
$response = array();
for($i=1;$i<= odbc_num_rows($rs);$i++){
$row = odbc_fetch_array($rs,$);
$response[] = $row;
}
?>
I am getting following output,
DATE AVG_VAL
06 Jun 2015 5.466593
08 Jun 2015 1.774121
09 Jun 2015 .729223
11 Jun 2015 3.329457
12 Jun 2015 2.344660
I want the following output ,
DATE=["06 Jun 2015","08 Jun 2015","09 Jun 2015","11 Jun 2015","12 Jun 2015"]
VALUE=["5.466593","1.774121",".729223","3.329457","2.344660"]
You are creating array $response so assuming array keys to be indexed and starting from zero.
If not, use array_values() inside json_encode() while echoing.
$all_dates = array_map(function($val){ return $val['DATE']; }, $response);
$all_values = array_map(function($val){ return (float)$val['AVG_VAL']; }, $response);
Use as,
DATE = <?php echo json_encode($all_dates)?>
VALUE = <?php echo json_encode($all_values)?>
I am not used to odbc_fetch_array but i think there should $i instead of just $
Also if you can fetch record you can use array_push() in storing those in array as
$date=array();
$value=array();
in your for loop
array_push($date,$row['date']);
array_push($value,$row['avg_value']);
If your $response is in this shape, perhaps array_column can help:
$response = array(
array("DATE" => "06 Jun 2015", "AVG_VAL" => 5.466593),
array("DATE" => "08 Jun 2015", "AVG_VAL" => 1.774121),
array("DATE" => "09 Jun 2015", "AVG_VAL" => 0.729223),
array("DATE" => "11 Jun 2015", "AVG_VAL" => 3.329457),
array("DATE" => "12 Jun 2015", "AVG_VAL" => 2.344660)
);
$date = array_column($response, 'DATE');
$value = array_column($response, 'AVG_VAL');