php - 为什么 Json 无法识别 MySQL 计算值/列？

Question

大家早上好，

在我的 php 文件中，我想从 mysql 数据库获取多个值并以 JSON 进行编码。该查询包括我在 mysql-Select 中计算的值。但是当我调用 php 文件时，计算出的值为“null”。我不知道也不明白为什么。选择是正确的，因为我能够在页面上显示没有 JSON 的结果。也许您在我的代码中发现了错误或者对该习惯有解释。

我的代码如下:

<?php 

  //--------------------------------------------------------------------------
  // Example php script for fetching data from mysql database
  //--------------------------------------------------------------------------
  $host = "localhost";
  $user = "root";
  $pass = "";

  $databaseName = "Boerse";
  $tableName = "prices";

  //--------------------------------------------------------------------------
  // 1) Connect to mysql database
  //--------------------------------------------------------------------------
  //include 'DB.php';
  $con = mysql_connect($host,$user,$pass);
  $dbs = mysql_select_db($databaseName, $con);

  //--------------------------------------------------------------------------
  // 2) Query database for data
  //--------------------------------------------------------------------------
  $result = mysql_query("SELECT *
FROM (

SELECT DISTINCT ticker, date_format( date, '%Y-%m-%d %H:%i:%s' ) AS date, concat('$', round( close, 2 )) close, concat('$', round( pxchange, 2 )) pxchange, concat( round( pxpct *100, 2 ) , '%' ) pxpct
FROM (

SELECT
CASE WHEN ticker <> @pxticker
THEN @pxclose := NULL
END , p. * , (
close - @pxclose
) AS pxchange, (
close - @pxclose
) / @pxclose AS pxpct, (
@pxclose := close
), (
@pxticker := ticker
)
FROM prices p
CROSS JOIN (

SELECT @pxclose := NULL , @pxticker := ticker
FROM prices
) AS a
) AS b
ORDER BY date DESC
)inv
LEFT JOIN stocks ON ticker = short
LEFT JOIN stockmarkets ON stockmarkets.id = stocks.stockmarket
WHERE stocks.stockmarket = (
SELECT id
FROM stockmarkets
WHERE marketticker = 'NASDAQ' )
GROUP BY ticker
ORDER BY date DESC");          //query
  $array = mysql_fetch_row($result);                          //fetch result    

  //--------------------------------------------------------------------------
  // 3) echo result as json 
  //--------------------------------------------------------------------------
  echo json_encode($array);

?>

谢谢。

--- 编辑:我得到的 json 字符串 ---我得到以下 JSON 字符串:

["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"]

--- 编辑:更详细的解释我试图得到的---我试图得到的是这样的:http://tradingdesk.finanzen.net/ (网站左侧，表格中自动刷新报价)。

我有一个自己的 MySQL 表，其中包含价格，我想从中获取最新价格并刷新网站表，以便它显示最新价格。

正如我已经提到的:我有一个静态版本并且它可以工作。如果我尝试使用 MySQL、Ajax、HTML 和 PHP 进行刷新，并且不使用 JSON，则刷新后值会消失。如果我在两个或多个市场之间切换，结果就是所选市场之间的闪烁。这就像代码正在处理两个值。

--- 编辑:使用间隔刷新时出现问题的页面代码。

下面的两段代码描述了我之前所做的事情。它显示了我如何获取数据。重要的是当我使用 windows.setInterval 时它如何 react :在第一次刷新发生之前，列表显示(示例):FB - Facebook, Inc - $50, -$x, -x%

但是加载速度很慢。刷新后我才看到FB - Facebook, Inc - 50 美元

如果我在市场之间切换，例如纳斯达克和纽约证券交易所，#stocks-div 正在闪烁。如果没有刷新功能，它工作得很好，但是是静态的。

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 *
FROM (

SELECT DISTINCT ticker, date_format( date, '%Y-%m-%d %H:%i:%s' ) AS date, concat('$', round( close, 2 )) close, concat('$', round( pxchange, 2 )) pxchange, concat( round( pxpct *100, 2 ) , '%' ) pxpct
FROM (

SELECT
CASE WHEN ticker <> @pxticker
THEN @pxclose := NULL
END , p. * , (
close - @pxclose
) AS pxchange, (
close - @pxclose
) / @pxclose AS pxpct, (
@pxclose := close
), (
@pxticker := ticker
)
FROM prices p
CROSS JOIN (

SELECT @pxclose := NULL , @pxticker := ticker
FROM prices
) AS a
) AS b
ORDER BY date DESC
)inv
LEFT JOIN stocks ON ticker = short
LEFT JOIN stockmarkets ON stockmarkets.id = stocks.stockmarket
WHERE stocks.stockmarket = (
SELECT id
FROM stockmarkets
WHERE marketticker = '".$q."' )
GROUP BY ticker
ORDER BY 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 from the server, add it to the graph and set a timeout to 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 the series is longer than 20
console.log(point)
                // add the point
                chart.series[0].addPoint(eval(point), true, shift);

                // call it again after one 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 FROM 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>

--- 编辑这是视频链接link这显示了闪烁问题。

Answer 1

更新

函数.php

//Create a new class - This should be in a separate functions page
class CreateTable {

    public $columnArray; // Change this variable name to the same as the 
                                 // object at the end of getData

    function __construct() {
            $this->getData(); // A new instance of the class will run query in getData
    }

    function getData() {

        // Your query here and remove the for loop

        for($i=1; $i < 6; ++$i) {       
            $column[] =  'column' . $i;     
        }

        $this->columnArray = $column; // Change the object name "columnArray"
                                                  // to whatever you want.
                                                  // this will be your 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" to the same as the array object name
            foreach($this->columnArray as $key => $value) {
                echo '<td>' . $value . '</td>';
            }
        echo '
                </tr>
            </tbody>
        </table>';
    }

}

tableGenerate.php - 这将使用 jQuery 加载

include('functions.php');

// Generate a new instance of this class
$display = new CreateTable();

    // Give me my table
$display->displayHTML();

displayToUser.php - 这是显示您的内容的主页。 #stage 将加载表格。

<div id="stage">

</div>

jQuery - 显然确保您已连接 jQuery 库:)

var auto_refresh = setInterval(
function ()
{
$('#stage').load('tableGenerate.php').fadeIn('slow');
}, 10000); // refresh every 10000 milliseconds