php mysql to xml
Converting MySQL rows to XML with PHP
I’m working on my php script to output the information from mysql database. I want to output these echo results in my php page as a xml file as i want to make it looks like this:
Here’s what my php output looks like:
Edit: I have got a problem with echo. I can’t be able to echo for the channels in the database.
Here’s the error I have got: error on line 12 at column 11: XML declaration allowed only at the start of the document
I will get the error when I’m using this under the while statement:
The output for my php is show as blank page. How do you use the code to allow me to print these xml output in my php?
2 Answers 2
if channel = 0 and your output is correct. It may not be tabbed, but it is correct to your logic.
It looks like you are already successfully generating XML. Your problem is that it shows up as a blank page. This is expected, because your web browser is going to interpret the XML as a bunch of HTML tags, and tags don’t get displayed.
What you probably want to do is set the content type to XML so that the web browser knows it’s XML and not HTML. You can do this by adding the header before you send any other output (i.e. before any echo statements):
This is necessary because if you don’t specify the content type explicitly, the server will automatically send a content type header that tells the browser that the content is HTML.
You can use your browser’s debugging console (press F12 to open it in most modern browsers) to inspect the HTTP headers that your script is sending and verify that it’s declaring the correct content type.
The other alternative is to format the XML as HTML. This probably isn’t really desirable, because the purpose of XML is the be processed by some other client and turning it into HTML makes it useless. However, if you really want to, it can be done by putting all the XML in a string and then calling the htmlspecialchars function to format it as HTML. (This turns the and > into HTML entities, causing them to be displayed in your browser.)
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:
6 Answers 6
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.
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.
Same than @krtek but for PHP 7 and above
Not the answer you’re looking for? Browse other questions tagged php mysql xml or ask your own question.
Linked
Related
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2021.9.17.40238
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.
How to generate xml files using php and mysql database
So to share book data with other people you are going to provide an XML file containing books information from MySQL database.
In order to generate XML files using php DOMDocument class, we are going to do following steps.
2. Create a table and insert sample data into it
3. Fetch data from the database and generate XML file.
Create a database dbbookstore and table tbl_books
First, we are going to create database for our bookstore application and a table for books.
SQL for creating database dbbookstore and table tbl_books
DOMDocument class to generate XML files using php
PHP DOMDocument class can be used to read or generate XML files. Here we want to generate XML file from a bookstore database. Generated XML document will contain title, author name, price, ISBN and category of books. An attribute id in book element will be added. In order to do above tasks you have to:
2. Select records from books table and add to an array
3. Create XML file by calling createXMLfile() function
1. Connect to database using mysqli
First you need to connect to database using mysqli method. This method requires host, username, password and database. If connection is successful, a connection object is returned else an error is thrown.
2. Select records from books table
Next to select records from books table. A query is passed to connection object’s query() method. query() method returns a result set. To fetch data rows from result set fetch_assoc function is used. This function fetches rows from result set as an associative array.
In a while loop data rows are pushed into an array. If there are records in the array then createXMLfile() function is called with books array parameter.
3. Create XML file using createXMLfile() function
Finally to generate XML file using php DOMDocument class a function createXMLfile is created. and array of books data is passed as parameter.
Source code to generate XML files using php
Download source code from GitHub
Source code for this tutorial can be found at GitHub. Please clone or download from this repository.
Summary
To sum up, in this tutorial we learned how to generate XML files using php from bookstore database. Please leave your valuable feedback and comments.
Insert XML Data to MySql Table
Programmatically importing XML data into a MySQL database will be handy in many a situations. In this tutorial, we are going to learn how to insert data from XML file to the database using PHP.
The XML nodes are considered as the columns of the database table and so the data of each node are the values to be inserted into the table.
In this example, I have an input.xml file containing item tags with child nodes named as title, description, links and keywords. This input file is loaded to get the file handle object to parse the XML data.
Then, the PHP code will iterate each of the parsed item to get its child node and its data. With this data MySQL insert query is created and executed to copy XML data to the database.
Input XML Data
The following XML is the input for our example program to insert XML data into the database table. This file contains 3 item XML elements with the child nodes title, link, description and keywords tags.
As the child nodes are treated as the columns of the database table, I have created the database table columns with the name of these child nodes. While parsing this input XML the data inside these child nodes will be inserted into the database to the appropriate column.
and the SQL script for creating the database table is,
Parse XML and Insert Data to MySQL using PHP
In this PHP code, we use simple XML parsing to load the input XML file to create the file handle. Using this file handle the XML items will be start iterated to read the child nodes. Then I create the MySQL insert query by using the data read from the XML.
After inserting the data, a message will be shown to the user about how many rows are newly added into the database. In case of any error occurrences, then the error message will be shown to the user about the cause of the error.
Insert XML Data into MySql Table Output
This code checks the condition for the response text created during the process of inserting XML data into the database table. This response text is to acknowledge the user about the result of the MySQL insert.
Php mysql to xml
The LOAD XML statement reads data from an XML file into a table. The file_name must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets ( and > ).
This statement supports three different XML formats:
Column names as attributes and column values as attribute values:
Column names as tags and column values as the content of these tags:
Column names are the name attributes of tags, and values are the contents of these tags:
All three formats can be used in the same XML file; the import routine automatically detects the format for each row and interprets it correctly. Tags are matched based on the tag or attribute name and the column name.
Prior to MySQL 8.0.21, LOAD XML did not support CDATA sections in the source XML. (Bug #30753708, Bug #98199)
The following clauses work essentially the same way for LOAD XML as they do for LOAD DATA :
LOW_PRIORITY or CONCURRENT
See Section 13.2.7, “LOAD DATA Statement”, for more information about these clauses.
Suppose further that this table is initially empty.
Each of the permissible XML formats discussed previously is represented in this example file.
To import the data in person.xml into the person table, you can use this statement:
Here, we assume that person.xml is located in the MySQL data directory. If the file cannot be found, the following error results:
The ROWS IDENTIFIED BY ‘
‘ clause means that each
element in the XML file is considered equivalent to a row in the table into which the data is to be imported. In this case, this is the person table in the test database.
As can be seen by the response from the server, 8 rows were imported into the test.person table. This can be verified by a simple SELECT statement:
You can verify that the dump is valid by creating a copy of the person table and importing the dump file into the new table, like this:
There is no requirement that every field in the XML file be matched with a column in the corresponding table. Fields which have no corresponding columns are skipped. You can see this by first emptying the person2 table and dropping the created column, then using the same LOAD XML statement we just employed previously, like this:
The order in which the fields are given within each row of the XML file does not affect the operation of LOAD XML ; the field order can vary from row to row, and is not required to be in the same order as the corresponding columns in the table.
In this case, you cannot simply load the XML file directly into the table, because the field and column names do not match:
The names of the user variables must match those of the corresponding fields from the XML file, with the addition of the required @ prefix to indicate that they are variables. The user variables need not be listed or assigned in the same order as the corresponding fields.
Using a ROWS IDENTIFIED BY ‘ tagname >’ clause, it is possible to import data from the same XML file into database tables with different definitions. For this example, suppose that you have a file named address.xml which contains the following XML:
You can again use the test.person table as defined previously in this section, after clearing all the existing records from the table and then showing its structure as shown here:
Now create an address table in the test database using the following CREATE TABLE statement:
To import the data from the XML file into the person table, execute the following LOAD XML statement, which specifies that rows are to be specified by the
element, as shown here;
You can verify that the records were imported using a SELECT statement:
To import the data from the elements into the address table, use the LOAD XML statement shown here:
You can see that the data was imported using a SELECT statement such as this one:
Security Considerations. As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD XML statement. Such a server could access any file on the client host to which the client user has read access.
In a Web environment, clients usually connect to MySQL from a Web server. A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. In this environment, the client with respect to the MySQL server is actually the Web server, not the remote program being run by the user who connects to the Web server.
To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it.
In other words, the FILE privilege affects only whether the client can read files on the server; it has no bearing on whether the client can read files on the local file system.