使用php和simplexml_load_file从xml中提取外汇数据

Question

<?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";
}

Answer 1

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;
        ));
    }
}