These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

alliance xml to Mysql
Author
Previous Topic Next Topic
Louis Vitton
Viziam
Amarr Empire
#1 - 2011-12-05 06:07:03 UTC
Hey guys,

i am building a basic application and want to put alliance name and allanceID's into a database to refer to.
The code below is how i pased the xml and got it to display the objects i want but the several methods i used to put these details into the database failed each time.

I was wondering if anyone could give me a hand.

Thanks

Quote:
$data = file_get_contents('http://api.eveonline.com/eve/AllianceList.xml.aspx');
$xml = new SimpleXMLElement($data);
#var_dump($xml);

foreach ($xml->result->rowset->row as $row)
{
echo (string)$row{'name'};
echo (Int)$row{'allianceID'};
}
Irijah Onzo
Caldari Provisions
Caldari State
#2 - 2011-12-05 15:16:53 UTC
Everything looks fine to me. When I run it locally, I get (as far as I can tell) all the names and ID's.

What's happening when you run it? Any errors?
TorTorden
Tors shibari party
#3 - 2011-12-05 17:51:40 UTC
Just off the top of my head, might want to go with a (double) instead of an (int) for allianceID to be on the safe side.

To provide any usefull input we need more details on what goes wrong.
Cyerus
University of Caille
Gallente Federation
#4 - 2011-12-05 17:55:32 UTC
Basic PHP:

Quote:
echo $row{'name'};
echo $row{'allianceID'};


should be;

Quote:
echo $row['name'];
echo $row['allianceID'];


Notice the brackets.
Irijah Onzo
Caldari Provisions
Caldari State
#5 - 2011-12-05 22:52:19 UTC
Cyerus wrote:
Basic PHP:

Quote:
echo $row{'name'};
echo $row{'allianceID'};


should be;

Quote:
echo $row['name'];
echo $row['allianceID'];


Notice the brackets.

Hmmm... I wonder why it worked for me then... You're 100% correct in the bracket correction, but now I'm really interested to see why the original code worked on my machine.
Louis Vitton
Viziam
Amarr Empire
#6 - 2011-12-05 23:21:28 UTC
The main issue is i then want to put all this in to a Database but when i have tried i get a repeat of the 1 entry or i get an error saying its taking to long to load.

Should i put the slq insert part in the for each section or make another loop later as $row['name']; $row['allianceID']; are already in an array. I think.
Louis Vitton
Viziam
Amarr Empire
#7 - 2011-12-06 03:29:58 UTC  |  Edited by: Louis Vitton
This is the error i am getting cause mysql times out after 30 seconds
Quote:
Fatal error: Maximum execution time of 30 seconds exceeded in G:\xampp\htdocs\post\a.php on line 23


here is my code with the MySql tried both in and outside the for each.

now the results are been placed in the database just it times out once i got 545 results in and the other time 450


Quote:
$data = file_get_contents('http://api.eveonline.com/eve/AllianceList.xml.aspx');
$xml = new SimpleXMLElement($data);
#var_dump($xml);

foreach ($xml->result->rowset->row as $row)
{
$con = mysql_connect("********","*********","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("timers", $con);

$row1 = (string)$row['name'];
$row2 = (Int)$row['allianceID'];

mysql_query("INSERT INTO alliance (allianceName, allianceID) VALUES('$row1', '$row2')");
mysql_close($con);
}
Irijah Onzo
Caldari Provisions
Caldari State
#8 - 2011-12-06 03:40:20 UTC
$row['name'] and $row['allianceID'] are going to be overwritten each pass through the loop, so you'll need to either run the SQL inside of the loop (creating several thousand INSERT statements) or store them for use later. You could do something like this:

Quote:
$data = file_get_contents('http://api.eveonline.com/eve/AllianceList.xml.aspx');
$xml = new SimpleXMLElement($data);
#var_dump($xml);

foreach ($xml->result->rowset->row as $row)
{
$alliances[$row['allianceID']]=$row['name'];
}

This should give you an array of all the alliances, which you could then loop through, building a larger SQL INSERT, which you could run every 15 or 20 or 50 passes.

Quote:

$i=1;
foreach ($alliances as $k => $v) {
if ($i >50) {
mysql_query ($sql); //May want to do something with the result
$sql='"INSERT INTO tablename VALUES ";
}
$sql.= "($k,$v),";
$i++;
}

It's late, so check the logic, but I think you get the general idea.

This will just insert multiple alliances into the DB at the same time, reducing the INSERTs and likely making your host/server happier.
Irijah Onzo
Caldari Provisions
Caldari State
#9 - 2011-12-06 03:42:39 UTC
Louis Vitton wrote:
This is the error i am getting cause mysql times out after 30 seconds
Quote:
Fatal error: Maximum execution time of 30 seconds exceeded in G:\xampp\htdocs\post\a.php on line 23


here is my code with the MySql tried both in and outside the for each.

now the results are been placed in the database just it times out once i got 545 results in and the other time 450

There's the key - you have to figure out how to get the execution of the script under 30 seconds. Most host will terminate the script execution in 30 sec by default. I'm interested to see if the 'batch importing' in my previous post will work better for you, or if there's something else we need to consider.
Dragonaire
Here there be Dragons
#10 - 2011-12-06 05:28:21 UTC
First thing you need to do is stop trying to create a new database connection on each time through the loop and than closing it Shocked

foreach ($xml->result->rowset->row as $row)
{
$con = mysql_connect("********","*********","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

....

mysql_query("INSERT INTO alliance (allianceName, allianceID) VALUES('$row1', '$row2')");
mysql_close($con);
}

should be

$con = mysql_connect("********","*********","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
foreach ($xml->result->rowset->row as $row)
{

...

mysql_query("INSERT INTO alliance (allianceName, allianceID) VALUES('$row1', '$row2')");
}
mysql_close($con);

The way you are doing it now you're wasting a lot of time just opening and closing the connections.

You should open the database connection after getting your data from the API then insert all of your data. What you are asking it to do now is make a few thousand connects and insert a single row then disconnect. I think you'll find that correct will solve you timeout errors.

I'd also suggest you look at using one of the many existing PHP API libraries that others have developed since that will save you a lot of work in the long run.

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.
Tonto Auri
Vhero' Multipurpose Corp
#11 - 2011-12-06 08:16:55 UTC
You could win another 8-10% by using PDO_mysql and prepared statements.

Two most common elements in the universe are hydrogen and stupidity. -- Harlan Ellison
Cyerus
University of Caille
Gallente Federation
#12 - 2011-12-06 13:10:59 UTC
Also, you can try to increase the execution time of the PHP script either by editing php.ini (best method) or by including this line near the top of your PHP script (sometimes works).

Quote:
@ini_set('max_execution_time', 300); //300 seconds = 5 minutes
Max Kolonko
Caldari Provisions
Caldari State
#13 - 2011-12-12 00:16:57 UTC
Dragonaire wrote:
First thing you need to do is stop trying to create a new database connection on each time through the loop and than closing it Shocked

foreach ($xml->result->rowset->row as $row)
{
$con = mysql_connect("********","*********","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

....

mysql_query("INSERT INTO alliance (allianceName, allianceID) VALUES('$row1', '$row2')");
mysql_close($con);
}

should be

$con = mysql_connect("********","*********","**********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
foreach ($xml->result->rowset->row as $row)
{

...

mysql_query("INSERT INTO alliance (allianceName, allianceID) VALUES('$row1', '$row2')");
}
mysql_close($con);

The way you are doing it now you're wasting a lot of time just opening and closing the connections.

You should open the database connection after getting your data from the API then insert all of your data. What you are asking it to do now is make a few thousand connects and insert a single row then disconnect. I think you'll find that correct will solve you timeout errors.

I'd also suggest you look at using one of the many existing PHP API libraries that others have developed since that will save you a lot of work in the long run.


This.

First thing I noticed in your code. Making connection takes longer than actual insert of one row.

I have a script that adds 5000+ rows (one INSERT at a time) from Api XML (Corp Assets) to MySQL DB in way under 30 seconds, so its all in the connect/disconect routine that You get timeout

Read and support: Don't mess with OUR WH's What is Your stance on WH stuff?
TorTorden
Tors shibari party
#14 - 2011-12-12 01:21:05 UTC  |  Edited by: TorTorden
Cyerus wrote:
Also, you can try to increase the execution time of the PHP script either by editing php.ini (best method) or by including this line near the top of your PHP script (sometimes works).

Quote:
@ini_set('max_execution_time', 300); //300 seconds = 5 minutes


Had to this myself on a few scripts but please only use thisafter you do as dragonaire says about optimizing your db connects/disconnects as well as preparing your inserts, many lower cost hosting providers limit the amounts of db inserts you can make in an hour and extended inserts can be your friend.

But that was mostly for rare scripts used to grab and cache a few 100 apikeyinfo and waiting for the api server timed me out.