XML and pulling data to insert into DB

[jacko_162]

FOr some reason i cant pull data from an XML file to insert into the database, it works without error just adds a blank value in the database?

 

i have this code at the moment, i guess arrayValue[71] is the value i need to pull and add to the "TABLE2" table, but when i run the script it just adds a blank value in the database?

 

<?php
include "connect.php";

//Price check Isle 9001! Query EVE-Central API. Example XML file is: http://api.eve-central.com/api/marketstat?typeid=35&regionlimit=10000002
$queryItemList = mysql_query("SELECT * FROM TABLE2;") or die(mysql_error());
$rowCount = mysql_num_rows($queryItemList);
$s=0;
$typeArray = array();
$region = "regionlimit=10000002";
while($s<$rowCount){
$typeID = mysql_result($queryItemList,$s,"typeID");
echo $typeID."<br />";

//echo "start<br />";
$reader = new XMLReader();
//echo "initialized XMLReader<br />";
$url = "http://api.eve-central.com/api/marketstat?typeid=".$typeID;
echo $url.$region."<br />";
$reader->open($url.'&'.$region);
$t=0;
$arrayName = array();
$arrayValue	 = array();
while ($reader->read()) {
	//echo $reader->name;
	$name = $reader->name;
	$arrayName[$t] = $name;
	if ($reader->hasValue) {
		//echo ": " . $reader->value."<br />";
		$value = $reader->value;
		$arrayValue[$t] = $value;
	}

	$t++;
}
echo $arrayValue[71];
$import="UPDATE TABLE2 SET marketPrice='$arrayValue[71]' WHERE typeID='$typeID';";
mysql_query($import) or die(mysql_error());
$s++;;
}
print_r($typeArray);

/*print_r($arrayName);
echo "<br />";
echo "<br />";
echo "<br />";
print_r($arrayValue);*/

?>

 

an example XML file would be:

<!--
Automatically generated data from EVE-Central.com 
-->
<!-- This is the new API :-) -->
−
<evec_api version="2.0" method="marketstat_xml">
−
<marketstat>
−
<type id="24692">
−
<all>
<volume>4.00</volume>
<avg>152500000.00</avg>
<max>152500000.00</max>
<min>152500000.00</min>
<stddev>0.00</stddev>
<median>152500000.00</median>
<percentile>0.00</percentile>
</all>
−
<buy>
<volume>0.00</volume>
<avg>0.00</avg>
<max>0.00</max>
<min>0.00</min>
<stddev>0.00</stddev>
<median>0.00</median>
<percentile>0.00</percentile>
</buy>
−
<sell>
<volume>4.00</volume>
<avg>152500000.00</avg>
<max>152500000.00</max>
<min>152500000.00</min>
<stddev>0.00</stddev>
<median>152500000.00</median>
<percentile>152500000.00</percentile>
</sell>
</type>
</marketstat>
</evec_api>

 

im looking at pulling the following value from the <sell> section:

<median>152500000.00</median>

 

i am guessing the above should be arrayValue[71] but for some reason its not picking it up.

 

any help on this would be greatly appreciated as im lost in this script.

[ansharma]

try this trick

$import="UPDATE TABLE2 SET marketPrice='$arrayValue[71]' WHERE typeID='$typeID';";
echo $import.'<br />';

 

this will give you query in your browser copy and execute it on console and execute if this query executed successfully then your code is fine else you get some error then by seeing the error you can easily debug it.

[jacko_162]

ok it gives me:

UPDATE TABLE2 SET marketPrice=' ' WHERE typeID='17619';

 

its obviously not getting the marketPrice from the URL: http://api.eve-central.com/api/marketstat?typeid=17619&regionlimit=10000002

 

not sure where the array[71] came from but i tried to echo it and got nothing?

 

[jacko_162]

still struggling to:

 

a) figure out why the insert part has marketPrice='$arrayValue[71]'

b) why nothing is being pulled from xml where array=71 and inserted into DB.

[BlueSkyIS]

a.

$import="UPDATE TABLE2 SET marketPrice='{$arrayValue[71]}' WHERE typeID='$typeID'";

 

b. are you sure there is a $arrayValue[71]? i would use var_dump($arrayValue) and/or print_r($arrayValue) to see what's in it and/or directly echo $arrayValue[71] to see if the value is set.

[jacko_162]

i actually dont think arrayValue[71] pulls anything as when i echo that i get nothing. problem was i didnt code the above coding and i dont know how it works tbh i just need to pull the averags sell price from the xml link.

[jacko_162]

well i managed to print_r the array and found out it shoul be array[79] =)

 

problem solved.. thanks for help guys as usuall. *thumbs up*

[ca99uk]

Hi Jacko_162,

 

I found this post really useful as I was looking to do a similar thing, however because of the large import I keep getting a timeout error on the execution did you manage to get a workaround to this?