PHP script to update database with new values or remove.

[jacko_162]

ok so past few days i coded a small script to pull some data from an xml file and add to a database.

 

this has all gone well and i managed to populate all the information using the script.

 

my next problem is this;

the data in the xml file changes periodically (every 6 hours or so) so i will run a CRON to repeatedly run the file every 6 hours.

 

some of the data in the XML will stay static (for instance a characterID, or name) sometimes the whole row will be removed and sometimes some of the data will be changed.

 

here is an example of a few rows in the XML;

<eveapi version="2">
<currentTime>2013-02-13 23:39:21</currentTime>
<result>
<rowset name="members" key="characterID" columns="characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles">
<row characterID="90158470" name="SOCK ZERO" startDateTime="2012-03-16 23:41:00" baseID="0" base="" title="" logonDateTime="2013-02-13 22:54:45" logoffDateTime="2013-02-13 22:56:30" locationID="60014911" location="1V-LI2 III - Moon 2 - MbI BepHyJIucb" shipTypeID="-1" shipType="" roles="0" grantableRoles="0"/>
<row characterID="90197511" name="good kharma" startDateTime="2011-10-05 00:21:00" baseID="0" base="" title="" logonDateTime="2013-02-11 13:48:41" logoffDateTime="2013-02-11 13:52:35" locationID="30000683" location="LBC-AW" shipTypeID="29248" shipType="Magnate" roles="0" grantableRoles="0"/>
</rowset>
</result>
<cachedUntil>2013-02-14 04:53:39</cachedUntil>
</eveapi>

 

and here is my code to pull the XML and insert into database;

<?php


// INCLUDE DB CONNECTION FILE
include("includes/connect.php");

// CHANGE THE VALUES HERE
include("includes/config.php");


// URL FOR XML DATA
$url = "https://api.eveonline.com/corp/MemberTracking.xml.aspx?keyID=".$keyID."&vCode=".$vCode."&extended=1";


// RUN XML DATA READY FOR INSERT
$xml = simplexml_load_file($url);


// Loop Through Names
foreach ($xml->result->rowset[0] as $value) {
echo "characterID:".$value['characterID']." name: ".$value['name']." location: ".$value['location']."<br />";


$characterID = mysql_real_escape_string($value['characterID']);
$name = mysql_real_escape_string($value['name']);
$startDateTime = mysql_real_escape_string($value['startDateTime']);
$baseID = mysql_real_escape_string($value['baseID']);
$base = mysql_real_escape_string($value['base']);
$title = mysql_real_escape_string($value['title']);
$logonDateTime = mysql_real_escape_string($value['logonDateTime']);
$logoffDateTime = mysql_real_escape_string($value['logoffDateTime']);
$locationID = mysql_real_escape_string($value['locationID']);
$location = mysql_real_escape_string($value['location']);
$shipTypeID = mysql_real_escape_string($value['shipTypeID']);
$shipType = mysql_real_escape_string($value['shipType']);
$roles = mysql_real_escape_string($value['roles']);
$grantableRoles = mysql_real_escape_string($value['grantableRoles']);



// NOW LETS INSERT INTO DATABASE!!
$query = "INSERT INTO membertracking (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles) VALUES ('$characterID','$name','$startDateTime',$baseID,'$base','$title','$logonDateTime','$logoffDateTime','$locationID','$location','$shipTypeID','$shipType','$roles','$grantableRoles')";


echo "$query<br /><br />";


mysql_query($query) or die(mysql_error());
};
?>

 

now i need to figure out how to adapt the code to check if there are changes, and if so update them WITHOUT removing the row first (i add secondary data in the database after the pull) and re adding a new row. also removing rows if the characterID no longer exists in the XML file.

 

how can i best achieve this?