CODEWITHSUNDEEP
phpmysqlxml
max_
max_

Reputation: 24481

Get MySQL database output via PHP to 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:

  1. udid
  2. country

Upvotes: 13

Views: 59404

Answers (6)

jechaviz
jechaviz

Reputation: 551

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>";
    }
}
?>

Upvotes: 0

Nutan Mishra
Nutan Mishra

Reputation: 11

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

Upvotes: 1

golucoder
golucoder

Reputation: 51

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

Upvotes: 1

Nuri Akman
Nuri Akman

Reputation: 820

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

?>

Upvotes: 1

krtek
krtek

Reputation: 26597

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

Upvotes: 9

Czechnology
Czechnology

Reputation: 14992

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>

Upvotes: 32

Related Questions