July 24, 2008 02:19pm pdt

1. hielo 598,005
2. angelIII 266,210
3. hernst42 211,146
4. logudotcom 162,123
5. routinet 134,597
6. Ray_Pas... 134,271
7. Roonaan 121,118
8. nplib 101,278
9. RQuadling 97,246
10. glcummins 86,770
11. yodercm 85,054
12. dr_dedo 79,410
13. rowejd 78,843
14. Xyptilon2 76,292
15. bportlock 75,982

1. angelIII 851,377
2. hernst42 767,019
3. ldbkutty 688,003
4. Roonaan 649,058
5. hielo 614,930
6. wasifg 504,829
7. RQuadling 479,859
8. snoyes_jw 401,445
9. glcummins 358,083
10. gamebits 307,148
11. steelseth12 302,929
12. i_m_aa... 296,907
13. yodercm 277,290
14. TeRReF 263,550
15. Diablo84 239,472

02.29.2008 at 06:17AM PST, ID: 23203713 | Points: 500

	[x] Attachment Details

MySQL to XML - Outputting XML-nodes based on "categories"

Zones: PHP and Databases, Extensible Markup Language (XML), MySQL

Tags: FIREFOX, IE, SAFARI, NETSCAPE, OPERA

I have a PHP script that extracts a whole "MySQL table" into a "XML file". It's very simple.

The script loops the rows within the MySQL table. And the output is: (see "CODESNIPPET 1:").

The actual code is: (see "CODESNIPPET 2:").

My question is:

On each row in my table, I want the option to assign a Category. That means that there is a FIELD that has a Category_ID or something, that will help the XML output to be more specific. Hence it will look moore like this: (CODESNIPPET: 3)

How can I best possible accomplish this, when thinking of architecture and logic? And what would be the best approach, creating an unique table for the category ID's? Any example or code would really be appreciated.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:

           
           
             <CODESNIPPET 1:>
 
<?xml version="1.0"?>
<root>
  <tablename>
    <dbCOLUMN1>George</dbCOLUMN1>
    <dbCOLUMN2>Jenkins</dbCOLUMN2>
    <dbCOLUMN3>Funky Chicken</dbCOLUMN3>
  </tablename>
  <tablename>
    <dbCOLUMN1>Maria</dbCOLUMN1>
    <dbCOLUMN2>Romana</dbCOLUMN2>
    <dbCOLUMN3>Mahatma Jira</dbCOLUMN3>
  </tablename>
</root>
 
</CODESNIPPET: 1>
---
<CODESNIPPET: 2>
 
	$table_id = 'TABLENAME';
	$query = "SELECT * FROM $table_id";
	$dbresult = mysql_query($query, $con);
 
	// creates new XML document
	$doc = new DomDocument('1.0');
 
	// creates root node
	$root = $doc->createElement('root');
	$root = $doc->appendChild($root);
 
	// process one row at a time
	while($row = mysql_fetch_assoc($dbresult)) {
 
	// adds node for each row
	$occ = $doc->createElement($table_id);
	$occ = $root->appendChild($occ);
 
	// adds child node for each field in each row
	foreach ($row as $fieldname => $fieldvalue) {
 
	$child = $doc->createElement($fieldname);
	$child = $occ->appendChild($child);
 
	$value = $doc->createTextNode($fieldvalue);
	$value = $child->appendChild($value);
 
	} // foreach
	} // while
 
	// gets full XML document from what above
	$xml_string = $doc->saveXML();
	$file_handle = fopen('THEFILE.xml','a');
	fwrite($file_handle,$xml_string);
	fclose($file_handle);
 
</CODESNIPPET: 2>
---
<CODESNIPPET: 3>
 
<?xml version="1.0"?>
<root>
  <tablename>
    <category male>
        <young>
            <dbCOLUMN1>George</dbCOLUMN1>
            <dbCOLUMN2>Jenkins</dbCOLUMN2>
            <dbCOLUMN3>Funky Chicken</dbCOLUMN3>
        </young>
    </category male>
  </tablename>
  <tablename>
    <category female>
            <dbCOLUMN1>Maria</dbCOLUMN1>
            <dbCOLUMN2>Romana</dbCOLUMN2>
            <dbCOLUMN3>Mahatma Jira</dbCOLUMN3>
    </category female>
  </tablename>
</root>
 
</CODESNIPPET: 3>
           
         

Start your free trial to view this solution

Question Stats

Zone: Web Development

Question Asked By: gladideg

Question Asked On: 02.29.2008

Participating Experts: 1

Points: 500

Translate:

Loading Advertisement...

20080236-EE-VQP-29 / EE_QW_2_20070628