homepage Welcome to WebmasterWorld Guest from 54.196.206.80
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Extracting data from two tables and writing it out to XML
Not sure if Joining table accomplishes my end goal
Snoegoer




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

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:

<companies>
<company>
<co_name></co_name>
<co_uid></co_uid>
<co_add1></co_add1>
<co_add2></co_add2>
<co_add3></co_add3>
<co_city></co_city>
<co_state></co_state>
<co_zip></co_zip>
<co_country></co_country>
<co_phone1></co_phone1>
<co_phone2></co_phone2>
<co_phone3> </co_phone3>
<co_phone4> </co_phone4>
<co_contact1> </co_contact1>
<co_contact2></co_contact2>
<co_contact3></co_contact3>
<co_contact4></co_contact4>
<co_email></co_email>
<co_web></co_web>
<products></products>
<brands></brands>
</company>
</companies>

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)
) TYPE=MyISAM;

-- 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)
) TYPE=MyISAM;

Current code:

$strSQL="SELECT * FROM companies ORDER BY co_name";
$result=mysql_query($strSQL) or die(mysql_error());
$strXML="<companies>\n";
while ($row=mysql_fetch_array($result))
{
$strXML.="<company>\n";
$strXML.="\t<co_name>".$row["co_name"]."</co_name>\n";
$strXML.="\t<co_uid>".$row["co_uid"]."</co_uid>\n";
$strXML.="\t<co_add1>".$row["co_add1"]."</co_add1>\n";
$strXML.="\t<co_add2>".$row["co_add2"]."</co_add2>\n";
$strXML.="\t<co_add3>".$row["co_add3"]."</co_add3>\n";
$strXML.="\t<co_city>".$row["co_city"]."</co_city>\n";
$strXML.="\t<co_state>".$row["co_state"]."</co_state>\n";
$strXML.="\t<co_zip>".$row["co_zip"]."</co_zip>\n";
$strXML.="\t<co_country>".$row["co_country"]."</co_country>\n";
$strXML.="\t<co_phone1>".$row["co_phone1"]."</co_phone1>\n";
$strXML.="\t<co_phone2>".$row["co_phone2"]."</co_phone2>\n";
$strXML.="\t<co_phone3>".$row["co_phone3"]."</co_phone3>\n";
$strXML.="\t<co_phone4>".$row["co_phone4"]."</co_phone4>\n";
$strXML.="\t<co_contact1>".$row["co_contact1"]."</co_contact1>\n";
$strXML.="\t<co_contact2>".$row["co_contact2"]."</co_contact2>\n";
$strXML.="\t<co_contact3>".$row["co_contact3"]."</co_contact3>\n";
$strXML.="\t<co_contact4>".$row["co_contact4"]."</co_contact4>\n";
$strXML.="\t<co_email>".$row["co_email"]."</co_email>\n";
$strXML.="\t<co_web>".$row["co_web"]."</co_web>\n";

Need guidance on code for this segment

$strXML.="</company>\n";
}
$strXML.="</companies>\n";

 

eelixduppy




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

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.

borntobeweb




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

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());
$strXML.="<products>\n";
while ($row=mysql_fetch_array($result)) {
$strXML.="<product>\n";
...
$strXML.="</product>\n";
}
mysql_free_result($result);
$strXML.="</products>\n";

// 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());
$strXML.="<brands>\n";
while ($row=mysql_fetch_array($result)) {
$strXML.="<brand>\n";
...
$strXML.="</brand>\n";
}
mysql_free_result($result);
$strXML.="</brands>\n";

return $strXML;
}

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved