Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Extracting data from two tables and writing it out to XML

Not sure if Joining table accomplishes my end goal



8:44 pm on Dec 19, 2007 (gmt 0)

5+ Year Member

I'm at a stand still so I'm going to provide as much info as I can with the hope someone can help me.

My final goal is to have a php file that writes XML. (See scheme below)

I am able to extract data and write out to xml as desired from the companies table but I am stuck when it comes to getting the information how I want it from the products table. (See current code below)

The data is in 2 different tables companies and products. Each table can be associated with the other with the field co_uid. (See table structures below)

You will notice that each table has its own co_uid field. In companies there is only 1 occurrence of co_uid because there can only be one of each company. However in the products table there can be up to 50 of the same co_uid’s depending on how many products the company has. The data I need from the products table is prod_name fields with the same co_uid as the company. in one xml field separated by a soft return.

There is also one more twist. I need all the prod_name entries without a prod_type of ‘Brand’ labeled as products in the xml scheme. I also need all of all the prod_name entries with a prod_type of ‘Brand’ listed as brands in the xml scheme.

I’ve tried joining the tables but that only make duplicate companies in the XML file.

Any help anyone can provide would be much appretiated.

Thank you for you time.

XML scheme needed:

<co_phone3> </co_phone3>
<co_phone4> </co_phone4>
<co_contact1> </co_contact1>

Table Structures:

-- Table structure for table `companies`
co_id int(11) NOT NULL auto_increment,
co_uid int(11) default NULL,
co_name char(64) default NULL,
co_email char(64) default NULL,
co_web char(64) default NULL,
co_add1 char(64) default NULL,
co_add2 char(32) default NULL,
co_add3 char(32) default NULL,
co_city char(32) default NULL,
co_state char(32) default NULL,
co_zip char(16) default NULL,
co_country char(32) default NULL,
co_contact1 char(32) default NULL,
co_contact2 char(32) default NULL,
co_contact3 char(32) default NULL,
co_contact4 char(32) default NULL,
co_phone1 char(32) default NULL,
co_phone2 char(32) default NULL,
co_phone3 char(32) default NULL,
co_phone4 char(32) default NULL,
PRIMARY KEY (co_id),
UNIQUE KEY co_uid (co_uid)

-- Table structure for table `products`
prod_id int(11) NOT NULL auto_increment,
co_uid int(11) default NULL,
prod_name char(128) default NULL,
prod_type char(96) default NULL,
PRIMARY KEY (prod_id)

Current code:

$strSQL="SELECT * FROM companies ORDER BY co_name";
$result=mysql_query($strSQL) or die(mysql_error());
while ($row=mysql_fetch_array($result))

Need guidance on code for this segment



1:13 am on Dec 23, 2007 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

Welcome to WebmasterWorld, Snoegoer!

I'm not sure this can be as simple as you want it to be. I'm trying to think of how this could be done with one query but I do not think that is going to happen. Maybe it would be easier to write the XML file without the products added and then use SimpleXML [php.net] to add the products for each of the companies (assuming you have PHP5); you'd have to loop through the XML file and then query the products table grouping by company_id.


10:20 pm on Dec 23, 2007 (gmt 0)

5+ Year Member

Need guidance on code for this segment

Replace that with:

$strXML.= toXMLProducts($row["co_uid"]); 

And add this function:

function toXMLProducts($co_uid) { 
$strXML = '';

// Add products, i.e. everything except Brands
$result=mysql_query("SELECT * FROM products where co_uid = $co_uid and prod_type!= 'Brand' ORDER BY prod_name") or die(mysql_error());
while ($row=mysql_fetch_array($result)) {

// Do the same for brands
$result=mysql_query("SELECT * FROM products where co_uid = $co_uid and prod_type = 'Brand' ORDER BY prod_name") or die(mysql_error());
while ($row=mysql_fetch_array($result)) {

return $strXML;

Real C programmer's rule #55: When things get complicated, break it down into a function :-)


Featured Threads

Hot Threads This Week

Hot Threads This Month