Get MySQL database output via PHP to XML

Question

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:

1. udid
2. country

Answer 1

<?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.

Answer 2

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>

Answer 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;

?>

Answer 4

<?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>

Answer 5

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);   
?>