How to export SQL result to XML file in PHP

Hello friends, today I will show you how to export SQL result to XML file in PHP. When we are working with any SQL result set it gives us one or many rows. We use this result in our application. But when you fetch rows using sql but instead of showing result if you need to store the result set for future use or any other reason, you may use xml file to store the result set. So I build a code to export sql result to xml in PHP.

Here I have a table name product. I will fetch all the rows from this table and store them in a xml file. At last this xml file will save to your directory and you will see the xml file output to your browse.



<?php
/*
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(8,5) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'iPod Classic', '10000', '100'), (NULL, 'iPhone6', '40000', '20');
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'MacBook Air', '30000', '60'), (NULL, 'MacBook Pro', '40000', '20');
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'iPod Nano', '20000', '60'), (NULL, 'Sony VAIO', '15000', '80');
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'HP LP3065', '26000', '40'), (NULL, 'iPod Touch', '25000', '34');
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'iPod Shuffle', '10000', '45'), (NULL, 'Nikon D300', '15000', '20');
INSERT INTO `products` (`id`, `name`, `price`, `quantity`) VALUES (NULL, 'Samsung Galaxy Tab', '17000', '45'), (NULL, 'Canon EOS 5D', '14000', '56');
*/

$mysqli = new mysqli("localhost", "root", "", "db_product");
if (mysqli_connect_errno()) {
	die("ERROR: Cannot connect. " . mysqli_connect_error());
}
// create and execute SELECT query
$sql = "SELECT id, model, quantity, price FROM product";
if ($result = $mysqli->query($sql)) {
	// if results exist
	// initialize DOM object
	$xml = new DOMDocument("1.0");
	// add root node
	$root = $xml->createElement("resultset");
	$xml->appendChild($root);
	// iterate over result set
	// print <record>s and <field>s
	if ($result->num_rows > 0) {
		while($row = $result->fetch_row()) {
			$record = $xml->createElement("record");
			$root->appendChild($record);
			$fieldCount = 0;
			while ($fieldCount < $mysqli->field_count) {
				$field = $xml->createElement("field");
				$record->appendChild($field);
				$field->appendChild($xml->createTextNode($row[$fieldCount]));
				$fieldCount++;
			}
		}
	}
	$result->close();
} else {
	die("ERROR: " . $mysqli->error . " (query was $sql)");
}
// close connection
$mysqli->close();
// display XML result set as HTML...
$xml->formatOutput = true;
echo "<xmp>" . $xml->saveXML() . "</xmp>";
// ...or write it to a file as XML
$time = time();
$xml->save("results.$time.xml") or die("ERROR: Could not write to file");
?>

Thats All. If you like my post please share it.

Spread the love
  •  
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
    1
    Share