How to have PHP create a XML file from 2 tables?

[grahampvdm]

I’m not the best PHP programmer in the world, but my boss has given me a fairly important task. What I need to do is write a PHP file, which takes data from 2 MySQL tables and then spits out a XML file with the data. The data is coming from the OpenCart ecommerce system.

This is what I have so far:

<?php

header('Content-type: text/xml');

// It will be called digitalsushiitems.xml
header('Content-Disposition: attachment; filename="digitalsushiitems.xml"');

// connect to database
$db_name = "database";
$connection = mysql_connect("localhost", "username", "password") or die("Could not connect.");

// querying mysql
$db = mysql_select_db($db_name, $connection) or die("Could not connect.");;

$result = mysql_query("SELECT * FROM product_selection NATURAL JOIN product");

// XML File Creation
$buf ='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ROOT>
<Products>
';

while($row=mysql_fetch_array($result)) {

      $buf .=  "<Product>
   <ProductCode><![CDATA[ ".htmlentities($row[2]) ." ]]></ProductCode>
   <Title><![CDATA[ ". htmlentities($row2[26]) ." ]]></Title>
   <Category><![CDATA[ ". htmlentities($row[5]) ." ]]></Category>
   <Price>no</Price>
   <Quantity>". htmlentities($row[5]) ."</Quantity>
   <Condition>New</Condition>
   <Location></Location>
   <ShippingOption>Speed Services</ShippingOption>
   <ImageURL><![ CDATA[ http://www.digitalsushi.co.za/image/". htmlentities($row[6]) ." ]]></ImageURL>
   <Description><![CDATA[ ". htmlentities($row2[27]) ." ]]></Description>
 </Product>
 ";
}

$buf .= "</Products>
</ROOT>";

echo $buf;

mysql_close($connection);;
$buf="";

?>

At present it’s not really doing anything. This is what I’m struggling with:
1. Having it generate a .xml file (lets say bob.xml) in the root of the folder as well as generating a downloadable version when you access the .php file
2. Combining the data from 2 tables and then displaying all of it. These are the 2 tables:
Product and product_descrip tion. Both of them have a common variable which is product_id, but product description only has 2 (out of 4) columns that I need.
3. Having it repeat the process for each product id that exists in the product table.

[dlite922]

You need to know SQL to query a database. Look up a tutorial on the DBMS you have. (MySQL I assume). The things you need to know is SELECT and JOINs.

Here's an example:
SELECT column_1, col_2, some_col FROM product AS p, product_descrip tion AS pd WHERE p.product_id = pd.product_id ORDER BY col_2;

As for the XML file. Just open a regular file and write your XML structure to it. XML as you may know is just tags that YOU define.

The last thing you need to know is how to take the result of your SQL query and write it to a file. You'll need a loop (such as foreach, for, or while) and fwrite() function.

If any trouble or questions, let us know!


Dan

[grahampvdm]

Thank you for the response, dlite922. I managed to figure it out so it all seems fine now. :)

This is my code:

<?php

header('Content-type: text/xml');

// It will be called downloaded.pdf
header('Content-Disposition: attachment; filename=".xml"');

// connect to database
$db_name = "database";
$connection = mysql_connect("localhost", "username", "password") or die("Could not connect.");

// querying mysql
$db = mysql_select_db($db_name, $connection) or die("Could not connect.");;

$result = mysql_query("SELECT * FROM product_description NATURAL JOIN product");

// XML File Creation
$buf ='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ROOT>
<Products>
';

while($row=mysql_fetch_array($result)) {

      $buf .=  "<Product>
   <ProductCode><![CDATA[ ".htmlentities($row[7]) ." ]]></ProductCode>
   <Title><![CDATA[ ". htmlentities($row[2]) ." ]]></Title>
   <Category><![CDATA[ categories ]]></Category>
   <Price>". htmlentities($row[14]) ."</Price>
   <Quantity>". htmlentities($row[9]) ."</Quantity>
   <Condition>New</Condition>
   <Location>Cape Town</Location>
   <ShippingOption>Speed Services</ShippingOption>
   <ImageURL><![ CDATA[ http://www.digitalsushi.co.za/image/". htmlentities($row[11]) ." ]]></ImageURL>
   <Description><![CDATA[ ".html_entity_decode($row[5]) ." ]]></Description>
 </Product>
 ";
}

$buf .= "</Products>
</ROOT>";

echo $buf;

mysql_close($connection);;
$buf="";

?>