mediadonkey
asked on
Building XML with PHP and MySQL
I'm in an utter mind-melt and having started covering the same ground of problems I'm hoping you guys (from whom I've already learnt a lot) can help me.
I'm trying to get a PHP to build a section of XML from a MySQL table.
The end-result should look something like this:-
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Code:
<Monkey_Muffin>
<R0>
<C0>
<section width="200" height="75" lineThickness="0" lineColour="0x333333"/>
<text>
<txt1 />
<txt2 text="Monkeys" y="2" font="Myriad Pro" fontSize="12" fontColour="0xFFFFFF" align="left" />
<txt3 text="I like them" y="15" />
</text>
<image>
</image>
</C0>
<C1>
<section width="75" height="75" lineThickness="0" lineColour="0x333333" lineAlpha="100" />
<text>
</text>
<image src="Monkey_Muffin.jpg" fit="false" width="70" height="70" x="2.5" y="2.5" alpha="100" />
</C1>
</R0>
</Monkey_Muffin>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
The table in the MySQL DB contains:-
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Code:
Field Type Null Default
name varchar(20) Yes
id int(11) Yes
row int(11) Yes
section int(11) Yes
width int(255) Yes
height int(255) Yes
lineThickness int(255) Yes
lineColour varchar(255) Yes
lineAlpha int(255) Yes
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
and a few other bits and pieces...
Baring in mind that there could be multiple rows and multiple sections/columns within rows
What I'm trying to get the PHP to do is start
* ID1 - print the name
* In ID 1 - where row 1 (in XML terms here, not SQL), print all buttons rows (in SQL terms here)
* then move onto row 2 until no further rows
* then repeat process for next ID
In the format outlined above. I have got this working for simpler examples, and I'm thus trying to use code from those... but it's much more complicated. I'm currently trying:-
Code:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
<?php
        $dbh = mysql_connect("xxx", "xxx", "xxx");
        mysql_select_db("xxx");
        $query = "SELECT * FROM table WHERE id = 1"; // get cycle up on this
        $result = mysql_query($query, $dbh);
        $fields = mysql_num_fields($result);
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
while ($row=mysql_fetch_array($r esult)) {
for ($i=0; $i<$fields; $i++) {
$name=mysql_field_name($re sult, $i);
if ($row['id']= 1) { // get this number to come from earlier variable
if ($row['row']= 1) { // get this number to cycle upwards until done
if ($row['section']= 1) {
echo "<$name=\"$row[$name]\" />\n"; }
else { echo "can put a stop on this for end of row"; }
}
else { echo "row not 0"; }
}
else {
echo "not showing";
}
}
echo "/>\n<";
}
?>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---
Admittedly with hard-coded ID etc. atm just to see if I can get it working. I can get bits working with bits of info, but I think I'm on completely the wrong road.
Any directional help would be really great.
I'm trying to get a PHP to build a section of XML from a MySQL table.
The end-result should look something like this:-
--------------------------
Code:
<Monkey_Muffin>
<R0>
<C0>
<section width="200" height="75" lineThickness="0" lineColour="0x333333"/>
<text>
<txt1 />
<txt2 text="Monkeys" y="2" font="Myriad Pro" fontSize="12" fontColour="0xFFFFFF" align="left" />
<txt3 text="I like them" y="15" />
</text>
<image>
</image>
</C0>
<C1>
<section width="75" height="75" lineThickness="0" lineColour="0x333333" lineAlpha="100" />
<text>
</text>
<image src="Monkey_Muffin.jpg" fit="false" width="70" height="70" x="2.5" y="2.5" alpha="100" />
</C1>
</R0>
</Monkey_Muffin>
--------------------------
The table in the MySQL DB contains:-
--------------------------
Code:
Field Type Null Default
name varchar(20) Yes
id int(11) Yes
row int(11) Yes
section int(11) Yes
width int(255) Yes
height int(255) Yes
lineThickness int(255) Yes
lineColour varchar(255) Yes
lineAlpha int(255) Yes
--------------------------
and a few other bits and pieces...
Baring in mind that there could be multiple rows and multiple sections/columns within rows
What I'm trying to get the PHP to do is start
* ID1 - print the name
* In ID 1 - where row 1 (in XML terms here, not SQL), print all buttons rows (in SQL terms here)
* then move onto row 2 until no further rows
* then repeat process for next ID
In the format outlined above. I have got this working for simpler examples, and I'm thus trying to use code from those... but it's much more complicated. I'm currently trying:-
Code:
--------------------------
<?php
        $dbh = mysql_connect("xxx", "xxx", "xxx");
        mysql_select_db("xxx");
        $query = "SELECT * FROM table WHERE id = 1"; // get cycle up on this
        $result = mysql_query($query, $dbh);
        $fields = mysql_num_fields($result);
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
while ($row=mysql_fetch_array($r
for ($i=0; $i<$fields; $i++) {
$name=mysql_field_name($re
if ($row['id']= 1) { // get this number to come from earlier variable
if ($row['row']= 1) { // get this number to cycle upwards until done
if ($row['section']= 1) {
echo "<$name=\"$row[$name]\" />\n"; }
else { echo "can put a stop on this for end of row"; }
}
else { echo "row not 0"; }
}
else {
echo "not showing";
}
}
echo "/>\n<";
}
?>
--------------------------
Admittedly with hard-coded ID etc. atm just to see if I can get it working. I can get bits working with bits of info, but I think I'm on completely the wrong road.
Any directional help would be really great.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fixed another way, but this will equally work.
A good example of this would be this mysql command line;
./mysql -ujon test --xml -e 'SELECT * FROM t1' > t1.xml
What this will do is take the result of the SELECT query and route the results to a file called t1.xml
Hope this offered some help. :)
~A~