Post by Digital Sushi » Mon Dec 06, 2010 7:32 pm

I’m not the best PHP programmer in the world, but I have an important task that I want done. 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:

Code: Select all

<?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_description. 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.

Can anyone here help me? This may benifit others in my position.

Active Member

Posts

Joined
Mon Oct 18, 2010 10:23 pm
Location - South Africa

Post by JAY6390 » Mon Dec 06, 2010 9:05 pm

Your query should look more like

SELECT * FROM product p LEFT JOIN product_description pd ON p.product_id = pd.product_id

If you wanted to have it only for a certain language, you would need to change it to
SELECT * FROM product p LEFT JOIN product_description pd ON p.product_id = pd.product_id WHERE pd.language_id = 1
changing 1 to whatever the language id is

Image


User avatar
Guru Member

Posts

Joined
Wed May 26, 2010 11:47 pm
Location - United Kingdom
Who is online

Users browsing this forum: No registered users and 4 guests