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:
- udid
- country
0
Add a comment
|
6 Answers
An example with 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>
1 Comment
Czechnology
@XcodeDev, you have to modify the SQL code to your database schema.
<?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.
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
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;
?>
Comments
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.
<?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);
?>
Comments
<?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>
1 Comment
mmgross
I improved your code formatting. I would suggest you improve your answer further by giving an explanation of your code including what it does and why and how this answers the questions.
Same than @krtek but for PHP 7 and above
<?php
$server='127.0.0.1';
$user='root';
$pass='mysql';
$db='mydb';
$query='SELECT `udid`, `country` FROM `MyTable`';
$mysqli = new mysqli($server, $user, $pass, $db);
$result = $mysqli->query($query);
while ($data = $result->fetch_assoc()) {
foreach($data as $key => $value) {
echo "<$key>$value</$key>";
}
}
?>
