Help with hourly changing XML file and auto updating database.

[jacko_162]

so i have an external XML file that i read, loop through all the data and run a INSERT to add it to my database to work with the results on a back-end system, this work great, however the rows i added to the database need to be edited manually (only a few rows tbh!) - which i hear you say is fine.

 

Well in theory it is fine and its confused the hell out of me all day;

 

 

I have a script to pull the data in a html form, i can update or add values not found in the XML to the row in the database.

 

My next problem is the XML file updates itself every 1 hour, so i have to run my script on a CRON job which auto run every 1 hour (I already know how to do this!)

 

now sometimes rows in the XML file are removed (I need these removed from database when the script runs), or individual items are changed (changes need to reflect in database when the script runs, WITHOUT delete the whole row, because of my manual data inputs)

 

how can i achieve this using my CRON script code?

 

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

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


// URL FOR XML DATA
$url = "test.xml"; // For Testing Purposes
//$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) {


$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 UPDATE THE DATA DATABASE!
$query = "INSERT INTO `ecmt_memberlist` SET
characterID='$characterID',
name='$name',
startDateTime='$startDateTime',
baseID='$baseID',
base='$base',
title='$title',
logonDateTime='$logonDateTime',
logoffDateTime='$logoffDateTime',
locationID='$locationID',
location='$location',
shipTypeID='$shipTypeID',
shipType='$shipType',
roles='$roles',
grantableRoles='$grantableRoles'";


//echo query to error check
//echo $query;
//echo "<br><br>";


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

 

if i use "INSERT INTO or REPLACE" i get "Duplicate entry '90197511' for key 'characterID'"

 

can anyone help me to achieve what i need?

 

p.s I am not the greatest PHP guru in the world.. i am still learning basics.

 

greatly appreciate any help on the above.

Edited February 15, 2013 by jacko_162

[jacko_162]

can i run 3x separate query's with like the following:

 

1) If .XML contains characterID not already in database add the row.

 

2) If database contains a characterID NOT in the .XML remove row?

 

3) If a row in the .XML file is different to the row in the dabase UPDATE the row with new information?

 

or is this not possible?

[jacko_162]

Anyone help please?

[jacko_162]

Please help me code an if statment for the above pls?