通过 PHP 获取 MySQL 数据库输出到 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5112282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get MySQL database output via PHP to XML
提问by max_
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 输出：
- udid
- country
- 你做了
- 国家
回答by Czechnology
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>
回答by krtek
<?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 数据。
回答by Nuri Akman
<?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;
?>
回答by golucoder
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);
?>
回答by Nutan Mishra
<?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>
