<?php
$url = "http://rates.fxcm.com/RatesXML";
$xml = simplexml_load_file($url);
print_r($xml);
?>
This script really works and the output is here:
http://sitoxte.com/test%20mercato/array.php
But what I want to do is put this data to MySQL tabs, I need some help because I want to store the data each minute.
So i Try:
json-to-mysql
but I think that array in xml variable is not adeguate.
I want create a for cicle
and create 69 table like this:
table 1 eur/usd
id - Bid - Ask - High - Low - Direction - Last - timestamp
1
2
3
4...
and so on
refresh mode is simple and I do in this way whit javascript:
<script>
setInterval(function () {}, 3000);
var myVar=setInterval(function(){myTimer()},10000);
function myTimer()
{
var d = new Date();
document.getElementById("demo").innerHTML = d.toLocaleTimeString();
location.reload();
}
</script>
connetion is simple too and is like this:
//Host:
$localhost="******";
//database
$mioblog=*******;
//Nome utente:
$username=********;
//Password:
$password=*******;
// connessione a MySQL con l'estensione MySQLi
$mysqli = new mysqli("$localhost", "$username", "$password", $mioblog);
// verifica dell'avvenuta connessione
if (mysqli_connect_errno()) {
// notifica in caso di errore
echo "Errore in connessione al DBMS: ".mysqli_connect_error();
// interruzione delle esecuzioni i caso di errore
exit();
}
else
{
// notifica in caso di connessione attiva
echo "Connessione avvenuta con successo";
}
This depends on what you're using for your database, but assuming PDO, something like the below. If you need more details then look up a MySQL tutorial, but hopefully this gets you started and shows you how to traverse the XML.
foreach($xml->Rate as $rate) {
$query = "INSERT INTO tblrate (time, symbol, bid) VALUES (NOW(), :symbol, :bid)";
$query = $pdo->prepare($query);
$query->execute(array(
':symbol' => $rate->@attributes['Symbol'],
':bid' => $rate->Bid;
));
}
edit: If you only need one currency, something like this should work
foreach($xml->Rate as $rate) {
if($rate->@attributes['Symbol'] == 'EURUSD') {
$query = "INSERT INTO tblrate (time, bid) VALUES (NOW(), :bid)";
$query = $pdo->prepare($query);
$query->execute(array(
':bid' => $rate->Bid;
));
}
}
My friend Biagio help me to write code:
foreach($xml->children() as $xml_child){
$Symbol = $xml_child['Symbol'];
$Bid = $xml_child->Bid;
$Ask = $xml_child->Ask;
$High = $xml_child->High;
$Low = $xml_child->Low;
$Direction = $xml_child->Direction;
$Last = $xml_child->Last;
echo('$Symbol = '.$Symbol.'<br>');
echo('$Bid = '.$Bid.'<br>');
}
final code of php scrip that write in mysql tables every second forex data: ...
<body id="top">
<!-- <button onclick="myFunction()">Reload page</button> -->
<a id=demo> <a>
...
<?php
echo date("F j, Y, g:i a", time()).'<br>';
$html="";
$url = "http://rates.fxcm.com/RatesXML";
$xml = simplexml_load_file($url);
//Host:
//echo('var_dump( $xml)<br>');
//var_dump( $xml);
//SimpleXMLElement
//Host:
$localhost="----";
//database
$mioblog=----;
//Nome utente:
$username=-----;
//Password:
$password=-----;
// connessione a MySQL con l'estensione MySQLi
$mysqli = new mysqli("$localhost", "$username", "$password", $mioblog);
// verifica dell'avvenuta connessione
if (mysqli_connect_errno()) {
// notifica in caso di errore
echo "Errore in connessione al DBMS: ".mysqli_connect_error();
// interruzione delle esecuzioni i caso di errore
exit();
}
else {
// notifica in caso di connessione attiva
// echo "Connessione avvenuta con successo";
}
// sql to create table
// sql to create table
foreach($xml->children() as $xml_child){
$Symbol = $xml_child['Symbol'];
$Bid = $xml_child->Bid;
$Ask = $xml_child->Ask;
$High = $xml_child->High;
$Low = $xml_child->Low;
$Direction = $xml_child->Direction;
$Last = $xml_child->Last;
// sql to create table
/*
$sql = "CREATE TABLE $Symbol (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Bid VARCHAR(30) NOT NULL,
Ask VARCHAR(30) NOT NULL,
High VARCHAR(30) NOT NULL,
Low VARCHAR(30) NOT NULL,
Direction VARCHAR(30),
Last VARCHAR(30) NOT NULL,
reg_date TIMESTAMP
)";*/
$sql = "INSERT INTO $Symbol (Bid,Ask,High,Low,Direction,Last)
VALUES ('$Bid','$Ask',$High,$Low,$Direction,'$Last')";
if ($mysqli->query($sql) === TRUE) {
// echo "Inserito in table ".$Symbol." / bid=".$Bid." / ask=".$Ask."/ High=".$High."/ Low=".$Low."/ Direction=".$Direction."/ Last=".$Last."<br>";
} else {
echo "Error creating table: " . $mysqli->error;
}
// echo('$Symbol = '.$Symbol.'<br>');
//echo('$Bid = '.$Bid.'<br>');
//echo('$Ask = '.$Ask.'<br>');
//echo('$High = '.$High.'<br>');
//echo('$Low = '.$Low.'<br>');
// echo('$Direction = '.$Direction.'<br>');
// echo('$Last = '.$Last.'<br>');
}
$mysqli-->close();
?>
....