mySQL Comparing?

jmutton · June 11, 2010, 11:14am

Hi there,

(Not exactly sure thats the correct topic for this post - I’m not sure what to call it! :goof:)

I’m running a cronjob every 5 minutes past the hour every hour. The data from this is being parsed from an XML document and then being insert into a database. This all works fine.

Example XML File Contents:

<?xml version='1.0' encoding='UTF-8'?>
<eveapi version="2">
  <currentTime>2007-12-16 11:21:59</currentTime>
  <result>
    <rowset name="solarSystems" key="solarSystemID" columns="solarSystemID,shipKills,factionKills,podKills">
      <row solarSystemID="30001343" shipKills="0" factionKills="17" podKills="0" />
      <row solarSystemID="30002671" shipKills="0" factionKills="340" podKills="0" />
      <row solarSystemID="30005327" shipKills="0" factionKills="21" podKills="0" />
      <row solarSystemID="30002410" shipKills="0" factionKills="3" podKills="0" />
      <row solarSystemID="30001082" shipKills="0" factionKills="3" podKills="0" />
      <row solarSystemID="30001105" shipKills="0" factionKills="6" podKills="0" />
      ...
      <row solarSystemID="30001937" shipKills="0" factionKills="14" podKills="0" />
      <row solarSystemID="30003560" shipKills="0" factionKills="3" podKills="0" />
      <row solarSystemID="30002478" shipKills="3" factionKills="15" podKills="2" />
      <row solarSystemID="30004101" shipKills="0" factionKills="22" podKills="0" />
    </rowset>
    <dataTime>2007-12-16 10:57:53</dataTime>
  </result>
  <cachedUntil>2007-12-16 12:21:59</cachedUntil>
</eveapi>

http://wiki.eve-id.net/APIv2_Map_Kills_XML

However if a Solar System has had zero shipkills, factionkills and podkills the solarsystem does notappear in the hourly XML file. So if the solarsystem ID is not in the xml file I need to assume 0’s for each.

I have another database table with all of the solarsystem IDs listed.

What I’m trying to do is doing some sort of compare?? on the two tables and if the solarsystem id does not appear in the XML file I need to insert 0’s for each.

If someone could point me in the right direction that would be great!

Thanks very much,
Jase

P.S. I hope I’m making sense!!

jmutton · June 13, 2010, 1:33pm

I’ve just submitted a support ticket to my webhost explaining the issue etc… I very much doubt they’ll have a clue. The last support ticket I sent about nginx blocking custom headers never got sorted.

Who do you use Anthony?

Thanks again,
Jase

AnthonySterling · June 13, 2010, 1:45pm

Afternoon Jase,

My hosts? Well, I have few. But all are VPS solutions so I have full control, it’s a learning curve though!

They’re currently spread over 1&1 and [URL=“http://www.linode.com/”]Linode.

AnthonySterling · June 12, 2010, 5:31pm

Lets put you out of your misery huh?


<?php
$xml = new SimpleXMLElement(
  'http://api.eveonline.com/map/Kills.xml.aspx',
  null,
  true
);

$datetime = date('Y-m-d H:00');

$ids = array();

$sql = "INSERT INTO edb_kills (kills_date, kills_solarSystemID, kills_shipKills, kills_factionKills, kills_podKills) VALUES";
foreach($xml->result->rowset->row as $row){
  array_push(
    $ids,
    (string)$row['solarSystemID']
  );
  $sql .= sprintf("('&#37;s', '%s', %d, %d, %d),",
    $datetime,
    $row['solarSystemID'],
    $row['shipKills'],
    $row['factionKills'],
    $row['podKills']
  );
}

$res = mysql_query(sprintf(
  "SELECT solarSystemID FROM mapSolarSystems WHERE solarSystemID NOT IN ('%s')",
  implode(
    "','",
    $ids
  )
));

if(0 < mysql_num_rows($res)){
  while($row = mysql_fetch_assoc($res)){
    $sql .= sprintf("('%s', '%s', %d, %d, %d),",
      $datetime,
      $row['solarSystemID'],
      0,
      0,
      0
    );
  }
}

mysql_query(rtrim($sql, ','));

?>

AnthonySterling · June 12, 2010, 6:43pm

Sorry Jase, I just can’t seem to see how that is happening.

We empty the table and the insert the value we get back from the API…

Are you definitely looking at the edb_kills table, same database?

StarLion · June 12, 2010, 6:51pm

It might have something to do with Anthony misspelling the table name in the Truncate query?

jmutton · June 12, 2010, 6:19pm

Overview

Returns the number of kills in solarsystems within the last hour. Only solar system where kills have been made are listed, so assume zero in case the system is not listed.

taken from http://wiki.eve-id.net/APIv2_Map_Kills_XML

I’m running that server side and refreshing the XML on my computer at the same time. Very odd!

jmutton · June 13, 2010, 3:09am

I’ve got a feeling its something to do with my web host. I’ve just setup a local server using WAMPSERVER and the XML feed directly matches the XML echo and the values pushed into the database.

http://img189.imageshack.us/img189/9591/localserver.png

Jase

jmutton · June 12, 2010, 6:02pm

Here it is:

http://img339.imageshack.us/img339/9536/notmatching.png

Jase

AnthonySterling · June 12, 2010, 6:04pm

All the values are higher (or appear to be), as this is live data I presume, could they have increased?

Did you empty(truncate) the table prior to updating?

AnthonySterling · June 12, 2010, 5:57pm

No worries, only too happy.

Eeep, it may take sometime for that attachment to be approved. Can you explain or host the image somewhere else?

jmutton · June 12, 2010, 6:35pm

I’m still seeing some that are different from each other.

Jase

StarLion · June 12, 2010, 8:08pm

jmutton: If you’re executing the last version of script that Anthony gave you, it will spit out an XML. Compare THAT to the database, and it should match. If it doesnt, something’s very wrong with your server’s database.

If it does, then the script is working correctly - the data on the EVE server just changes -every- -single- -second-.

AnthonySterling · June 12, 2010, 7:47pm

Unfortunately, there’s very little we can do about the data that is returned. Whether it’s valid or not, is something you’ll have to decide.

jmutton · June 12, 2010, 5:54pm

Wow,

I really do appreciate the time you are taking to help me!

Using your code I’ve noticed a problem. The XML data is not matching whats in the DB. I’ve attached a screenshot to show you what I mean. (I know they are not sorted the same.)

Thanks again,
Jase

jmutton · June 12, 2010, 5:39am

Been looking into this a lot and I think I’m starting to understand it better now.

This is what I’ve got so far…

$solar_systems = array();

$result = mysql_query('SELECT solarSystemID FROM mapSolarSystems');

while($record = mysql_fetch_assoc($result)){
  array_push($solar_systems, $record['solarSystemID']);
}

$datetime = date('Y-m-d H:00');

$sql = "INSERT INTO edb_kills (kills_date, kills_solarSystemID, kills_shipKills, kills_factionKills, kills_podKills) VALUES";
foreach($solar_systems as $solar_system){
  $sql .= sprintf("('&#37;s', '%s', 0, 0, 0),", $datetime, $solar_system);
}
mysql_query(rtrim($sql, ','));


// Define Arrays
$apidump = array();

// Kills API URL
$xml = simplexml_load_file("http://api.eveonline.com/map/Kills.xml.aspx");
// Loops XML Results
foreach ($xml->result->rowset->row as $rowdetail){
	// Solar System ID
	$solarSystemID = $rowdetail->attributes()->solarSystemID;
	// Ship Kills
	$shipKills     = $rowdetail->attributes()->shipKills;
	// Faction Kills
	$factionKills  = $rowdetail->attributes()->factionKills;
	// Pod Kills
	$podKills      = $rowdetail->attributes()->podKills;

	// Push Data Into Array
	array_push($apidump, $solarSystemID, $shipKills, $factionKills, $podKills);
}

foreach ($apidump as $key=>$value){
    $data[$key] = "('$value[0]', '$value[0]', '$value[0]', '$value[0]')";
}
$query = "INSERT INTO edb_apidump (apidump_solarSystemID, apidump_shipKills, apidump_factionKills, apidump_podKills) VALUES".implode(',', $data);
echo $query;

Its very nearly there. Its outputting the following:

INSERT INTO edb_apidump (apidump_solarSystemID, apidump_shipKills, apidump_factionKills, apidump_podKills) VALUES('30001082', '30001082', '30001082', '30001082'),('0', '0', '0', '0'),('83', '83', '83', '83'),('0', '0', '0', '0'),('30005327', '30005327', '30005327', '30005327'),('0', '0', '0', '0'),('259', '259', '259', '259'),('0', '0', '0', '0')

Should be:

INSERT INTO edb_apidump (apidump_solarSystemID, apidump_shipKills, apidump_factionKills, apidump_podKills) VALUES('30001082', '0', '83', '0') .......

This is going into a temp table where I’ll use that to update the main edb_kills with the updated data.(http://www.kavoir.com/2009/05/mysql-update-multiple-rows-with-one-single-query.html) You think thats the best way to go about it?

Any suggestions?

Thanks,
Jase

jmutton · June 12, 2010, 5:08pm

These 0’s are being plotted on a graph using google charts:

I’ve just seen another problem with my code…

Some of the array data is not matching up with the XML feed.

XML:

<row solarSystemID=“30001082” shipKills=“0” factionKills=“79” podKills=“0”/>
<row solarSystemID=“30001746” shipKills=“0” factionKills=“92” podKills=“0”/>
<row solarSystemID=“30005327” shipKills=“0” factionKills=“243” podKills=“0”/>
<row solarSystemID=“30035042” shipKills=“0” factionKills=“79” podKills=“0”/>
<row solarSystemID=“30004663” shipKills=“0” factionKills=“1” podKills=“0”/>
<row solarSystemID=“30002410” shipKills=“0” factionKills=“22” podKills=“0”/>

Array:

Array
(
[0] => Array
(
[0] => SimpleXMLElement Object
(
[0] => 30001082
)

        [1] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

        [2] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 79
            )

        [3] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

    )

[1] =&gt; Array
    (
        [0] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 30001746
            )

        [1] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

        [2] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 92
            )

        [3] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

    )

[2] =&gt; Array
    (
        [0] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 30005327
            )

        [1] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

        [2] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 251
            )

        [3] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

    )

[3] =&gt; Array
    (
        [0] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 30035042
            )

        [1] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

        [2] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 99
            )

        [3] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

    )

[4] =&gt; Array
    (
        [0] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 30004663
            )

        [1] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

        [2] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 1
            )

        [3] =&gt; SimpleXMLElement Object
            (
                [0] =&gt; 0
            )

    )

…

EDIT: It looks like every two records are not correct: correct, correct, wrong, wrong, correct, correct etc…

Its really getting on my tits in now lol

AnthonySterling · June 12, 2010, 4:59pm

I’m wondering, why do you need these zero values in your database? It’s awfully wastful, could you not just supplement a non-existing record with these values upon retrieval?

jmutton · June 11, 2010, 5:21pm

Right I’ve made some corrections to get this working. It appears to be working but when I run the page its taking a long time to complete and I’m receiving the following:

504 Gateway Time-out

$result = mysql_query("SELECT * FROM mapSolarSystems");
while($row = mysql_fetch_array($result)){
	$solarSystemID = $row['solarSystemID'];
	$TodayHour = date("Y-m-d H:00");
	mysql_query("INSERT INTO edb_kills (kills_date, kills_solarSystemID, kills_shipKills, kills_factionKills, kills_podKills) VALUES ('$TodayHour', '$solarSystemID', '0', '0', '0')");
}

// Kills API URL
$xml = simplexml_load_file("http://api.eveonline.com/map/Kills.xml.aspx");
// Loops XML Results
foreach ($xml->result->rowset->row as $rowdetail){
	// Solar System ID
	$solarSystemID = $rowdetail->attributes()->solarSystemID;
	// Ship Kills
	$shipKills     = $rowdetail->attributes()->shipKills;
	// Faction Kills
	$factionKills  = $rowdetail->attributes()->factionKills;
	// Pod Kills
	$podKills      = $rowdetail->attributes()->podKills;
	
	// Insert Data Into Database
	//$query =  "INSERT INTO edb_kills (kills_solarSystemID, kills_shipKills, kills_factionKills, kills_podKills) VALUES ('$solarSystemID', '$shipKills', '$factionKills', '$podKills')";
	//$result = @mysql_query($query);
	
	mysql_query("UPDATE edb_kills SET kills_shipKills = '$shipKills', kills_factionKills = '$factionKills', kills_podKills = '$podKills' WHERE kills_solarSystemID = '$solarSystemID' AND kills_date='$TodayHour'");
	
}

Thanks very the replies,
Jase

AnthonySterling · June 12, 2010, 7:19pm

You’re right, your server’s IP is based in the UK.

However, I get the same XML returned by your server and the API.

Topic		Replies	Views
simplexml_load_file array keys PHP	3	662	August 25, 2010
Updating DB with Daily XML Feed PHP	3	363	June 19, 2011
Batch editing XML file PHP	2	909	April 16, 2016
Xml to database - any ideas or pointers? PHP	5	425	August 21, 2011
Updating MySQL Using XML HTML & CSS xml	1	838	March 7, 2011

mySQL Comparing?

Related topics