Export MySQL table to formatted XML

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kchrist
Forum Newbie
Posts: 5
Joined: Mon Dec 12, 2005 3:35 am
Location: Greece

Export MySQL table to formatted XML

Post by kchrist »

Hello to all.
I'm working on an integration between a Flash Gallery (which use XML to present photos and data) and an PHP Admin Panel (which saves the data in a MySQL database).

I have a mySQL table named "products" with the following columns:

Code: Select all

`products` (
  `id` int(11) NOT NULL auto_increment,
  `code` int(11) NOT NULL default '0',
  `collection` longtext NOT NULL,
  `product_name` longtext NOT NULL,
  `caption` longtext NOT NULL,
  `subtitle` longtext NOT NULL,
  `price` varchar(100) NOT NULL default '0',
  `status` int(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;
How can I export some of the data to an XML file with the following structure, using php ? :

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<collection name="xyz" subtitle="xyz" status="xyz">
<image filename="pic1x.jpg" caption="xyz" />
etc
etc
etc
</collection>
I know I may use an Select SQL Query to select the data, but how can I map them to XML tags and attributes?
I'm using class.mysql_xml PHP Class (http://www.phpclasses.org/browse/package/782.html), but the exported XML is the following:

Code: Select all

<?xml version='1.0' encoding='UTF-8'?>
<table>
	<ROW>
		<ID>26</ID>
		<CODE>1234567890</CODE>
		<COLLECTION>1</COLLECTION>
		<PRODUCT_NAME>test</PRODUCT_NAME>
		<CAPTION>test</CAPTION>
		<SUBTITLE>TEST</SUBTITLE>
		<PRICE>100</PRICE>
		<STATUS>1</STATUS>
	</ROW>
</table>

Any idea of XML transformation using the class.mysql_xml ?

With my best regards,
Konstantinos
User avatar
wtf
Forum Contributor
Posts: 331
Joined: Thu Nov 03, 2005 5:27 pm

Post by wtf »

You should be able to parse that with Flash without any problems.
Post Reply