我需要帮助php解析xml以插入mysql数据库

Question

Aloha everyone,

I apologize in advance for the many questions, but I've been asked to develop a database and have no experience with PHP and MySQL. I thought it would be a good exercise for me to attempt to learn a little bit about them and try to develop a concept database for my work at the same time. Basically this is a database that uses SYDI to obtain WMI information from our Windows-based computers to use for patch management. The way I envision this working is like this:

1. SYDI is run and an XML file is generated with the information.
2. Using the PHP front end to our patch database, the XML report is parsed and the desired information is then inserted into the MySQL database.
3. Reports are generated from the database to compare with the latest known baseline for the activity. If computers are found to be below the baseline, the patch server is used to deliver the needed patches to the delinquent computer(s).

There are a couple of formats used in the XML report from SYDI, one with attributes in a single tag, and another where a single parent tag contains several child tags with attributes. I have figured out how to parse the first. Here's a sample of the data and the code for that (it's really pretty basic stuff) with the resulting ouput:

<machineinfo manufacturer="Dell Inc." productname="Precision M90" identifyingnumber="87ZGFD1" chassis="Portable" /> 

$xml = simplexml_load_file("sydiTest.xml");

foreach($xml->machineinfo[0]->attributes() as $a => $b)
    {
        echo $b, "</br>";
    }

Dell Inc.
Precision M90
87ZGFD1
Portable

I didn't need the name of the attribute, only the value, so I only echo'd $b there. For the second, here's a sample of the data itself as well as the code and output for the parse:

 <patches>
  <patch description="Microsoft .NET Framework 1.1 Security Update (KB2416447)" hotfixid="M2416447" installdate="04-Feb-11" />
  <patch description="Microsoft .NET Framework 1.1 Service Pack 1 (KB867460)" hotfixid="S867460" installdate="04-Feb-11" />
  <patch description="Windows Management Framework Core" hotfixid="KB968930" installdate="2/4/2011" />
  <patch description="Security update for MSXML4 SP2 (KB954430)" hotfixid="Q954430" installdate="04-Feb-11" />
  <patch description="Security update for MSXML4 SP2 (KB973688)" hotfixid="Q973688" installdate="04-Feb-11" />
  <patch description="Microsoft Internationalized Domain Names Mitigation APIs" hotfixid="IDNMitigationAPIs" installdate="6/30/2008" />
 </patches>

foreach ($xml->patches->patch[0]->attributes() as $a => $b) 
    {
        echo $b, "</br>";
    }

Microsoft .NET Framework 1.1 Security Update (KB2416447)
M2416447
04-Feb-11

As you can see, I only got the first patch, not the rest of them. I figure that 'patch[0]' is most likely the issue, as it only references the first child tag. How can I get it to reference the rest of the children?

The results raise another issue. Is there any way to pick out specific attributes and disregard the rest? For example, in the first parse, the machineinfo parse gets all the information I need. In the second parse, I only need the description and hotfixid. Once I get the correct syntax for the parse, assuming it runs like the first one, I would most likely get all of the attributes. I don't need the install date.

Lastly, how can I assign the retrieved values to variables? The first parse results in the data I need, but not in the correct order. My table structure is like this:

CREATE TABLE InventoryItems
    (InvSerNum          VARCHAR(20) NOT NULL,
    Make                VARCHAR(20),
    Model               VARCHAR(20),
    Platform            VARCHAR(12),
CONSTRAINT Inventory_PK PRIMARY KEY (InvSerNum));

I need the identifyingnumber (InvSerNum) first. Of course, I could always reorder the fields in the table to match the XML, but I'd rather leave it as is. My thinking is that I can use an INSERT statement and just use the variables for the values to be input.

I'm trying to do all of this on my own, but got stuck on the XML parsing part. If anyone can assist me in understanding the process, I would be in your debt.

Answer 1

Try using RapidXML in PHP. Makes XML parsing a bit easier. It's still not that intuitive: you'll need a good debugger to get to the bottom of it. The rest of your questions require you to do a bit of research into the mysql_(function_name) bindings in PHP. There's heaps of articles out there about this.