通过PHP获取MySQL数据库输出到XML

I have a MySQL database on my website, and I would like to know how I could get an XML output via PHP of the following columns in the table:

我的网站上有一个MySQL数据库，我想知道如何通过表格中的以下列的PHP获取XML输出：

1. udid
2. 你做了
3. country
4. 国家

5 个解决方案

#1

An example with XMLWriter.

XMLWriter的一个例子。

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>

#2

<?php

mysql_connect('myserver', 'username', 'password');
mysql_select_db('mydatabase');
$result = mysql_query('SELECT `udid`, `country` FROM `MyTable`');

while($data = mysql_fetch_assoc($result)) {
  foreach($data as $key => $value) {
    echo "<$key>$value</$key>";
  }
}

?>

This code snippet should give you a good start. But without the wanted XML structure, it's hard to do better.

此代码段应该为您提供良好的开端。但是如果没有想要的XML结构，就很难做得更好。

However I'm not sure PHP is the right solution for this task. Many tools, like phpmyadmin for example, can output mysql data in XML format.

但是我不确定PHP是否适合这项任务。许多工具，例如phpmyadmin，可以输出XML格式的mysql数据。

#3

<?php

mysql_connect('myserver', 'username', 'password');
mysql_select_db('mydatabase');
$result = mysql_query('SELECT `udid`, `country` FROM `MyTable`');

$Result = "<?xml version='1.0' encoding='utf-8'?>\n<employees>\n";

while($data = mysql_fetch_assoc($Recordset1)) {
  $Result .= " <employee>\n";
  foreach($data as $key => $value) {
    $Result .=  "  <$key>$value</$key>\n";
  }
    $Result .= " </employee>\n";
}
$Result .= "</employees>\n";
echo $Result;

?>

#4

I struggle a lot to find out this solution in mysqli format but nowhere i found the solution. Below is the solution i figured. Hope it will help some one.

我很难找到mysqli格式的解决方案，但我找不到解决方案。以下是我想到的解决方案。希望它会帮助一些人。

<?php
//Create file name to save
$filename = "export_xml_".date("Y-m-d_H-i",time()).".xml";

$mysql = new Mysqli('server', 'user', 'pass', 'database');
if ($mysql->connect_errno) {
    throw new Exception(sprintf("Mysqli: (%d): %s", $mysql->connect_errno, $mysql->connect_error));
}

//Extract data to export to XML
$sqlQuery = 'SELECT * FROM t1';
if (!$result = $mysql->query($sqlQuery)) {
    throw new Exception(sprintf('Mysqli: (%d): %s', $mysql->errno, $mysql->error));
}

//Create new document 
$dom = new DOMDocument;
$dom->preserveWhiteSpace = FALSE;

//add table in document 
$table = $dom->appendChild($dom->createElement('table'));

//add row in document 
foreach($result as $row) {
    $data = $dom->createElement('row');
    $table->appendChild($data);

    //add column in document 
    foreach($row as $name => $value) {

        $col = $dom->createElement('column', $value);
        $data->appendChild($col);
        $colattribute = $dom->createAttribute('name');
        // Value for the created attribute
        $colattribute->value = $name;
        $col->appendChild($colattribute);           
    }
}

/*
** insert more nodes
*/

$dom->formatOutput = true; // set the formatOutput attribute of domDocument to true 
// save XML as string or file 
$test1 = $dom->saveXML(); // put string in test1
$dom->save($filename); // save as file
$dom->save('xml/'.$filename);   
?>

#5

<?php

    // Create connection
    $con=mysqli_connect("localhost","root","root","students");

    // Check connection
    if (mysqli_connect_errno())
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    //get student ID from URL
    $STU_ID = $_GET['id'];

    $sql = "SELECT * FROM students_info WHERE ID = ".$STU_ID;
    $res = mysqli_query($con, $sql);

    $xml = new XMLWriter();

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

    $xml->startElement('students');

    while ($row = mysqli_fetch_assoc($res)) {
      $xml->startElement("student");

      $xml->writeElement("id", $row['ID']);
      $xml->writeElement("name", $row['name']);
      $xml->writeElement("gpa", $row['GPA']);
      $xml->writeRaw($row['student']);

      $xml->endElement();
    }

    $xml->endElement();

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

    // Free result set
    mysqli_free_result($result); 
    // Close connections
    mysqli_close($con);
?>
Output
<students>
    <student>
        <id>111</id>
        <name>sara</name>
        <gpa>4.5</gpa>
    </student>
</students>