php - Why does Json not recognize MySQL calculated values/columns? -

good morning everybody,

in php-file want several values mysql-database , encode in json. query includes values calculate within mysql-select. when call php-file calculated values 'null'. don't know , don't understand why. select correct, because able show results without json on page. maybe see mistake in code or has explanation habbit.

my code following:

<?php     //--------------------------------------------------------------------------   // example php script fetching data mysql database   //--------------------------------------------------------------------------   $host = "localhost";   $user = "root";   $pass = "";    $databasename = "boerse";   $tablename = "prices";    //--------------------------------------------------------------------------   // 1) connect mysql database   //--------------------------------------------------------------------------   //include 'db.php';   $con = mysql_connect($host,$user,$pass);   $dbs = mysql_select_db($databasename, $con);    //--------------------------------------------------------------------------   // 2) query database data   //--------------------------------------------------------------------------   $result = mysql_query("select * (  select distinct ticker, date_format( date, '%y-%m-%d %h:%i:%s' ) date, concat('$', round( close, 2 )) close, concat('$', round( pxchange, 2 )) pxchange, concat( round( pxpct *100, 2 ) , '%' ) pxpct (  select case when ticker <> @pxticker @pxclose := null end , p. * , ( close - @pxclose ) pxchange, ( close - @pxclose ) / @pxclose pxpct, ( @pxclose := close ), ( @pxticker := ticker ) prices p cross join (  select @pxclose := null , @pxticker := ticker prices ) ) b order date desc )inv left join stocks on ticker = short left join stockmarkets on stockmarkets.id = stocks.stockmarket stocks.stockmarket = ( select id stockmarkets marketticker = 'nasdaq' ) group ticker order date desc");          //query   $array = mysql_fetch_row($result);                          //fetch result        //--------------------------------------------------------------------------   // 3) echo result json    //--------------------------------------------------------------------------   echo json_encode($array);  ?> 

thank you.

--- edit: json-string --- following json-string:

["tsla","2014-03-22 16:06:40","$55.00",null,null,"5","tesla motors, inc.","tsla","122590000","1","1","1","national association of securities dealers automated quotations","nasdaq","2"] 

--- edit: more detailled explanation try --- try this: http://tradingdesk.finanzen.net/ (on left of website, self-refreshing quotations in table).

i have own mysql table prices , want latest prices , refresh website table shows latest prices.

as mentioned: have static version of , works. if try refresh mysql, ajax , html , php , without json values disappear after refreshing. , if switching between 2 or more markets result flicker between selected markets. code handling 2 values.

--- edit: code of page problem while using intervals refresh.

following 2 codes describe have done before. shows how data. important how reacts when use windows.setinterval: before first refresh happens list shows (example): fb - facebook, inc - $50, -$x, -x%

but loading slow. after refresh see fb - facebook, inc - $50

if switch between markets, e.g. nasdaq , nyse, #stocks-div flickering. without refresh-function works well, static.

getuser_exp.php

 <?php if( $_get["q"] ) { $q = $_get['q']; //$q = 'sp500';  $con = mysqli_connect('localhost','root','','boerse'); if (!$con)   {   die('could not connect: ' . mysqli_error($con));   }   $sql="select * (  select distinct ticker, date_format( date, '%y-%m-%d %h:%i:%s' ) date, concat('$', round( close, 2 )) close, concat('$', round( pxchange, 2 )) pxchange, concat( round( pxpct *100, 2 ) , '%' ) pxpct (  select case when ticker <> @pxticker @pxclose := null end , p. * , ( close - @pxclose ) pxchange, ( close - @pxclose ) / @pxclose pxpct, ( @pxclose := close ), ( @pxticker := ticker ) prices p cross join (  select @pxclose := null , @pxticker := ticker prices ) ) b order date desc )inv left join stocks on ticker = short left join stockmarkets on stockmarkets.id = stocks.stockmarket stocks.stockmarket = ( select id stockmarkets marketticker = '".$q."' ) group ticker order date desc";   $result = mysqli_query($con,$sql);  if($result === false) {     die(mysql_error()); // todo: better error handling     }  echo "<table border='0'> <tr> <th>id</th> <th>name</th> <th>price</th> <th>chng</th> <th>%</th> </tr>";  while($row = mysqli_fetch_array($result))   {   echo "<tr>";    echo "<td>" . $row['ticker'] . "</td>";   echo "<td>" . $row['name'] . "</td>";   echo "<td>" . $row['close'] . "</td>";    echo "<td>" . $row['pxchange'] . "</td>";   echo "<td>" . $row['pxpct'] . "</td>";      echo "</tr>";   } echo "</table>";  mysqli_close($con); } ?>  

index.php

<html> <head> <script type="text/javascript" charset="utf-8" src="http://code.jquery.com/jquery-latest.min.js"></script> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script> <script src="http://code.highcharts.com/highcharts.js"></script> <script src="http://code.highcharts.com/modules/exporting.js"></script> <script> $(document).ready(function(){  $(".information").click(function () { var str = $(this).closest("tr").find("#nr").text();   $(document).ready(function() { // window.setinterval(function(){                 $.get("getuser_exp.php",              { q:str },              function(data) { $('.stock').html(data);              } //function data            ); }, 3000); //          }); //document ready }); //information click   }); //document ready  //highland charts     var chart; // global      /**      * request data server, add graph , set timeout request again      */     function requestdata() {         $.ajax({             url: 'live-server-data.php',              success: function(point) {                 var series = chart.series[0],                     shift = series.data.length > 20; // shift if series longer 20 console.log(point)                 // add point                 chart.series[0].addpoint(eval(point), true, shift);                  // call again after 1 second                 settimeout(requestdata, 1000);               },             cache: false         });     }      $(document).ready(function() {         chart = new highcharts.chart({             chart: {                 renderto: 'chart',                 defaultseriestype: 'area',                 events: {                     load: requestdata                 }             },             title: {                 text: 'live random data'             },             xaxis: {                 type: 'datetime',                 tickpixelinterval: 150,                 maxzoom: 20 * 1000             },             yaxis: {                 minpadding: 0.2,                 maxpadding: 0.2,                 title: {                     text: 'value',                     margin: 80                 }             },             series: [{                 name: 'random data',                 data: []             }]         });          });  </script> </head> <title></title> <link rel="stylesheet" type="text/css" href="css/style.css">  <body> <script src="js/highstock.js"></script> <script src="js/exporting.js"></script> <? $con = mysqli_connect('localhost','root','','boerse'); $sql="select marketticker, marketname stockmarkets";         $result = mysqli_query($con,$sql);  ?>       <!-- upper navigation --> <div class="nacon">  <div class="tr">   <div class="td"><a href="index.htm">introduction</a></div>   <div class="td"><a href="tradingdesk.php?id=1">stocks</a></div>   <div class="td"><a href="tradingdesk.php?id=2">bonds</a></div>   <div class="td"><a href="tradingdesk.php?id=3">forex</a></div>   <div class="td"><a href="wallet.php">wallet</a></div>  </div> </div>    <!-- choosing markets --> <div class="selection">       <div class="markets"> test <? echo "<table> <thead border='0'> <tr> <th>index</th> <th>name</th> </tr> </thead> <tbody border='1'>"; while($row = mysqli_fetch_array($result))   {   echo "<tr>";   echo "<td id='nr' class='information'>" . $row['marketticker'] . "</td>";   echo "<td>" . $row['marketname'] . "</td>";   echo "</tr>";   } echo "</tbody></table>"; date_default_timezone_set('asia/tokyo'); $current_time = date('g:i:s'); echo $current_time; ?>       </div>       <!-- choosing stocks -->       <div class="stock" >        </div>  </div>    <!-- chart --> <div id="chart" style="width:600px;height:300px;">    </div>    <!-- depot --> <div id="depot">     <ul id="navigation">     <li><a href="einfuehrung.htm">depots</a></li>     <li><a href="mehrspaltige.htm">orders</a></li>     <li><a href="fixbereiche.htm">fixe bereiche mit css-basierten layouts</a></li>     <li><a href="navigationsleisten.htm">css-basierte navigationsleisten</a></li>     <li><a href="browserweichen.htm">css-browserweichen</a></li>   </ul>   <img src="../../../src/logo.gif" alt="selfhtml">   <p><a href="../fixbereiche.htm#definieren">zurück</a></p> </div>    <!-- ordermask --> <div id="ordermask">     <ul id="navigation">     <li><a href="einfuehrung.htm">depots</a></li>     <li><a href="mehrspaltige.htm">orders</a></li>     <li><a href="fixbereiche.htm">fixe bereiche mit css-basierten layouts</a></li>     <li><a href="navigationsleisten.htm">css-basierte navigationsleisten</a></li>     <li><a href="browserweichen.htm">css-browserweichen</a></li>   </ul>   <img src="../../../src/logo.gif" alt="selfhtml">   <p><a href="../fixbereiche.htm#definieren">zurück</a></p> </div>    <!-- communication --> <div id="communication">     <ul id="navigation">     <li><a href="einfuehrung.htm">depots</a></li>     <li><a href="mehrspaltige.htm">orders</a></li>     <li><a href="fixbereiche.htm">fixe bereiche mit css-basierten layouts</a></li>     <li><a href="navigationsleisten.htm">css-basierte navigationsleisten</a></li>     <li><a href="browserweichen.htm">css-browserweichen</a></li>   </ul>   <img src="../../../src/logo.gif" alt="selfhtml">   <p><a href="../fixbereiche.htm#definieren">zurück</a></p> </div>  </body> </html> 

--- edit here video link link shows flickering problem.

update

functions.php

//create new class - should in separate functions page class createtable {      public $columnarray; // change variable name same                                   // object @ end of getdata      function __construct() {             $this->getdata(); // new instance of class run query in getdata     }      function getdata() {          // query here , remove loop          for($i=1; $i < 6; ++$i) {                    $column[] =  'column' . $i;              }          $this->columnarray = $column; // change object name "columnarray"                                                   // whatever want.                                                   // array      }      function displayhtml() {                     // basic table          echo '         <table>             <thead>                 <tr>                     <th>heading1</th>                     <th>heading2</th>                     <th>heading3</th>                     <th>heading4</th>                     <th>heading5</th>                 </tr>             </thead>             <tbody>                 <tr>';                             // change "columnarray" same array object name             foreach($this->columnarray $key => $value) {                 echo '<td>' . $value . '</td>';             }         echo '                 </tr>             </tbody>         </table>';     }  } 

tablegenerate.php - loaded jquery

include('functions.php');  // generate new instance of class $display = new createtable();      // give me table $display->displayhtml(); 

displaytouser.php - main page displays content. #stage load table.

<div id="stage">  </div> 

jquery - ensure have jquery library connected :)

var auto_refresh = setinterval( function () { $('#stage').load('tablegenerate.php').fadein('slow'); }, 10000); // refresh every 10000 milliseconds