Seasons greetings!
I'm new to PHP, MySQL and XML... and have been trying to write a class/object that extracts data from an XML file, and stores it into a database. A fun delightfully frustrating challenge to work on during the christmas holiday.
Before posting this question I've looked at the PHP5.x documentation, W3C and also searched quite a bit around stackoverflow.
My lamp stack is configured as follows:
PHP version: 5.6, PHPmyAdmin version: 4.2.0, and MySQL version: 5.6.15
The code looks as follows...
> SAMPLE XML EXCERPT
<server>
<name>Epic1</name>
</server>
<alliances>
<alliance>
<alliance id="101">Knock Out</alliance>
<foundedbyplayerid id="3" />
<alliancecapitaltownid id="10001" />
<allianceticker>K.O.</allianceticker>
<foundeddatetime>2014-12-25T14:12:01.123</foundeddatetime>
<alliancecapitallastmoved>2014-12-28T19:41:15.837</alliancecapitallastmoved>
<alliancetaxrate>0.01</alliancetaxrate>
<alliancetaxratelastchanged>2014-12-27T18:01:34.130</alliancetaxratelastchanged>
<membercount>99</membercount>
<totalpopulation>9973554</totalpopulation>
<roles>
<role>
<role id="1">Founder</role>
<heirarchy id="1" />
</role>
<role>
<role id="3">CEO</role>
<heirarchy id="2" />
</role>
<role>
<role id="352">CFO</role>
<heirarchy id="4" />
</role>
</roles>
<relationships>
<relationship>
<proposedbyalliance id="102" />
<acceptedbyalliance id="101" />
<relationshiptype id="4">NAP</relationshiptype>
<establishedsince>2014-12-27T18:01:34.130</establishedsince>
</relationship>
<relationship>
<proposedbyalliance id="101" />
<acceptedbyalliance id="103" />
<relationshiptype id="4">NAP</relationshiptype>
<establishedsince>2014-12-27T18:01:34.130</establishedsince>
</relationship>
<relationship>
<proposedbyalliance id="104" />
<acceptedbyalliance id="101" />
<relationshiptype id="4">NAP</relationshiptype>
<establishedsince>2014-12-27T18:01:34.130</establishedsince>
</relationship>
</relationships>
</alliance>
> PHP CODE:
$xml = simplexml_load_file($alliances_xml); //$alliances_xml = path to file
foreach ($xml->alliances->alliance as $alliance) {
// Alliance info (seems to work as intended)
$alliance_id = mysqli_real_escape_string($dbconnect,$alliance->alliance['id']);
$alliance_name = mysqli_real_escape_string($dbconnect,$alliance->alliance);
// Diplomacy info (yields error, see result below...)
$proposed_by_alliance_id = mysqli_real_escape_string($dbconnect,$alliance->relationships->relationship->proposedbyalliance['id']);
$accepted_by_alliance_id = mysqli_real_escape_string($dbconnect,$alliance->relationships->relationship->acceptedbyalliance['id']);
$relationship_type_id = mysqli_real_escape_string($dbconnect,$alliance->relationships->relationship->relationshiptype['id']);
$established_date = mysqli_real_escape_string($dbconnect,$alliance->relationships->relationship->establishedsince);
// MySQL insert statement
mysqli_query($dbconnect,
"INSERT INTO diplomacy (alliance_id, alliance_name, proposed_by_alliance_id, accepted_by_alliance_id, relationship_type_id, established_date)
VALUES ('$alliance_id', '$alliance_name', '$proposed_by_alliance_id', '$accepted_by_alliance_id', '$relationship_type_id', '$established_date')
ON DUPLICATE KEY UPDATE
alliance_name = VALUES(alliance_name),
proposed_by_alliance_id = VALUES(proposed_by_alliance_id),
accepted_by_alliance_id = VALUES(accepted_by_alliance_id),
relationship_type_id = VALUES(relationship_type_id),
established_date = VALUES(established_date)
");
}
> RESULT:
Notice: Trying to get property of non-object in http://localhost/project/diplomacy.php on line 21 // refers to ^ : $proposed_by_alliance_id
Notice: Trying to get property of non-object in http://localhost/project/diplomacy.php on line 22 // refers to ^ : $accepted_by_alliance_id
Notice: Trying to get property of non-object in http://localhost/project/diplomacy.php on line 23 // refers to ^ : $relationship_type_id
Notice: Trying to get property of non-object in http://localhost/project/diplomacy.php on line 24 // refers to ^ : $established_date
This repeats over and over until php is done looping through the XML file.
-------------------------------------------------------------------------------
> PROBLEM / SITUATION:
When I head over to phpmyadmin, my table looks like this...
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* aliance_id * alliance_name * proposed_by_alliance_id * accepted_by_alliance_id * relationship_type_id * established_date *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 101 * Knock Out * 102 * 101 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 102 * WeJazz * 101 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 103 * alliance 3 * 104 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 104 * alliance 4 * 102 * 101 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
When in reality it should look more like this...
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* aliance_id * alliance_name * proposed_by_alliance_id * accepted_by_alliance_id * relationship_type_id * established_date *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 101 * Knock Out * 102 * 101 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 101 * Knock Out * 101 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 102 * WeJazz * 102 * 101 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 102 * WeJazz * 104 * 102 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 103 * alliance 3 * 101 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 103 * alliance 3 * 104 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 101 * alliance 4 * 104 * 102 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 104 * alliance 4 * 104 * 103 * 4 * 2014-12-27T18:01:34.130 *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
While typing this question, I immediately noticed that the design of the table isn't exactly optimal... but I've been at it for hours, and can't think of a more efficient layout for the moment.
So to sum everything up, my questions would be...
- What is my code missing?
- Any suggestions for improvements?
- Can you recommend a better table layout?
- Perhaps also a good book on PHP, OOP and MySQL? (I'm a graphic designer)
Another thing that comes to mind is server load. Is there anything I should add or change in the code, in order to avoid overloading the server once I start parsing the real 30Mb XML every couple of hours?
This parser will eventually become part of a larger web app, running on a VPS alongside 10 to 20 wordpress sites that receive negligible traffic. (lol)
I realize that the scope of this question may be slightly broader than what's usually accepted on stackoverflow, so I do apologize if anyone is bothered by this. I also realize that as a newcomer to PHP and OOP, I should be working on more basic things; but it's been so delightfully frustrating that I just can't help myself! :D
Thank you in advance. I'm eternally grateful for your time, help and wisdom; and promise to pay it forward once I get the hang of PHP, OOP and MySQL.
No comments:
Post a Comment