sql to xml - a parse with php

chris (2003-12-12 18:40:56)
3345 views
0 replies
Although this isn't the finest example, this code is supposed to illustrate how using a LEFT JOIN in SQL lends the returne results set to a simple once-only parse to generate a representative XML document..

first of all, the query used here and the results are as follows:
mysql> select gr_id, gr_name, gl_directoryentry from groups left join groups_link on gr_id = gl_group where gr_userid = 2 order by gr_name;
+-------+-----------------------+-------------------+
| gr_id | gr_name               | gl_directoryentry |
+-------+-----------------------+-------------------+
|    18 | another group (group) |                54 |
|    18 | another group (group) |                55 |
|    18 | another group (group) |                71 |
|    18 | another group (group) |                34 |
|    18 | another group (group) |                32 |
|    18 | another group (group) |                65 |
|    22 | my group (group)      |                67 |
|    22 | my group (group)      |                64 |
|    22 | my group (group)      |               121 |
|    22 | my group (group)      |                17 |
|    22 | my group (group)      |                54 |
+-------+-----------------------+-------------------+
11 rows in set (0.00 sec)

The aim of the game is to generate an XML document which represents these results an looks something like this:
<result status="ok">
  <groups>
    <group id="18" name="another group (group)" default="0">
      <addresses>
        <address id="55"/>
        <address id="54"/>
        <address id="34"/>
        <address id="71"/>
        <address id="32"/>
        <address id="65"/>
      </addresses>
    </group>
    <group id="22" name="my group (group)" default="0">
      <addresses>
        <address id="67"/>
        <address id="64"/>
        <address id="121"/>
        <address id="54"/>
        <address id="17"/>
      </addresses>
      </group>
    </groups>
</result>

It's simple to do this in a SINGLE parse of the sql results data, but I'm in a hurry so I'm just gonna paste it in.. here goes:
<?php

        if($asth=mysql_db_query($db_name, "select gr_id, gr_name, gl_directoryentry , gr_default
                                                from groups left join groups_link on gr_id = gl_group
                                                where gr_userid=".$userdata[us_id]." order by gr_name"){


                $reply = "<result status="ok">";
                if ( mysql_num_rows($asth) > 0 ) {
                        $reply.="<groups>";
                        $currentGroup = -1;
                        $loopCount = 0;
                        while($row = mysql_fetch_assoc($asth)){
                                if($currentGroup!=$row['gr_id']){
                                        if($loopCount>0){
                                                // only terminate a group if we have just built one
                                                $reply.="</addresses></group>";
                                        }
                                        // start a new group
                                        $reply.="<group id="$row[gr_id]" name="$row[gr_name]" default="$row[gr_default]"><addresses>";
                                }
                                $reply.="<address id="$row[gl_directoryentry]"/>";
                                if($currentGroup!=$row['gr_id']){

                                        $currentGroup = $row['gr_id'];
                                }
                                $loopCount ++;
                        }
                        $reply.="</addresses></group></groups></result>";
                }

        }else{
                $reply = "<result status="ERROR: ".mysql_error()."" query="".$query.""/>";
        }

        echo "$reply";
        include $toolkit_includedir."/xml_tail.php";
?>

cheers all,

christo
comment