How to Output MySQL Data as XML Using PHP?

Query MySQL Database and Output XML via PHP

Problem:
Extract an XML output of specific columns ('udid' and 'country') from a MySQL database table using PHP.

Solution:

To achieve this, follow these steps:

1. Establish Database Connection: Connect to the MySQL database using PHP's mysql_connect() and mysql_select_db() functions.
2. Execute MySQL Query: Use the mysql_query() function to execute a SELECT query that retrieves the desired columns from the database table.
3. Initialize XMLWriter: Create an XMLWriter object to generate the XML output.
4. Start XML Document: Begin the XML document by calling the startDocument() method of the XMLWriter object.
5. Start Root Element: Create the root element of the XML document (e.g., 'countries').
6. Loop Through MySQL Results: Iterate over the results of the MySQL query and add each row as a child element within the root element.
7. Add Attributes and Elements: For each row, set attributes and write the data for the corresponding columns.
8. End XML Document: Close the root element and XML document using the endElement() and endDocument() methods of the XMLWriter object.

PHP Code:

mysql_connect('server', 'user', 'pass');
mysql_select_db('database');

$sql = "SELECT udid, country FROM table ORDER BY udid";
$res = mysql_query($sql);

$xml = new XMLWriter();

$xml->openURI("php://output");
$xml->startDocument();
$xml->setIndent(true);

$xml->startElement('countries');

while ($row = mysql_fetch_assoc($res)) {
  $xml->startElement("country");

  $xml->writeAttribute('udid', $row['udid']);
  $xml->writeRaw($row['country']);

  $xml->endElement();
}

$xml->endElement();

header('Content-type: text/xml');
$xml->flush();

Output:

<?xml version="1.0"?>
<countries>
 <country udid="1">Country 1</country>
 <country udid="2">Country 2</country>
 ...
 <country udid="n">Country n</country>
</countries>

The above is the detailed content of How to Output MySQL Data as XML Using PHP?. For more information, please follow other related articles on the PHP Chinese website!