Turn the result set into an XML document with PHP's DOM functions:
- <?php
- // open connection
- $mysqli = new mysqli("localhost", "user", "pass", "db1");
- if (mysqli_connect_errno()) {
- die("ERROR: Cannot connect. " . mysqli_connect_error());
- }
- // create and execute SELECT query
- $sql = "SELECT id, name, price FROM products";
- if ($result = $mysqli->query($sql)) {
- // if results exist
- // initialize DOM object
- $xml = new DOMDocument("1.0");
- // add root node
- $root = $xml->createElement("resultset");
- $xml->appendChild($root);
- // iterate over result set
- // print <record>s and <field>s
- if ($result->num_rows > 0) {
- while($row = $result->fetch_row()) {
- $record = $xml->createElement("record");
- $root->appendChild($record);
- $fieldCount = 0;
- while ($fieldCount < $mysqli->field_count) {
- $field = $xml->createElement("field");
- $record->appendChild($field);
- $field->appendChild(↩
- $xml->createTextNode($row[$fieldCount]));
- $fieldCount++;
- }
- }
- }
- $result->close();
- } else {
- die("ERROR: " . $mysqli->error . " (query was $sql)");
- }
- // close connection
- $mysqli->close();
- // display XML result set as HTML…
- $xml->formatOutput = true;
- echo "<xmp>" . $xml->saveXML() . "</xmp>";
- // …or write it to a file as XML
- $xml->save("results.xml") or die("ERROR: Could not write to file");
- ?>
Comments
There are a number of reasons why you might want to export a SQL result set to an XML file: to make the data more usable and portable, to reduce your application's dependence on a database server, or to improve performance (because it's usually faster to read from a disk file than from a network connection).
The previous listing shows you how, by executing a SELECT query and then converting the resulting data set into a series of <record> and <field> elements. PHP's DOM extension, which comes with built-in support for dynamically adding nodes to an XML document tree, takes care of the heavy lifting with its createElement(), createTextNode(), and appendChild() methods (these methods are discussed in greater detail in the listings in "10.4: Creating XML" and "10.5: Adding or Removing XML Nodes"). Once the document has been completely generated, it may be written to a file via the save() method, or returned as a string suitable for display with the saveXML() method.
You can also do the reverse: read data from an XML file and write it to a database. The next listing demonstrates this, using SimpleXML to parse an XML file and generate a series of SQL INSERT statements from it:
- <?php
- $xmlData =<<< END
- <?xml version="1.0"?>
- <data>
- <item>
- <id>20</id>
- <name>mangoes</name>
- <price>11</price>
- </item>
- <item>
- <id>22</id>
- <name>strawberries</name>
- <price>5</price>
- </item>
- <item>
- <id>23</id>
- <name>grapes</name>
- <price>25</price>
- </item>
- </data>
- END;
- // read XML data string
- $xml = simplexml_load_string($xmlData)↩
- or die("ERROR: Cannot create SimpleXML object");
- // open MySQL connection
- $connection = mysqli_connect("localhost", "user", "pass", "db1")↩
- or die ("ERROR: Cannot connect");
- // process node data
- // create and execute INSERT queries
- foreach ($xml->item as $item) {
- $id = $item->id;
- $name = mysqli_real_escape_string($connection, $item->name);
- $price = $item->price;
- $sql = "INSERT INTO products (id, name, price)↩
- VALUES ('$id', '$name', '$price')";
- mysqli_query($connection, $sql) or die ("ERROR: " .↩
- mysqli_error($connection) . " (query was $sql)");
- }
- // close connection
- mysqli_close($connection);
- ?>
In this listing, SimpleXML is used to iterate over each <item> in the XML document and generate an SQL INSERT statement from the values contained within it. PHP's ext/mysqli functions are then used to execute each INSERT statement on the database server, thus writing the data to the database.
Tip | Metabase is a PHP-based database abstraction layer that uses XML to express table relationships, structures and records. Read more about it at http://www.phpclasses.org/metabase. |