SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Export SQL 2 XML

    I've wrote this little class to export any sql query to XML
    PHP Code:
    <?php
    #
    # EXPORT SQL to XML PHP CLASS By Dragan Bajcic
    # http://hosaka.blogspot.com
    #
    class exportSQL2XML{

    public 
    $dbuser='';
    public 
    $dbpass='';
    public 
    $db='';
    public 
    $dbhost='';

        function 
    conect($database) {
            
    $my_conn = @mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
            @
    mysql_select_db$this->db$my_conn );
            return 
    $my_conn;
        }

        function 
    export($SQL,$file_path,$print_structure){
            
    $this->mysql=$this->conect($this->db);
            
    $query=mysql_query("$SQL",$this->mysql);
            
    $num_fields=mysql_num_fields($query);
            
    $FIELDS.="\t<table_info>\n";

            for (
    $i=0;$i<$num_fields;$i++){
                
    $fieldname[$i]=mysql_field_name($query,$i);
                
    $tablename[$i]=mysql_field_table($query,$i);
                
    $FIELDS.="\t\t<field>\n\t\t\t<table>$tablename[$i]</table>\n\t\t\t<fieldName>$fieldname[$i]</fieldName>\n\t\t</field>\n";
            }
            
    $FIELDS.="\t</table_info>\n";

            
    $xml='<?xml version="1.0" encoding="utf-8" ?>';
            
    $xml.="\n<data>\n";
            if(
    $print_structure==1){
                
    $xml.=$FIELDS."<RS>\n";
            }else{
                
    $xml.="<RS>\n";
            }
            while(
    $rs=mysql_fetch_row($query)){

                
    $xml.="\t<rec>\n";
                for (
    $j=0;$j<$num_fields;$j++){
                    
    $xml.="\t\t<$fieldname[$j]>$rs[$j]</$fieldname[$j]>\n";
                }
                
    $xml.="\t</rec>\n";

            }
            
    $xml.="</RS>\n</data>";
            
    file_put_contents($file_path,$xml);

        }


    }
    #  Example on how to use this class




    $ex= new exportSQL2XML();

    $ex->dbuser='user';         // MySQL username
    $ex->dbpass='pass';         // MySQL password
    $ex->db='database';         // MySQL database
    $ex->dbhost='localhost';    // MySQL host

    $sql="SELECT
    products.name as product,
    products.price,
    categories.name as category,
    dealers.name as dealer
    FROM products
    LEFT JOIN categories
    on products.cat_id = categories.id
    LEFT JOIN dealers
    ON products.dealer_id = dealers.id
    ORDER BY price ;"
    ;


    #  usage $ex->export(sql_query,xml_file_path);
    $ex->export($sql,'/path/to/products.xml',0);


    ?>
    and here is example output
    Code:
    <?xml version="1.0" encoding="utf-8" ?>
    <data>
    <RS>
    	<rec>
    		<product>Product 2</product>
    		<price>2.93</price>
    		<category>Cat 2</category>
    		<dealer>Dealer B</dealer>
    	</rec>
    	<rec>
    		<product>Product 1</product>
    		<price>123.64</price>
    		<category>Cat 1</category>
    		<dealer>Dealer B</dealer>
    	</rec>
    	<rec>
    		<product>Product 3</product>
    		<price>233.99</price>
    		<category>Cat 2</category>
    		<dealer>Dealer A</dealer>
    	</rec>
    	<rec>
    		<product>Product 4</product>
    		<price>523.00</price>
    		<category>Cat 1</category>
    		<dealer>Dealer B</dealer>
    	</rec>
    </RS>
    </data>
    SQL and PHP files used in this example are attached to this post...
    cheers
    Attached Files Attached Files
    my little Blog

  2. #2
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tip to improve it-

    what happens when the database contains the < or > or & character in one of the fields?
    htmlspecialchars()


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •