I'm back here with this problem: I've got an API page where I get son data files in response of requests.
So I've created a js file that gets cross domain json through yql and returns json data decoded into a string I pass to php to be inserted into a MySql database and returned into a table.
So I know I can interrogate API with an url like this ..../usd-gold.json where I get informations about exchange between US dollar and Gold, but if I want to get the opposite change I should interrogate ..../gold-usd.json.
This is the question finally. How can I get multiple json interrogation in order to get back json data, decode it, combine togheter the data and send it to php. In other words: how can I make an multiple interrogation like idr-gold.json, gold-dir.json, usd-gold.json, gold-use.json and so on for all the currency, combine code, transmit it to php and work on mysql.
Here is the code I use, for one query.
JS code/file:
// JavaScript Document
$(document).ready(function(){
var container = $('#target');
$('.ajaxtrigger').click(function(){
doAjax($(this).attr('href'));
return false;
});
function doAjax(url){
// if it is an external URI
if(url.match('^http')){
// call YQL
// TEST
$.getJSON("http://query.yahooapis.com/v1/public/yql?"+
"q=select%20*%20from%20html%20where%20url%3D%22"+
encodeURIComponent(url)+
"%22&format=json'&callback=?", // QUESTO è URL cui segue la "," e poi function(data)
// this function gets the data from the successful
// JSON-P call
function(data){
// if there is data, filter it and render it out
if(data.results[0]){
var data = filterData(data.results[0]);
container.html(data);
// alert(data); // MIO TEST
document.getElementById("prova1").value = data; //MIO
// TEST
var obj = $.parseJSON(data);
document.getElementById("provabis").innerHTML = obj.buy.currency+"/"+obj.sell.currency+" "+obj.offer[0].rate+" "+obj.offer[0].amount+" "+obj.offer[0].seller.name;
// alert(obj.sell.currency);
// TEST END
// otherwise tell the world that something went wrong
} else {
var errormsg = "<p>Error: can't load the page.</p>";
container.html(errormsg);
}
}
);
// if it is not an external URI, use Ajax load()
} else {
$('#target').load(url);
}
}
// filter out some nasties
function filterData(data){
data = data.replace(/<body>/,'');
data = data.replace(/<?\/body[^>]*>/g,'');
data = data.replace(/[|
]+/g,'');
data = data.replace(/<--[\S\s]*?-->/g,'');
data = data.replace(/<noscript[^>]*>[\S\s]*?<\/noscript>/g,'');
data = data.replace(/<script[^>]*>[\S\s]*?<\/script>/g,'');
data = data.replace(/<script.*\/>/,'');
return data;
}
});
here is html code/file:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"> </meta>
<title>
Under Developement
</title>
<link type="text/css" href="styles.css" rel="stylesheet"></link>
</head>
<body>
<div id="doc" class="yui-t7">
<div id="hd" role="banner">
<h1>
Ajax with jQuery - using YQL
</h1>
</div>
<div id="bd" role="main">
<h2>
Demo
</h2>
<ul>
<li>
<a class="ajaxtrigger" href="ajaxcontent.html">
Load Ajax Content
</a>
</li>
<li>
<a class="ajaxtrigger" href="http://www.API LINK.json">
<!-- <a class="ajaxtrigger" href="http://www.API LINK.json
</a><br>
<a class="ajaxtrigger" href="http://www.API LINK.json">
<!-- <a class="ajaxtrigger" href="http://www.API LINK.json"> -->
Get cspro2.json
</a>
</li>
</ul>
<div id="target">
</div>
<h2>
Formatted List
</h2>
</div>
<div id="prova2">
</div>
<h2>
TEST
</h2>
</div>
<form action="jsontodb10.php" method="post">
Json code: <br>
<textarea id="prova1" style="width:40%;height:40em;" name="jsonCode"> </textarea>
<!-- metti qui il pulsante per submittare al php -->
<br>
<input type="submit">
</form>
<textarea id="provabis" style="width:40%;height:40em;"></textarea>
<script src="http://code.jquery.com/jquery-latest.pack.js"> </script>
<script src="code.js"></script>
<script src="using-yql7.js"></script>
</div>
</body>
</html>
and finally php code/file:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="table-db.css" rel="stylesheet" type="text/css">
<title>Database show</title>
</head>
<body>
<?php
//inserisci il reset della variabile che non manda in loop il force update
$submitVerify = $_POST["_Submit"];
if($submitVerify=="force Update"){
// unset($submitVerify);
echo ("<script type='text/javascript'>location.reload();</script>");
};
//connect to mysql db
$servername = "localhost";
$username = "my user";
$password = "";
$dbname = "my db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully <br />";
// QUI METTIAMO la verifica circa il RecordTime ed il TimeNow (così che non si registrino più di 1 Record/ 2 hour)
$timeNow = date('Y-m-d H:i:s'); // ora adesso
$time = strtotime($timeNow); // settato l'ora adesso
$timeRange = date('Y-m-d H:i:s',strtotime('-2 hours', $time)); // l'ora adesso -2 ore
$sqlsearchTime = "SELECT DateTimeSave FROM Valuta ORDER BY ID DESC LIMIT 1";
$searchresultTime = mysqli_query($conn, $sqlsearchTime);
if (mysqli_num_rows($searchresultTime) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($searchresultTime)) {
echo "
Last record in database was at: " . $row["DateTimeSave"]. "<br>";
if ($row["DateTimeSave"] > $timeRange) // se l'orario estratto dal database è < dell'orario attuale -2 ore
{
echo('hey sono passate meno di due ore, non inserirò un altro dato a meno che tu non mi forzi <br />'); // allora apri il database ed inserisci l'ultimo dato ottenuto e mostra il contenuto del database
// goto showData;
goto insertData;
}
else // se l'orario è maggiore
{
echo('si che possiamo mettere questo nuovo dato nel database <br />'); // allora mostra solo il contenuto e rimanda a al tasto che forza l'estrazione del dato e l'inserimento dei dati ne db e mostra contenuto
goto insertData;
};
}
} else {
echo "0 results";
};
// NOME funzione
insertData:
//VALUES from JSON
//read the json file contents
//$jsondata = file_get_contents('idr-gold.json');
//$jsondata = file_get_contents('idr-gold.txt');
//convert json object to php associative array
//$data = json_decode($jsondata, true);
//get the value details
$content = $_POST["jsonCode"];
//$jsondata = file_get_contents('idr-gold.txt');
//$json = json_decode($content, true);
//convert json object to php associative array
$data = json_decode($content, true);
$buycurrency = $data['buy']['currency'];
$sellcurrency = $data['sell']['currency'];
$sellrate = $data['offer'][0]['rate'];
$sellamount = $data['offer'][0]['amount'];
$sellername = $data['offer'][0]['seller']['name'];
$offer = $data['offer'];
//se l'offerta nulla metti "no record"
if($offer === NULL)
{
$sellrate = 'No Record';
$sellamount = 'No Record';
$sellername = 'No Record';
};
//VALUES RECORDSNORECORDS (OK) questo setta se c'è o meno un Record
if($sellername != NULL)
{
$recordsNoRecords = TRUE;
} else {
$recordsNoRecords = FALSE;
};
//VALUES DATE&HOUR (OK) qui setta l'ora dell'inserimento e poi inserisce i dati
date_default_timezone_set('UTC+1');
$timestamp = date('Y-m-d H:i:s');
$sqlinsert = "INSERT INTO Valuta (BuyCurrency,SellCurrency,SellRate,SellAmount,SellerName,LinktoPage,Records NoRecords,DateTimeSave)
VALUES ('$buycurrency','$sellcurrency','$sellrate','$sellamount','$sellername','test','$recordsNoRecords','$timestamp')";
if ($conn->query($sqlinsert) === TRUE) {
echo "New record created successfully <br />";
} else {
echo "Error: " . $sqlinsert . "<br>" . $conn->error;
} goto showData;
// NOME funzione
showData:
// QUERY X VEDERE
$sqlsearch = "SELECT BuyCurrency, SellCurrency, SellRate, SellAmount, SellerName, LinktoPage, DateTimeSave FROM Valuta";
$searchresult = mysqli_query($conn, $sqlsearch);
if (mysqli_num_rows($searchresult) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($searchresult)) {
echo "
Currency to buy: " . $row["BuyCurrency"]. " - Currency to sell: " . $row["SellCurrency"]. " - Sell Rate " . $row["SellRate"]. " - Amount on sale: " . $row["SellAmount"]. " - Seller Name: " . $row["SellerName"]. " - Lik to page: " . $row["LinktoPage"]. " - Date & Time of record: " . $row["DateTimeSave"]. "<br>";
}
} else {
echo "0 results";
};
$conn->close();
?>
<!-- bottone per forzare l'aggiornamento -->
<form name="update" action="" method="post">
se vuoi forza
<input type="submit" name="_Submit" value="force Update">
</form>
<?php
$submit = $_POST["_Submit"];
if($submit=="force Update"){
echo ("facciamolo!");
// goto insertData;
}else{
echo ("SUCA!!");
// goto showData;
};
?>
<br />
<br />
<br />
<br />
<?php
// TABELLA
//connect to mysql db
$servername = "localhost";
$username = "";
$password = "";
$dbname = "my db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully <br />";
$sql = "SELECT BuyCurrency,SellCurrency,SellRate,SellAmount,SellerName,LinktoPage,DateTime Save FROM Valuta ORDER BY ID DESC LIMIT 1";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
echo '<table cellpadding="0" cellspacing="0" class="db-table">';
echo '<tr><th>Buy</th><th>Sell</th><th>Rate</th><th>Amount</th> <th>Seller</th><th>Link</th><th>Time Record</th></tr>';
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row["BuyCurrency"]."</td> <td>".$row["SellCurrency"]."</td><td>".$row["SellRate"]."</td> <td>".$row["SellAmount"]."</td><td>".$row["SellerName"]."</td> <td>".$row["LinktoPage"]."</td><td>".$row["DateTimeSave"]."</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
};
$conn->close();
?>
</body>
</html>
Thx in advance for help!
UPDATE porpose of the script
The porpose of the script is to have a site where i can show the retrieved data from an api that returns json or xml.
So I have tryed the way that seemed to me the easyest to retrieve json data (xml was harder if i understood well). I found that YQL was easy to get cross-domain data with json files.
What I read than, is that json answer, when you try to access to different files at the same time, might have transfert rate limitations and I checked that json data in real time get about 1/2 sec to give answer.
So this is what I thought. I create a js form that create the url to the api answer, I recive data throw YQL, that code to php and insert it into db. So the user gets mysql tables with saved data in spite of having to wait for site to get all the data in real time.
The optimal goal for me would be: 1) have a db that every 4 hours get the json from api; 2) update data saved; 3) when user Open page could interrogate db or force json data retrieve in real time (updateing db).
That's the final porpose. Thx for your time.