user1383088
Reputation:
fetch or extract data from Json to php and insert into mysql database
Can any one help me out how to fetch the data from this json script generated from yql . I want to store the data to mysql which is grabbed from here... I want to extract or grab datas AskRealtime, BidRealtime, LastTradeDate, LastTradePriceOnly, LastTradeTime, Ask
<?php
session_start();
query= Insert into table .....value ...
?>
I want to do something like the above script from the below json . better if i can directly print the extracted data to the same page in one of the html table without sending it to mysql and pulling data from there ...
`{
"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"
}
]
}
}
}`
The link to this json script is here Json script generated by YQL The json script is verified from http://jsonlint.com/
Upvotes: 0
Views: 1171
Answers (1)
EmmanuelG
Reputation: 1051
If I understand your question correctly, you want to take that JSON and be able to access the data within? To make the JSON a bit easier to use I would suggest using the following code which makes use of the json_decode function which will transform the JSON into a php usable array. (I included the echo statement so that you can see what the resulting associative array looks like)
$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>";
The $yql_json input is the json itself as a string (however you collect and store it) and the value of true being passed into the function makes the function return an associative array that gets stored in $yql_result. After this you can access all of it's items easily. I hope I answered at least part of your question. Good luck on your project.
Upvotes: 1
Related Questions
- Php code to get json data from mysql database
- Parsing JSON data and inserting to MySQL
- Getting data from MYSQL into JSON using PHP
- how to extract mysql data into json using php
- How to retrive php data from mysql and convert it to json?
- Json into mysql
- JSON - PHP - MySql
- How to extract json data and insert in mysql php
- PHP - get data from db to JSON
- Parse JSON to mySQL