Search code examples
phpmysqlxmlloopsapi-eveonline

Loop through XML and only "look" at specific ID

in my mysql database I have several rows each containing an item id. What I would like to do is update each of the "placidRate" columns for what is contained within the XML document of the very large URL below. Example, for typeID 1230, it should find the rate of 1230 in my db and match it with the rate of $type->sell->min. That is the end goal. Right now all I'm looking for it to do is echo the old rate (db) and the new rate (xml) next to each other. 

while($row = mysql_fetch_assoc($result)){
    $oldTypeID = $row['typeID'];

    $urlPlacid = 'http://api.eve-central.com/api/marketstat?typeid=1230&typeid=17470&typeid=17471&typeid=1228&typeid=17463&typeid=17464&typeid=1224&typeid=17459&typeid=18&typeid=17455&typeid=17456&typeid=1227&typeid=17867&typeid=17868&typeid=20&typeid=17452&typeid=17453&typeid=1226&typeid=17448&typeid=17449&typeid=1231&typeid=17444&typeid=17445&typeid=21&typeid=17440&typeid=17441&typeid=1229&typeid=17865&typeid=17866&typeid=1232&typeid=17436&typeid=17437&typeid=19&typeid=17466&typeid=17467&typeid=1225&typeid=17432&typeid=17433&typeid=1223&typeid=17428&typeid=17429&typeid=22&typeid=17425&typeid=17426&typeid=11396&typeid=17869&typeid=17870&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=40&typeid=39&typeid=11399&regionlimit=10000048';
    $xmlPlacid = simplexml_load_file($urlPlacid);
    foreach($xmlPlacid->marketstat->type as $type){
         $newRate = $type->sell->min;
         if($type[id] = $oldTypeID){
               echo "old placid rate: ".$row['placidRate']." || new placid rate: ".$newRate;
         }
    else{}

The problem with this code, is that it returns

old placid rate: 14.5 || new placid rate: 12.00old placid rate: 14.5 || new placid rate: 25.00old placid rate: 14.5 || new placid rate: 17.00old placid rate: 14.5 || new placid rate: 25.21old placid rate: 14.5 || new placid rate: 22.01old placid rate: 14.5 || new placid rate: 30.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 55.00old placid rate: 14.5 || new placid rate: 80.00old placid rate: 14.5 || new placid rate: 85.00old placid rate: 14.5 || new placid rate: 94.00old placid rate: 14.5 || new placid rate: 90.00old placid rate: 14.5 || new placid rate: 120.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 290.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 1087.06old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 0.00old placid rate: 14.5 || new placid rate: 4.00old placid rate: 14.5 || new placid rate: 10.00old placid rate: 14.5 || new placid rate: 40.00old placid rate: 14.5 || new placid rate: 143.00old placid rate: 14.5 || new placid rate: 819.00old placid rate: 14.5 || new placid rate: 2599.00old placid rate: 14.5 || new placid rate: 770.00old placid rate: 14.5 || new placid rate: 0.00

And I'm not entirely sure why...

One step closer to updating the sql table. edited once more

$market = new eveCentral();
while($row = mysql_fetch_assoc($result2)){
    $oldTypeID = $row['typeID'];
    $oldRate = $row['placidRate'];
    $newPlacidRate = $market->placidMin($oldTypeID);
    $newForgeRate = $market->forgeMin($oldTypeID);
    $newUniverseRate = $market->universeMin($oldTypeID);

    $updateQuery = "UPDATE itemRates SET placidRate = $newPlacidRate, forgeRate = $newForgeRate, universeRate = $newUniverseRate WHERE typeID = $oldTypeID";
Solution

  • Apart from the problem with the wrong operator in the if comparison (as @GBD has outlined), you have a few issues here:

    1. You are fetching the XML from eve-central.com for each database result row. But the XML does not change. Move it above the database while query.
    2. As @Alex has answered, use xpath to locate the item with the ID you're looking for.

    A view from above:

    $marketstat = new EveCentralMarketstat();

while ($row = mysql_fetch_assoc($result)) {
    $oldTypeID = $row['typeID'];
    $oldRate   = $row['placidRate'];
    $newRate   = $marketstat->getSellMinByType($oldTypeID);
    if (FALSE !== $newRate) {
        echo "old placid rate: " . $oldRate . " || new placid rate: " . $newRate;
    }
}

    Definition for the EveCentralMarketstat class:

    Add the functions you need for more data access, also the constructor can be improved depending on which information you need, just a quick outline how you can improve.

    /**
 * Wrapper Class for Even Central API Market Statistics
 */
class EveCentralMarketstat
{
    /**
     * @var SimpleXMLElement
     */
    private $xml;

    public function __construct() {

        $urlPlacid = 'http://api.eve-central.com/api/marketstat?typeid=1230&typeid=17470&typeid=17471&typeid=1228&typeid=17463&typeid=17464&typeid=1224&typeid=17459&typeid=18&typeid=17455&typeid=17456&typeid=1227&typeid=17867&typeid=17868&typeid=20&typeid=17452&typeid=17453&typeid=1226&typeid=17448&typeid=17449&typeid=1231&typeid=17444&typeid=17445&typeid=21&typeid=17440&typeid=17441&typeid=1229&typeid=17865&typeid=17866&typeid=1232&typeid=17436&typeid=17437&typeid=19&typeid=17466&typeid=17467&typeid=1225&typeid=17432&typeid=17433&typeid=1223&typeid=17428&typeid=17429&typeid=22&typeid=17425&typeid=17426&typeid=11396&typeid=17869&typeid=17870&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=40&typeid=39&typeid=11399&regionlimit=10000048';
        $this->xml = simplexml_load_file($urlPlacid);
    }

    /**
     * @param $typeID
     * @return bool|string String value if $typeID is found, FALSE on error
     */
    public function getSellMinByType($typeID) {

        $result = $this->xml->xpath("/*/marketstat/type[@id = '$typeID']/sell/min");
        if (!$result) {
            return FALSE;
        }
        return (string)$result[0];
    }
}