loop and insert not working

[jacko_162]

i am reading from a .xml file with the following contents:

 

<?xml version='1.0' encoding='UTF-8'?>
<eveapi version="2">
 <currentTime>2013-02-15 00:38:26</currentTime>
 <result>
   <rowset name="members" key="characterID" columns="characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles">
     <row characterID="90197511" name="good kharma" startDateTime="2011-10-05 00:21:00" baseID="0" base="" title="" logonDateTime="2013-02-14 21:49:02" logoffDateTime="2013-02-14 21:54:43" locationID="30000683" location="LBC-AW" shipTypeID="29248" shipType="Magnate" roles="0" grantableRoles="0" />
     <row characterID="90210962" name="Dylan" startDateTime="2011-12-09 13:31:00" baseID="0" base="" title="" logonDateTime="2011-12-18 12:01:34" logoffDateTime="2011-12-18 12:05:15" locationID="60013189" location="Iivinen X - Moon 10 - Genolution Biohazard Containment Facility" shipTypeID="602" shipType="Kestrel" roles="0" grantableRoles="0" />
   </rowset>
 </result>
 <cachedUntil>2013-02-15 04:32:12</cachedUntil>
</eveapi>

 

and using the following PHP file;

 

<?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


// 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 INSERT AND UPDATE THE DATABASE!!
$result = mysql_query("UPDATE `ecmt_memberlist` SET
 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'
 WHERE characterID='characterID'");

$message= 'UPDATE successfull!'; 


if (mysql_affected_rows() == 0) {
$result = mysql_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'");

$message= 'INSERT successfull!';   
}


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


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

 

im trying to pull data from XML and INSERT, unless its already in the database then just UPDATE.

 

when i run the file i get the following output;

 

 

INSERT successfull!

Query was empty

 

upon looking in the database table i only get the first row added to the database. can anyone see what is wrong?

[Jessica]

1. You need to check for SQL errors.

2. Your INSERT syntax is wrong. 

3. You're trying to use the RESULT of a query as another query at the end. 

[jacko_162]

should i recode it to use an if statment as well?

 

im trying to tell it to INSERT into db when it doesnt exist, but if it does exist then UPDATE with the new information as the XML file changes every 6 hours with new info.

[jacko_162]

aaaah i just found the useful "ON DUPLICATE KEY UPDATE"

 

so now i have it INSERTING, and updating if the info changes.

 

how can i also add into the loop;

 

if character ID is NOT in the XML remove the row from database?

[Psycho]

Never, ever run queries in loops.

 

Here is what you should be doing:

1. Add a new field to the table called "last_modified"

2. Iterate over the records in the XML file and create the INSERT values. Before the loop create a timestamp for the last modified date that will be included in the last_modified field

3. Run ONE query to INSERT all the records using the "ON DUPLICATE KEY UPDATE"

4. After you INSERT/UPDATE all the records, run ONE query to DELETE all the records that have a last_modified < (less than) the timestamp created before the loop.

 

A few other comments. Why are you using mysql_real_escape_string() on fields such as 'startDateTime', 'baseID', etc.? You shoudl verify/format those values as appropriate for the data type. mysql_real_escape_string() is for STRING data. I didn't make any changes for this in the same code below because I don't know the data types for all of them. I see in a couple instances you appear to be inserting an ID and a value for a couple things (Base, ship, location). You should insert only the ID and then save the id/name to another table.

 

This is just an example - I don't have your database or input data.

<?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

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

// Loop Through Names
$insertValues = array();
$modifiedTS = date('Y-m-d h:i:s');
foreach ($xml->result->rowset[0] as $value)
{
   //Prepare the values
   $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_stfdsg($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']);

   //Create INSERT value statement
   $insertvalues[] = "('$characterID', '$name', '$startDateTime', '$baseID',
    '$base', '$title', '$logonDateTime', '$logoffDateTime', '$locationID',
    '$location', '$shipTypeID', '$shipType', '$roles', '$grantableRoles',
    '$modifiedTS')";
};

//Create and run ONE INSERT statement (with UPDATE clause)
$query "INSERT INTO `ecmt_memberlist`
	    (characterID name, startDateTime, baseID, base, title, logonDateTime,
		 logoffDateTime, locationID, location, shipTypeID, shipType, roles,
		 grantableRoles, last_modified)
    VALUES " . implode(', ', $insertvalues) . "
    ON DUPLICATE KEY UPDATE
	    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'
	    last_modified = '$modifiedTS'";
mysql_query($result) or die(mysql_error());

//Create and run ONE query to delete records that were not just inserted/updated
$query = "DELETE FROM `ecmt_memberlist` WHERE last_modified < '$modifiedTS'";
mysql_query($result) or die(mysql_error());

?>

[jacko_162]

removed all the real_escape strings.

 

i got error on line 47

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /htdocs/dev/test2.php on line 47

 

line 47:

 

VALUES " . implode(', ', $insertvalues) . "

[jacko_162]

fixed it

 

next problem;

i want to add 2x new fields to the same table called "remarks" and "afk" and manually update it via a form, but this script seems to remove the field data when it runs after i manually input the data into remarks & afk.

 

can i get it to ignore the field "remarks" and "afk"

 

many thanks so far guys, its running flawlessly at the moment apart from the above.

Edited February 19, 2013 by jacko_162

[Psycho]

removed all the real_escape strings.

I never said remove ALL of them. I only stated you should use the correct process based upon the data type. For fields that are text you should use mysql_real_escape string(), For integer fields you should use intval() or something similar. For date fields you should do something to verify the string is an acceptable date, etc.

 

next problem;

i want to add 2x new fields to the same table called "remarks" and "afk" and manually update it via a form, but this script seems to remove the field data when it runs after i manually input the data into remarks & afk.

 

can i get it to ignore the field "remarks" and "afk"

 

I'm really not following you. You want to take the data from the XML field and insert/update your current records, correct? Then you want to add data for two other fields ("remarks" and "afk")? Do you want to do that as part of the INSERT/UPDATE process or after?

 

There were no such fields in your script above, so that script would do nothing to overwrite any existing data in those fields unless you modified the query to do so.

[jacko_162]

that's what i thought, the script is pretty much 100% as you posted and it mentions NOTHING of the "afk" or "remarks" fields. Yet it removes all the data from them?

 

<?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
$insertValues = array();
$modifiedTS = date('Y-m-d h:i:s');
foreach ($xml->result->rowset[0] as $value)
{
       //Prepare the values
       $characterID = $value['characterID'];
       $name = mysql_real_escape_string($value['name']);
       $startDateTime = $value['startDateTime'];
       $baseID = $value['baseID'];
       $base = mysql_real_escape_string($value['base']);
       $title = mysql_real_escape_string($value['title']);
       $logonDateTime = $value['logonDateTime'];
       $logoffDateTime = $value['logoffDateTime'];
       $locationID = $value['locationID'];
       $location = mysql_real_escape_string($value['location']);
       $shipTypeID = $value['shipTypeID'];
       $shipType = $value['shipType'];
       $roles = $value['roles'];
       $grantableRoles = $value['grantableRoles'];



//Create and run ONE INSERT statement (with UPDATE clause)
   $insert = "INSERT INTO `ecmt_memberlist` (characterID,name,startDateTime,baseID,base,title,logonDateTime,logoffDateTime,locationID,location,shipTypeID,shipType,roles,grantableRoles, last_modified) VALUES('$characterID','$name','$startDateTime','$baseID','$base','$title','$logonDateTime','$logoffDateTime','$locationID','$location','$shipTypeID','$shipType','$roles','$grantableRoles','$modifiedTS') ON DUPLICATE KEY UPDATE 
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',
last_modified = '$modifiedTS'";

mysql_query($insert) or die(mysql_error());
//Create and run ONE query to delete records that were not just inserted/updated
$delete = "DELETE FROM `ecmt_memberlist` WHERE last_modified < '$modifiedTS'";
mysql_query($delete) or die(mysql_error());


};
?>

Edited February 20, 2013 by jacko_162

[jacko_162]

also i just noticed my xml file contains 213 characterID's but the script above only pulls 161 without any errors.. no idea to why..

[jacko_162]

ok its not adding characters with the same

$startDateTime

how can i get around this?? the only primary key should "characterID" but i guess by default sql adds datetime as unique?

[Jessica]

No, no it doesn't.

[DavidAM]

ON DUPLICATE KEY ... will trigger if ANY UNIQUE INDEX value already exists. If you have a UNIQUE INDEX on StartDateTime, then it will trigger the UPDATE. Check your indexes. PRIMARY KEYs are always UNIQUE, however the other indexes should not be UNIQUE (unless they are, uh ... unique).

[exeTrix]

No MySQL will not define unique indexes without you telling it to do so via ALTER TABLE or CREATE TABLE statements.

 

Also, you're using a depreciated method of connecting to your database. Have you considered using PDO? It's more secure and fun! No need to use mysql_real_escape_string and it'll build on you OO knowledge

 

Sorry if I've missed anything:

 

try{
//get our database handle
$db = new PDO( 'mysql:dbname=testdb;host=127.0.0.1', 'Username', 'Password' );
//prepare our statament ready for values to be bound
$stmt = $db->prepare("
   INSERT INTO `ecmt_memberlist` (
    characterID,
    name,
    startDateTime,
    baseID,
    base,
    title,
    logonDateTime,
    logoffDateTime,
    locationID,
    location,
    shipTypeID,
    shipType,
    roles,
    grantableRoles,
    last_modified
   ) VALUES ( 
    :characterID,
    :name,
    :startDateTime,
    :baseID,
    :base,
    :title,
    :logonDateTime,
    :logoffDateTime,
    :locationID,
    :location,
    :shipTypeID,
    :shipType,
    :roles,
    :grantableRoles,
    :modifiedTS
   ) 
   ON DUPLICATE KEY UPDATE 
    name = VALUE(name),
    startDateTime = VALUE(startDateTime),
    baseID = VALUE(baseID),
    base = VALUE($base),
    title = VALUE(title),
    logonDateTime = VALUE(logonDateTime),
    logoffDateTime = VALUE(logoffDateTime),
    locationID = VALUE(locationID),
    location = VALUE(location),
    shipTypeID = VALUE(shipTypeID),
    shipType = VALUE(shipType),
    roles = VALUE(roles),
    grantableRoles = VALUE(grantableRoles),
    last_modified = VALUE(modifiedTS)
  ");
//create our object for binding
$ef = new stdClass();
$ef->characterID;
$ef->name;
$ef->baseID;
$ef->base;
$ef->title;
$ef->logonDateTime;
$ef->logoffDateTime;
$ef->locationID;
$ef->location;
$ef->locationID;
$ef->shipTypeID;
$ef->shipType;
$ef->roles;
$ef->grantableRoles;
//bind the params to the object properties
//the binding is done by reference so when the value of the property changes it will be retrieved when execute is called
$stmt->bindParam( ':characterID', $ef->characterID, PDO::PARAM_INT );
$stmt->bindParam( ':name', $ef->name, PDO::PARAM_STR );
$stmt->bindParam( ':baseID', $ef->baseID, PDO::PARAM_INT );
$stmt->bindParam( ':base', $ef->base, PDO::PARAM_STR );
$stmt->bindParam( ':title', $ef->title, PDO::PARAM_STR );
$stmt->bindParam( ':logonDateTime', $ef->logonDateTime, PDO::PARAM_STR );
$stmt->bindParam( ':logoffDateTime', $ef->logoffDateTime, PDO::PARAM_STR );
$stmt->bindParam( ':locationID', $ef->locationID, PDO::PARAM_INT );
$stmt->bindParam( ':location', $ef->location, PDO::PARAM_STR );
$stmt->bindParam( ':locationID', $ef->locationID, PDO::PARAM_INT );
$stmt->bindParam( ':shipTypeID', $ef->shipTypeID, PDO::PARAM_INT );
$stmt->bindParam( ':shipType', $ef->shipType, PDO::PARAM_INT );
$stmt->bindParam( ':roles', $ef->roles, PDO::PARAM_INT );
$stmt->bindParam( ':grantableRoles', $ef->grantableRoles, PDO::PARAM_INT );

//loop through our result set obtained via the API, I've left out the connection stuff
foreach ($xml->result->rowset[0] as $value){
 //reassign the new set of variables
 $ef = (object) $value;
 //execute the query
 $stmt->execute();
}

}catch( PDOException $e ){
echo $e->getMessage();
}

 

It's also worth mentioning that the above is trusting the api to return the correct fields. If you received more then clone the $ef object and reference it with property_exists.

[jacko_162]

something must of gone wrong with the table setup in database, delete and re did the whole table seems of fixed the pull issue.

 

i still have issue with the above code removing anything in the "afk" and "remarks" fields tho.. very strange....!

[DavidAM]

@jacko_162

The INSERT statement will not touch any columns that are not specified in the statement. The columns should receive the value assigned as the DEFAULT when the table was created. The UPDATE (triggered by the duplicate key) will only update the columns that you specify in that phrase of the query. Since you dropped and re-created the table, and these columns are not supplied by this code, could it be the form you are using for these fields that is causing the "lost data"? Do you have a trigger on the table that could be manipulating this data?

 

@exeTrix

No MySQL will not define unique indexes without you telling it to do so via ALTER TABLE or CREATE TABLE statements.

I never said mySQL would arbitrarily define unique indexes. I simply said that the ON DUPLICATE KEY UPDATE phrase will be triggered by ANY UNIQUE INDEX on the table, NOT JUST the PRIMARY KEY. If you define a unique index on farkle_counts, it WILL trigger the ON DUPLICATE KEY phrase.

 

Also, you're using a depreciated method of connecting to your database. Have you considered using PDO? It's more secure and fun! No need to use mysql_real_escape_string and it'll build on you OO knowledge

There is NOTHING inherently "more secure" about PDO. It is simply "a lightweight, consistent interface for accessing databases" (PDO). It is the Prepared Statement that "eliminates" the need for escaping strings. And only because it is doing it itself, inside the "black box". If you use PDO.query or PDO.execute then "Data inside the query should be properly escaped.".

[Jessica]

Psycho I think eXeTrix was responding to the OP saying "i guess by default sql adds datetime as unique?".

[jacko_162]

well i figured out the DELETE query was being run inside the foreach() loop, after dropping it out of the loop the fields no longer loose data.

 

problem solved

[exeTrix]

@jacko_162 Well spotted! It's always good when you get to the bottom of an issue. Well done mate.

 

@DavidAM As Jessica pointed out I was responding to the original response, I was just slow on the uptake. You are right in the majority of what you've said. However, I'd just like to point out that you've inadvertently given some incorrect advice, which could lead people new to PHP into a false sense of security (no pun intended ).

 

PDO::query() will NOT escape data, running a statement in this way would be equivalent to mysql_query(). Therefore, you will need to escape data when using this method.

 

Taking all arguments aside valid or not, PDO is the recommended method for database connection by PHP, so surely we should give people with less experience than us advice to change? Anyway, as long as all queries are prepared and executed then I'd be inclined to disagree that PDO will not provide a more robust protection because it eliminates human error. For example, above jacko han't escaped variables that he believes will yield an integer value from the API, I realise that maybe you can trust the response but I don't trust any values unless they're created by me. So using mysql_query() security hole PDO::prepare()->execute() hole plugged.

[exeTrix]

Also exceptions built in That's a big advantage