1. Tutorials
  2. PHP Tutorial
  3. Convert MySQL Table Data to XML Using PHP: A Step-by-Step Guide to Export Database Records

Convert MySQL Table Data to XML Using PHP: A Step-by-Step Guide to Export Database Records

Submitted by oretnom23 on Saturday, November 11, 2023 - 11:44.

This tutorial will guide you through the systematic procedure of Exporting MySQL Database Table Records to a XML File using the PHP language. Geared towards students and PHP beginners, this guide serves as a valuable reference, offering insights to bolster their understanding and programming skills. I will present a straightforward web application that illustrates the primary objective outlined in this article.

What is XML?

eXtensible Markup Language, commonly referred to as XML, stands as a versatile markup language designed for the storage and transportation of data. Employing a set of rules that ensure both human and machine readability when encoding documents, XML's adaptability and self-descriptive characteristics have led to its widespread utilization in diverse applications. These applications include handling configuration files, facilitating data interchange between web services, and representing hierarchical data structures.

Here are some other tutorials or articles that are related in XML:

Explore the XML-related topics listed above by clicking on them to be redirected to their dedicated articles for a more in-depth understanding.

How to Export MySQL Database Table Records into XML File?

Before getting started, please download and install the following software on your local machine if they are not already installed:

  • XAMPP/WAMP or any equivalent local web and MySQL server software
  • Code Editor such as Notepad++, Sublime Text, and MS VS Code

After successfully installing the aforementioned requirements, ensure that you have started the Apache and MySQL servers. In XAMPP/WAMP, open their control panel and click the start buttons for the mentioned servers. The status of each server will be displayed in the control panel logs.

Let's Get Started

Step 1: Let's Create the Database and the Table

Initial steps involve creating the Database for our sample web application. Open PHPMyAdmin and navigate to the SQL page. Confirm that the dummy_db database name is still accessible in your MySQL. Utilize or refer to the MySQL script below for the creation of the database, table, and columns:

  1. --
  2. -- Database: `dummy_db`
  3. --
  4. CREATE DATABASE IF NOT EXISTS `dummy_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  5. USE `dummy_db`;
  6.  
  7. -- --------------------------------------------------------
  8.  
  9. --
  10. -- Table structure for table `members`
  11. --
  12.  
  13. CREATE TABLE IF NOT EXISTS `members` (
  14.     `id` int(11) NOT NULL AUTO_INCREMENT,
  15.     `name` varchar(255) DEFAULT NULL,
  16.     `username` varchar(50) DEFAULT NULL,
  17.     `email` varchar(100) DEFAULT NULL,
  18.     `contact_no` varchar(15) DEFAULT NULL,
  19.     PRIMARY KEY (`id`),
  20.     UNIQUE KEY `username` (`username`),
  21.     UNIQUE KEY `email` (`email`)
  22. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  23.  
  24. --
  25. -- Dumping data for table `members`
  26. --
  27.  
  28. INSERT INTO `members` (`id`, `name`, `username`, `email`, `contact_no`) VALUES
  29. (1, 'John Doe', 'johndoe', '[email protected]', '123-456-7890'),
  30. (2, 'Jane Smith', 'janesmith', '[email protected]', '987-654-3210'),
  31. (3, 'Bob Johnson', 'bobjohnson', '[email protected]', '555-123-4567'),
  32. (4, 'Alice Brown', 'alicebrown', '[email protected]', '789-456-1230'),
  33. (5, 'Charlie Wilson', 'charliewilson', '[email protected]', '555-789-0123'),
  34. (6, 'Eva Garcia', 'evagarcia', '[email protected]', '123-789-4560'),
  35. (7, 'David Lee', 'davidlee', '[email protected]', '456-123-7890'),
  36. (8, 'Emily Davis', 'emilydavis', '[email protected]', '789-012-3456'),
  37. (9, 'Frank Miller', 'frankmiller', '[email protected]', '012-345-6789'),
  38. (10, 'Grace Turner', 'graceturner', '[email protected]', '987-654-3210');

Executing the provided MySQL script will yield a result similar to the image below:

Exporting MySQL Database Record to XML file DB Structure

Step 2: Creating the Database Connection

Moving forward, let's generate the PHP file containing the scripts that establish a connection between our PHP web application and the MySQL Database. To accomplish this, create a new PHP file within your web application's source code directory and open it with your preferred code editor. Save the file as `db-connect.php`. Consult the following PHP script for composing the database connection script in PHP:

  1. <?php
  2. // DB Hostname
  3. $host = "localhost";
  4. // DB username
  5. $uname = "root";
  6. // DB password
  7. $pw = "";
  8. // DB Name
  9. $dbName = "dummy_db";
  10.  
  11. try{
  12.     // Opening Database Connection
  13.     $conn = new MySQLi($host, $uname, $pw, $dbName);
  14. }catch(Exception $e){
  15.     // Opening Database Connection Failed
  16.     echo "Database connection Error:\n<br>\n";
  17.     print_r($e->getMessage());
  18.     exit;
  19. }

Ensure to replace the variable values with the correct database credentials in the script above on your end.

Step 3: Creating Web Application Page Interface

Now, let's establish the page interface for our sample web application. Create a new PHP file named `index.php`. This file contains a combined PHP and HTML scripts, relevant for the page interface that displays the retrieved database records. It also incorporates the PHP script responsible for retrieving records from the database and includes the PHP file for data export or transformation.

Note that the script includes CDN links for loading the Bootstrap Framework and jQuery Library. Therefore, an internet connection is necessary for the expected page design to load when browsing the page.

  1. <?php
  2. // Start Session
  4. // Require Database Connection File
  5. require_once("db-connect.php");
  6.  
  7. /**
  8.     * Retrieve Members Data from MySQL Database
  9.     */
  10. // SQL Statement
  11. $members_sql = "SELECT * FROM `members` order by id asc";
  12. // SQL Query
  13. $members_qry = $conn->query($members_sql);
  14. // SQL Execution and storing retrive data in a variable
  15. $members = $members_qry->fetch_all(MYSQLI_ASSOC);
  16.  
  17. /**
  18.     * Including the PHP file that contains the Script that Exports MySQL Data into XML File  
  19.     */
  20. if(isset($_GET['action']) && $_GET['action'] == 'export_as_xml'){
  21.     include_once("export_as_xml.php");
  22. }
  23.  
  24.  
  25. ?>
  26. <!DOCTYPE html>
  27. <html lang="en">
  28. <head>
  29.     <meta charset="UTF-8">
  30.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  31.     <title>Create XML from MySQL Data</title>
  32.     <!-- Bootstrap 5.3 CSS-->
  33.     <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
  34.     <!-- jQuery -->
  35.     <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
  36.     <!-- Bootstrap 5.3 JS-->
  37.     <script src="       https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
  38. </head>
  39. <body>
  40.     <nav class="navbar navbar-expand-lg bg-body-tertiary">
  41.         <div class="container-fluid">
  42.             <a class="navbar-brand" href="#">Create XML From MySQL Data</a>
  43.             <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
  44.             <span class="navbar-toggler-icon"></span>
  45.             </button>
  46.             <div class="collapse navbar-collapse" id="navbarNav">
  47.             <ul class="navbar-nav">
  48.                 <li class="nav-item">
  49.                 <a class="nav-link <?= $page == 'home' ? "active" : '' ?>" href="./">Home</a>
  50.                 </li>
  51.             </ul>
  52.             </div>
  53.         </div>
  54.     </nav>
  55.     <div class="container-md py-4">
  56.         <!-- Flashdata -->
  57.         <?php if(isset($_SESSION['flashdata']) && !empty($_SESSION['flashdata'])): ?>
  58.             <div class="alert alert-<?= $_SESSION['flashdata']['type'] ?> px-2 py-1 rounded-0">
  59.                 <?= $_SESSION['flashdata']['msg'] ?>
  60.             </div>
  61.         <?php unset($_SESSION['flashdata']); ?>
  62.         <?php endif; ?>
  63.         <!-- End of Flashdata -->
  64.  
  65.         <div class="card rounded-0 mx-auto col-lg-9 col-md-10 col-sm-12 col-12">
  66.             <div class="card-header rounded-0">
  67.                 <div class="d-flex w-100 justify-content-between align-items-center">
  68.                     <h5 class="card-title col-auto flex-grow-1">Sample Site Member List</h5>
  69.                     <div class="col-auto flex-shrink-1">
  70.                         <a href="./?action=export_as_xml" class="btn btn-sm btn-primary border rounded-0">Export Data to XML File</a>
  71.                     </div>
  72.                 </div>
  73.             </div>
  74.             <div class="card-body rounded-0">
  75.                 <div class="container-fluid">
  76.                     <!-- Displaying MySQL Data into Table Element -->
  77.                     <div class="table-responsive">
  78.                         <table class="table table-bordered table-hover table-striped">
  79.                             <colgrep>
  80.                                     <col width="10%">
  81.                                     <col width="30%">
  82.                                     <col width="20%">
  83.                                     <col width="20%">
  84.                                     <col width="20%">
  85.                                 </colgrep>
  86.                                 <thead>
  87.                                     <tr class="bg-gradient bg-primary">
  88.                                         <th class="text-center bg-transparent text-light">ID</th>
  89.                                         <th class="text-center bg-transparent text-light">Name</th>
  90.                                         <th class="text-center bg-transparent text-light">Username</th>
  91.                                         <th class="text-center bg-transparent text-light">Email</th>
  92.                                         <th class="text-center bg-transparent text-light">Contact No.</th>
  93.                                     </tr>
  94.                                 </thead>
  95.                                 <tbody>
  96.                                     <?php if(!empty($members)): ?>
  97.                                         <?php foreach($members as $member): ?>
  98.                                             <tr>
  99.                                                 <td class="text-center px-2 py-1"><?= $member['id'] ?></td>
  100.                                                 <td class="px-2 py-1"><?= $member['name'] ?></td>
  101.                                                 <td class="px-2 py-1"><?= $member['username'] ?></td>
  102.                                                 <td class="px-2 py-1"><?= $member['email'] ?></td>
  103.                                                 <td class="px-2 py-1"><?= $member['contact_no'] ?></td>
  104.                                             </tr>
  105.                                         <?php endforeach; ?>
  106.                                     <?php endif; ?>
  107.                                 </tbody>
  108.                             </table>
  109.                         </div>
  110.                         <!-- End of Displaying MySQL Data into Table Element -->
  111.                     </div>
  112.                 </div>
  113.             </div>
  114.         </div>
  115.     <?php
  116.     // Closing Database Connection
  117.     if($conn)
  118.         $conn->close();
  119.     ?>
  120.     </body>
  121.     </html>

Exporting MySQL Database Record to XML file DB Structure

Step 3: Creating Main PHP Script

Finally, let's craft the PHP file housing the script responsible for generating XML content from the MySQL Database Table Records. Create a new PHP file named `export_as_xml.php`. This file contains the script utilizing the DOMDocument built-in class in PHP.

The DOMDocument class is a component of the Document Object Model (DOM) extension in PHP. It offers a method to represent an entire document, be it HTML or XML, as an Object-Oriented tree structure. In this structure, each node corresponds to a component of the document, such as elements, attributes, or text.

  1. <?php
  2. if(!empty($members)){
  3.     // Creating a new DOMDocument
  4.     $xml = new DOMDocument();
  5.     // Creating the Root Element
  6.     $membersEl = $xml->createElement('members');
  7.  
  8.     // Looping Members Row
  9.     foreach($members as $member){
  10.         // Creating Member Row Element and adding it into the root Element
  11.         $newMemberRow = $membersEl->appendChild($xml->createElement('member'));
  12.  
  13.         foreach($member as $field => $value){
  14.             // Creating the Memeber's Info Field Name and Value Element and adding it to the current memeber row's element
  15.             $newMemberRow->appendChild($xml->createElement($field, $value));
  16.         }
  17.     }
  18.     // adding the root Members Element with the child member elements into the created document
  19.     $xml->appendChild($membersEl);
  20.     // Nicely printing the format of the output with indentions
  21.     $xml->formatOutput = true;
  22.     // Save in new or overwrite existing XML file with the current XML File Content
  23.     $xml->save("members.xml");
  24.  
  25.  
  26.     // Storing a Success Flash Message to display in the UI
  27.     $_SESSION['flashdata'] = [
  28.         "type" => "success",
  29.         "msg" => "Members Data has been successfully exported in XML File"
  30.     ];
  31.  
  32.  
  33. }else{
  34.     // members data is empty
  35.  
  36.     // Storing Error Flash Message to display in the UI
  37.     $_SESSION['flashdata'] = [
  38.         "type" => "danger",
  39.         "msg" => "Members Data is Empty"
  40.     ];
  41. }
  42. // reloading the page using location.replace JS script to remove the provided GET request data
  43. echo "<script>location.replace('./');</script>";
  44. die();
  45. ?>

Upon executing the provided scripts, clicking the `Export Data to XML File` button above the table in the page interface will initiate the export of MySQL database table records into an XML file. Upon successful completion of the process, a confirmation message will be displayed, as shown in the image below:

Exporting MySQL Database Record to XML file Success Message

The resulting exported XML file will be named `members.xml`, containing content similar to the following:

Exporting MySQL Database Record to XML file Result

I've also included the complete compressed source code file of the provided scripts on this website. You can download it by clicking the Download button located below this tutorial article.

And that's it! I hope this PHP Tutorial on Exporting MySQL Database Records into XML Files proves beneficial, aiding you in your PHP projects. Explore further on this website for additional Free Source Codes, Tutorials, and Articles spanning various programming languages.

Happy Coding =)

Tags
php
MYSQL
php tutorial
MySQL Tutorial
PHP XML
PHP DOMDocument Class
Export MySQL DB record into XML
xml

Comments

Add new comment

About text formats