Home >Database >Mysql Tutorial >How to Output MySQL Data as XML Using PHP?

How to Output MySQL Data as XML Using PHP?

Susan Sarandon
Susan SarandonOriginal
2024-11-10 00:13:02231browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn