how do i compare with with current row's next row

[mpsn]

Hi, I am trying to write an SQL to XML converter, so so far I have all the records inputted into the db tables, and now I want to append a child, but I don't know how to compare current row's field value with the next row below it, specifically I want to check if a current row is parent of the next row. I have already got the XML to SQL function working (some bugs but for the most part it's working)

 

So for eg:

<?php

$query=mysql_query("SELECT * FROM edge");

while($row=mysql_fetch_assoc($query) ) {

    if($row['target'] is parent of the next row),  then

                append the next row as child of current row

}

 

?>

 

Table edge is one of the tables that hold target node/source(aka: parent) nodes.

 

Any help much appreciated!

[mpsn]

I think I will try:

 

$nextRow=mysql_query("SELECT source FROM edge WHERE edge_id>$currentEdge_id LIMIT 1");

 

but how do I get the current table edge.edge_id to store in $currentEdge_id for this comparison. I know I'll have to use mysql_fetch_assoc to retrieve the actual id BUT how do I even use the SELECT query to ask for the current edge_id I mean.

 

Any help much appreciated!

[mpsn]

to be more specific, here is the partial code:

 

<?php
//CHECK/DEAL w/ set up for current node's source/parent 
$currentIdQuery=mysql_query("SELECT ");//HOW DO I GET current row's edge_id??
$nextRowQuery=mysql_query("SELECT source FROM edge WHERE edge_id>$currentIDQuery LIMIT 1");
$nextRow=mysql_fetch_assoc($nextRowQuery);
if($row['target']==$nextRow['source']) {
        //append current row's next row as child node of current row 
}

?>

 

Any help much appreciated!

[trq]

Post your table structure and some sample data. There is probably a much easier / more efficient method.

[mpsn]

Ok, let's use this simple email.xml:

<?xml version="1.0" encoding="ISO-8859-1" ?>

<email>

<message>

  <to>

  <toFirstName>Bob</toFirstName>

  </to>

</message>

</email>

 

For table edge:

============

edge_id  source            target              flag

----------  --------            -------              ------

  1          email              message          Ref

  2          message        to                    Ref

  3          to                  toFirstName    Ref

  4          toFirstName  Bob                  Val

 

flag refers to the current node's child ("Ref" for if child node is an element and "Val" if current node's child is a text node)

 

I hope this is more clear. Thanks.