Get MySQL database output via PHP to XML
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>
MySQL to XML using PHP script
This code works just fine in order to convert MYSQL to XML with PHP, however there was an error in the 8th row as suggested by Steve in the comments. Always check your apostrophes folks.
PHP XML Output from mySQL Database
Try this.
$rows = [
['channel_name' => 'ASN', 'program_id' => 1, 'program_title' => 'Program 1'],
['channel_name' => 'ASN', 'program_id' => 2, 'program_title' => 'Program 2'],
['channel_name' => 'NBC', 'program_id' => 3, 'program_title' => 'Program 3'],
];
// pre grouping pattern
$grouped = array_reduce($rows, function($channels, $row) {
if (!array_key_exists($row['channel_name'], $channels)) {
$channels[$row['channel_name']] = [];
}
$channels[$row['channel_name']][] = $row;
return $channels;
}, []);
foreach ($grouped as $channelName => $program) {
// build up XML
}
// Or SimpleXML pattern
$root = new SimpleXMLElement('<root />');
foreach ($rows as $row) {
$channel = $root->xpath("/root/channel/channel_name[text()='{$row['channel_name']}']/parent::node()");
if (count($channel) === 0) {
$channel = $root->addChild('channel');
$channel->addChild('channel_name', $row['channel_name']);
} else {
$channel = $channel[0];
}
$program = $channel->addChild('program');
$program->addAttribute('id', $row['program_id']);
$program->addChild('title', $row['program_title']);
}
echo $root->asXML();
how do i output an xml file using php
Here's some example code using PHP's built in classes to build an XML tree. This isn't 100% of what you're asking for, but it demonstrates how to create the structure, add children, add attributes, etc. Using this snippet, you can get there from here. I use indentation to help me keep the tree structure straight...
// create a dom document with encoding utf8
$domtree = new DOMDocument('1.0', 'UTF-8');
// create a root element of the xml tree
$tv = $domtree->createElement('tv');
//create attributes for element
$generator_info_name = $domtree->createAttribute('generator-info-name');
$generator_info_name->value = 'www.mysite.com/xmltv';
//append attribute
$tv->appendChild($generator_info_name);
// append element to the doc
$tv = $domtree->appendChild($tv);
//add a channel as a child of the root
$channel = $domtree->createElement('channel');
$channel_id = $domtree->createAttribute('id');
$channel_id->value = '""';
$channel = $tv->appendChild($channel);
//append children to channel
$channel->appendChild($domtree->createElement('display-name','Information from database'));
$channel->appendChild($domtree->createElement("programme"));
$channel->appendChild($domtree->createElement('desc'));
//finally, save the file
echo $domtree->saveXML();
$domtree->save('myChannel.xml');
How can export mysql data in to xml using php
When we are dealing with XML and HTML, the best way to act is ever through a parser.
In this particular situation, operating with a parser guarantees a valid XML and a clean, short code.
After defining mySQL query, we init a new DOMDocument
with version and encoding, then we set his ->formatOutput
to True to print out XML in indented format:
$query = "SELECT AB.id, AB.name, AB.firstname, AB.street, AB.zipcode, AB.city_id, CI.city FROM address_book AS AB INNER JOIN city AS CI ON AB.city_id = CI.id";
$dom = new DOMDocument( '1.0', 'utf-8' );
$dom ->formatOutput = True;
Then, we create the root node and we append it to DOMDocument
:
$root = $dom->createElement( 'addressbook' );
$dom ->appendChild( $root );
At this point, after executing mySQL query, we perform a while
loop through each resulting row; for each row, we create an empty node <address>
, then we perform a foreach
loop through each row's field. For each field, we create an empty childnode with tag as field key, then we append to childnode the field value as CDATA and the same childnode to <address>
node; at the end of each while
loop, each <address>
node is appended to root node:
$result = $mysqli->query( $query );
while( $row = $result->fetch_assoc() )
{
$node = $dom->createElement( 'address' );
foreach( $row as $key => $val )
{
$child = $dom->createElement( $key );
$child ->appendChild( $dom->createCDATASection( $val) );
$node ->appendChild( $child );
}
$root->appendChild( $node );
}
Now, your XML is ready.
If you want save it to a file, you can do it by:
$dom->save( '/Your/File/Path.xml' );
Otherwise, if you prefer send it as XML you have to use this code:
header( 'Content-type: text/xml' );
echo $dom->saveXML();
exit;
If you want instead output it in HTML page, you can write this code:
echo '<pre>';
echo htmlentities( $dom->saveXML() );
echo '</pre>';
- See more about DOMDocument
display mysql query results in xml format using php
You made mistake in variable name ($result['courseID']
not $res['courseID']
).
<?php
header ("content-type: text/xml");
include("database.php");
$xml='<?xml version="1.0" encoding="UTF-8"?>';
$res=$pdo->query('SELECT * FROM sk_courses ORDER BY courseID ASC');;
$xml.='<courses>';
while ($result=$res->fetch(PDO::FETCH_ASSOC)){
$xml.='<course>
<courseID>'.$result['courseID'].'</courseID>
<courseName>'.$result['courseName'].'</courseName>
</course>';
}
$xml.='</courses>';
echo $xml;
?>
How to retrieve data from MySQL database and save it to xml file
Tried xml export via mysql command line ?
e.g.
mysql -uroot -p --xml -e 'SELECT * FROM mydb.Table1' > table1.xml
See the MySQL manual command options for xml
And I just saw that you have access to the database thru an html form, and maybe can't run a command line?
Otherwise you could do your own xmldoc from a select, a simple query I made here SQLFIDDLE.
create table animal(id int, name char(10));
insert into animal values
(1,'dog'),
(2,'cat'),
(3,'mouse'),
(4,'horse'),
(5,'cow');
SELECT
CONCAT('\n<animals>\n',
GROUP_CONCAT('<animal id=', id, '>',name,'</animal>\n' SEPARATOR ''),
'</animals>') AS xmldoc
FROM animal;
Output would be:
<animals>
<animal id=1>dog</animal>
<animal id=2>cat</animal>
<animal id=3>mouse</animal>
<animal id=4>horse</animal>
<animal id=5>cow</animal>
</animals>
The sqlfiddle
Converting MYSQL table data directly to an XML in PHP
There is no inbuilt function that will create XML directly from MYSQL result-set after executing SELECT query.
You have to write code for this
Some nice tutorials are this..
http://www.codediesel.com/php/converting-mysql-queries-to-xml/
http://www.mightywebdeveloper.com/coding/mysql-to-xml-php/
Whats the best way to generate a XML output of a MySQL database using PHP
Do not use PHP at all for the XML generation. MySQL can do that. If the XML in a specific format you can use XSLT, but at that point it may be easier to use PHP instead.
Related Topics
You Must Enable the Openssl Extension to Download Files via Https
How to Get Customer Details from an Order in Woocommerce
MySQL No Connection Could Be Made Because the Target MAChine Actively Refused It
Hide Shipping Methods for Specific Shipping Class in Woocommerce
Why Can't PHP Create a Directory with 777 Permissions
How to Convert Datetime to Iso 8601 in PHP
Traits in PHP - Any Real World Examples/Best Practices
PHP Regex to Check Date Is in Yyyy-Mm-Dd Format
What Is Pdo & Why Should I Use It
What Is the Most Efficient Way to Count All the Occurrences of a Specific Character in a PHP String
Parse Xml Namespaces with PHP Simplexml
How to Easily Consume a Web Service from PHP
Using PHP to Populate a <Select></Select> Dropdown
Force Browser to Download Image with JavaScript Window.Open