从Json获取或提取数据到php并插入到mysql数据库中。

Question

谁能帮我弄清楚如何从这个从yql生成的json脚本中获取数据。我想将数据存储到mysql中，它是从这里抓取的…我想提取或获取数据AskRealtime、BidRealtime、LastTradeDate、LastTradePriceOnly、LastTradeTime、Ask

<?php
session_start();
query= Insert into table .....value ...
?>

我想从下面的json中做一些类似上面的脚本的事情。如果我可以直接将提取的数据打印到html表中的同一页，而不是将其发送到mysql并从那里提取数据，那就更好了。

`{

"query": {
    "count": 5,
    "created": "2012-05-11T01:14:55Z",
    "lang": "en-US",
    "diagnostics": {
        "publiclyCallable": "true",
        "redirect": {
            "from": "http://datatables.org/alltables.env",
            "status": "301",
            "content": "http://www.datatables.org/alltables.env"
        },
        "url": [
            {
                "execution-start-time": "425",
                "execution-stop-time": "865",
                "execution-time": "440",
                "proxy": "DEFAULT",
                "content": "http://datatables.org/alltables.env"
            },
            {
                "execution-start-time": "884",
                "execution-stop-time": "1097",
                "execution-time": "213",
                "proxy": "DEFAULT",
                "content": "http://www.datatables.org/yahoo/finance/yahoo.finance.quotes.xml"
            },
            {
                "execution-start-time": "1307",
                "execution-stop-time": "1307",
                "execution-time": "0",
                "proxy": "DEFAULT",
                "content": "http://download.finance.yahoo.com/d/quotes.csv?f=aa2bb2b3b4cc1c3c6c8dd1d2ee1e7e8e9ghjkg1g3g4g5g6ii5j1j3j4j5j6k1k2k4k5ll1l2l3mm2m3m4m5m6m7m8nn4opp1p2p5p6qrr1r2r5r6r7ss1s7t1t7t8vv1v7ww1w4xy&s=XAUUSD%3DX,XAGUSD%3DX,PLN12.NYM,PAK12.NYM,USDCAD%3DX"
            }
        ],
        "query": {
            "execution-start-time": "1105",
            "execution-stop-time": "1308",
            "execution-time": "203",
            "params": "{url=[http://download.finance.yahoo.com/d/quotes.csv?f=aa2bb2b3b4cc1c3c6c8dd1d2ee1e7e8e9ghjkg1g3g4g5g6ii5j1j3j4j5j6k1k2k4k5ll1l2l3mm2m3m4m5m6m7m8nn4opp1p2p5p6qrr1r2r5r6r7ss1s7t1t7t8vv1v7ww1w4xy&s=XAUUSD%3DX,XAGUSD%3DX,PLN12.NYM,PAK12.NYM,USDCAD%3DX]}",
            "content": "select * from csv where url=@url and columns='Ask,AverageDailyVolume,Bid,AskRealtime,BidRealtime,BookValue,Change&PercentChange,Change,Commission,ChangeRealtime,AfterHoursChangeRealtime,DividendShare,LastTradeDate,TradeDate,EarningsShare,ErrorIndicationreturnedforsymbolchangedinvalid,EPSEstimateCurrentYear,EPSEstimateNextYear,EPSEstimateNextQuarter,DaysLow,DaysHigh,YearLow,YearHigh,HoldingsGainPercent,AnnualizedGain,HoldingsGain,HoldingsGainPercentRealtime,HoldingsGainRealtime,MoreInfo,OrderBookRealtime,MarketCapitalization,MarketCapRealtime,EBITDA,ChangeFromYearLow,PercentChangeFromYearLow,LastTradeRealtimeWithTime,ChangePercentRealtime,ChangeFromYearHigh,PercebtChangeFromYearHigh,LastTradeWithTime,LastTradePriceOnly,HighLimit,LowLimit,DaysRange,DaysRangeRealtime,FiftydayMovingAverage,TwoHundreddayMovingAverage,ChangeFromTwoHundreddayMovingAverage,PercentChangeFromTwoHundreddayMovingAverage,ChangeFromFiftydayMovingAverage,PercentChangeFromFiftydayMovingAverage,Name,Notes,Open,PreviousClose,PricePaid,ChangeinPercent,PriceSales,PriceBook,ExDividendDate,PERatio,DividendPayDate,PERatioRealtime,PEGRatio,PriceEPSEstimateCurrentYear,PriceEPSEstimateNextYear,Symbol,SharesOwned,ShortRatio,LastTradeTime,TickerTrend,OneyrTargetPrice,Volume,HoldingsValue,HoldingsValueRealtime,YearRange,DaysValueChange,DaysValueChangeRealtime,StockExchange,DividendYield'"
        },
        "javascript": {
            "execution-time": "266",
            "instructions-used": "302754",
            "table-name": "yahoo.finance.quotes"
        },
        "warning": "Unable to sort response according to field 'Ask,Bid,AskRealtime,BidRealtime,LastTradeDate,LastTradePriceOnly,LastTradeTime'.",
        "user-time": "1373",
        "service-time": "653",
        "build-version": "27222"
    },
    "results": {
        "quote": [
            {
                "symbol": "XAUUSD=X",
                "Bid": "1590.9498",
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1590.5499",
                "LastTradeTime": "9:12pm",
                "Ask": "1590.15"
            },
            {
                "symbol": "XAGUSD=X",
                "Bid": null,
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/10/2012",
                "LastTradePriceOnly": "29.136",
                "LastTradeTime": "5:27pm",
                "Ask": null
            },
            {
                "symbol": "PLN12.NYM",
                "Bid": null,
                "AskRealtime": "1486.50",
                "BidRealtime": "1486.00",
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1486.00",
                "LastTradeTime": "8:44pm",
                "Ask": null
            },
            {
                "symbol": "PAK12.NYM",
                "Bid": null,
                "AskRealtime": "616.00",
                "BidRealtime": "609.40",
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "658.80",
                "LastTradeTime": "8:00pm",
                "Ask": null
            },
            {
                "symbol": "USDCAD=X",
                "Bid": "1.0033",
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1.0034",
                "LastTradeTime": "9:13pm",
                "Ask": "1.0035"
            }
        ]
    }
}

}`

指向此json脚本的链接位于此处，该json脚本是通过http://jsonlint.com/验证的Json script generated by YQL

Answer 1

如果我对您的问题理解正确的话，您想使用JSON并能够访问其中的数据吗？为了让JSON更容易使用，我建议使用下面的代码，它利用json_decode function将JSON转换成php可用的数组。(我包含了echo语句，这样您就可以看到生成的关联数组的样子)

$yql_json =
'{
"query": {
    "count": 5,
    "created": "2012-05-11T01:14:55Z",
    "lang": "en-US",
    "diagnostics": {
        "publiclyCallable": "true",
        "redirect": {
            "from": "http://datatables.org/alltables.env",
            "status": "301",
            "content": "http://www.datatables.org/alltables.env"
        },
        "url": [
            {
                "execution-start-time": "425",
                "execution-stop-time": "865",
                "execution-time": "440",
                "proxy": "DEFAULT",
                "content": "http://datatables.org/alltables.env"
            },
            {
                "execution-start-time": "884",
                "execution-stop-time": "1097",
                "execution-time": "213",
                "proxy": "DEFAULT",
                "content": "http://www.datatables.org/yahoo/finance/yahoo.finance.quotes.xml"
            },
            {
                "execution-start-time": "1307",
                "execution-stop-time": "1307",
                "execution-time": "0",
                "proxy": "DEFAULT",
                "content": "http://download.finance.yahoo.com/d/quotes.csv?f=aa2bb2b3b4cc1c3c6c8dd1d2ee1e7e8e9ghjkg1g3g4g5g6ii5j1j3j4j5j6k1k2k4k5ll1l2l3mm2m3m4m5m6m7m8nn4opp1p2p5p6qrr1r2r5r6r7ss1s7t1t7t8vv1v7ww1w4xy&s=XAUUSD%3DX,XAGUSD%3DX,PLN12.NYM,PAK12.NYM,USDCAD%3DX"
            }
        ],
        "query": {
            "execution-start-time": "1105",
            "execution-stop-time": "1308",
            "execution-time": "203",
            "params": "{url=[http://download.finance.yahoo.com/d/quotes.csv?f=aa2bb2b3b4cc1c3c6c8dd1d2ee1e7e8e9ghjkg1g3g4g5g6ii5j1j3j4j5j6k1k2k4k5ll1l2l3mm2m3m4m5m6m7m8nn4opp1p2p5p6qrr1r2r5r6r7ss1s7t1t7t8vv1v7ww1w4xy&s=XAUUSD%3DX,XAGUSD%3DX,PLN12.NYM,PAK12.NYM,USDCAD%3DX]}",
            "content": "select * from csv where url=@url and columns=\'Ask,AverageDailyVolume,Bid,AskRealtime,BidRealtime,BookValue,Change&PercentChange,Change,Commission,ChangeRealtime,AfterHoursChangeRealtime,DividendShare,LastTradeDate,TradeDate,EarningsShare,ErrorIndicationreturnedforsymbolchangedinvalid,EPSEstimateCurrentYear,EPSEstimateNextYear,EPSEstimateNextQuarter,DaysLow,DaysHigh,YearLow,YearHigh,HoldingsGainPercent,AnnualizedGain,HoldingsGain,HoldingsGainPercentRealtime,HoldingsGainRealtime,MoreInfo,OrderBookRealtime,MarketCapitalization,MarketCapRealtime,EBITDA,ChangeFromYearLow,PercentChangeFromYearLow,LastTradeRealtimeWithTime,ChangePercentRealtime,ChangeFromYearHigh,PercebtChangeFromYearHigh,LastTradeWithTime,LastTradePriceOnly,HighLimit,LowLimit,DaysRange,DaysRangeRealtime,FiftydayMovingAverage,TwoHundreddayMovingAverage,ChangeFromTwoHundreddayMovingAverage,PercentChangeFromTwoHundreddayMovingAverage,ChangeFromFiftydayMovingAverage,PercentChangeFromFiftydayMovingAverage,Name,Notes,Open,PreviousClose,PricePaid,ChangeinPercent,PriceSales,PriceBook,ExDividendDate,PERatio,DividendPayDate,PERatioRealtime,PEGRatio,PriceEPSEstimateCurrentYear,PriceEPSEstimateNextYear,Symbol,SharesOwned,ShortRatio,LastTradeTime,TickerTrend,OneyrTargetPrice,Volume,HoldingsValue,HoldingsValueRealtime,YearRange,DaysValueChange,DaysValueChangeRealtime,StockExchange,DividendYield\'"
        },
        "javascript": {
            "execution-time": "266",
            "instructions-used": "302754",
            "table-name": "yahoo.finance.quotes"
        },
        "warning": "Unable to sort response according to field \'Ask,Bid,AskRealtime,BidRealtime,LastTradeDate,LastTradePriceOnly,LastTradeTime\'.",
        "user-time": "1373",
        "service-time": "653",
        "build-version": "27222"
    },
    "results": {
        "quote": [
            {
                "symbol": "XAUUSD=X",
                "Bid": "1590.9498",
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1590.5499",
                "LastTradeTime": "9:12pm",
                "Ask": "1590.15"
            },
            {
                "symbol": "XAGUSD=X",
                "Bid": null,
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/10/2012",
                "LastTradePriceOnly": "29.136",
                "LastTradeTime": "5:27pm",
                "Ask": null
            },
            {
                "symbol": "PLN12.NYM",
                "Bid": null,
                "AskRealtime": "1486.50",
                "BidRealtime": "1486.00",
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1486.00",
                "LastTradeTime": "8:44pm",
                "Ask": null
            },
            {
                "symbol": "PAK12.NYM",
                "Bid": null,
                "AskRealtime": "616.00",
                "BidRealtime": "609.40",
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "658.80",
                "LastTradeTime": "8:00pm",
                "Ask": null
            },
            {
                "symbol": "USDCAD=X",
                "Bid": "1.0033",
                "AskRealtime": null,
                "BidRealtime": null,
                "LastTradeDate": "5/11/2012",
                "LastTradePriceOnly": "1.0034",
                "LastTradeTime": "9:13pm",
                "Ask": "1.0035"
            }
        ]
    }
}
}';

$yql_results = json_decode($yql_json, true);
echo "<pre>".print_r($yql_results, true)."</pre>";

$yql_json输入本身就是字符串形式的json (不管您如何收集和存储它)，传递给函数的TRUE值使函数返回一个存储在$yql_result中的关联数组。在此之后，您可以轻松访问它的所有项目。我希望我至少回答了你问题的一部分。祝你的项目好运。