how to get content from FIRST row of db table

[mpsn]

Hi, I am building function to convert the db table to an XML file. But I want to retrieve the root node value from the FIRST row in that db table before I build a for loop to traverse that db table.

 

Let's say I have this table NodeInfo:

node_Id    source  target

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

1              email    to

2              to          toFirstName

 

pseudocode for what I want:

1) store root (the source field of table NodeInfo

2)for each entry in table NodeInfo, do:

    build XML

  END FOR-EACH

 

Any help much appreciated!

 

[xyph]

I think you need to rework the way you're storing this in the database. You can look here for the optimum way to use MySQL to store and retrieve hierarchical data

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

 

Here's the answer to your question though.

SELECT `column` FROM `table` LIMIT 1

 

LIMIT 1 with no ORDER clause should return the first value in the database.

[mpsn]

Appreciate the help!

[mpsn]

On a related issue, each time I traverse a db table1, I want it to look at the corresponding table2 (where table2 linked to table1 via FK of course) so what would be the SELECT query?

 

let's say these table1, table2:

(for table1 )                                    (for table2)

tbl_1_ID    fieldT1A                        tbl_2_ID    tbl_1_ID(FK)    fiedlT2A

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

1                yes                              1                1                  hello

2                  no                                2                2                  world

3                yes                              3                3                  bye

 

so then in loop:

while still rows in table1, do:

if table1.fieldT1A is this, then:

      get corresponding table2.fieldT2A WHERE table1.tbl_1_ID=table2.tbl_1_ID?

 

BUT how do I get the CURRENT table1's tbl_1_ID PK?

 

Any help much appreciated!

 

 

 

[xyph]

So you want to get all your results of table1. If table1.fieldT1A = 'yes', you also want to grab the value table2.fieldT2A where the keys match?

[mpsn]

yes that is what I want.

[xyph]

The simplest way would probably be to join the data, and verify if it's a yes or no while you're looping through the results in PHP

 

http://phpweby.com/tutorials/mysql/32

[mpsn]

but even if I join the tables, how do I still refer to the CURRENT ROW in table1 to get the edge_id from table1 to get the table2.edge_id(FK), that is the problem I am having.

 

Can I just use: SELECT edge_id FROM edge LIMIT 1 to get edge_id from the current row I am at?

 

Any help much appreciated!

[xyph]

You are now contradicting yourself. The example I posted explains how to do exactly what I clarified in the thread previous to that.

If my example wasn't what you wanted, you shouldn't said it was.

 

On top of that, you seem to use table1 and edge as if they mean the same thing. They don't. Pick a naming convention and stick to it, or I'll have no idea what you're talking about.

 

Why don't you show the results you want in the same way you've presented your structure/data in Reply #3?

[mpsn]

Ok, let's say I have these db tables:

 

(edge)                                            (value)

edge_id    source  target            value_id    edge_id  value

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

1              email    to                    1              1              null

2              to        toFirstName    2              2            Bob

 

 

while($curEdgeRow=mysql_fetch_assoc() ) {

  if some condition true from some field in cur row in table edge, then:

  $query=mysql_query("SELECT edge_id FROM edge LIMIT 1");

  $getEdgeId=mysql_fetch_assoc($query);

  $getValQuery=mysql_query("SELECT val from value,edge WHERE value.edge_id='edge.$getEdgeId[edge_id]'");

  $getVal=mysql_fetch_assoc($getValQuery);

  print $getVal["val"];

}

 

I tried to do it like this but it doesn't work. So I want to always be able to retrieve the current row's edge_id and then get the value from the corresponding (via FK of course) table value.value, which I just print. So I just want that query "SELECT edge_id from edge LIMIT 1" to automatically refer to the current row in the while loop, if that is possible.

 

Sorry for confusion, I hope this clarifies what I want.

 

Any help much appreciated!

[mpsn]

Actually I think I got it. I will try first...

[mpsn]

No, it doesn't work, here is exact db tables I use (I should have posted this first!)

 

(edge)                                                    (value)           

edge_id  source  target    flag          value_id  edge_id(FK)  val

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

1            letter      to          Ref            1              1                  null

2            to          ...          Ref            2              2                  null

3            ...          ...          Val            3              3                  Bob

4            ...          ...          Val            4            4                  Smith

5            ...          ...          Ref            5            5                  null

6            ...          ...          Val            6            6                  John

7            ...          ...          Val            7            7                  normal

8            ...          ...          Val            8            8                  Doe

 

So when I run this script:

while($curEdgeRow=mysql_fetch_assoc($edgeQuery)) {

if($curEdgeRow["flag"]=="Val") {

$query=mysql_query("SELECT edge_id FROM edge LIMIT 1");//does this refer to next row in while loop?

$getEdgeId=mysql_fetch_assoc($query);

$getEdgeIdToUse=$getEdgeId["edge_id"];

$getValQuery=mysql_query("SELECT val from value,edge WHERE value.edge_id<'$getEdgeIdToUse' LIMIT 1") or die("ERROR!");

$getVal=mysql_fetch_assoc($getValQuery);

if($getVal["val"]==NULL)

print "null </br >";

else

"nay <br />";

}

}

 

It outputs to browser:

null

null

null

null

null

 

Any help much appreciated!

[mpsn]

Please, can someone help a fellow PHP programmer out???

[cypher86]

can you write a littel exemple of what you would like to obtain?

(table and output)

[mpsn]

Thanks for checking it out:

(edge)                                            (value)

edge_id    source  target            value_id    edge_id  value

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

1              email    to                    1              1              null

2              to        toFirstName    2              2            Bob

3              from    fromFirstName  3              3              Frank

4            closing  closingText        4            4              Yours Sincerely

 

I want to parse and insert an XML file to this db table and specifically I for a node with text (so tag nodes <toFirstName>Bob</toFirstName> or <closingText>Yours Sincerly</closingText>, I would like to traverse this whole table and if it is a parent node with text node child, then go to the corresponding table value to get the value (via FK) but I don't know how to always refer to the current table edge's edge_id in order to get the matching FK table value.value, I hope I am clear as to what I would like to make happen.

 

Any help much appreciated!

[cypher86]

probably i didn't understand the point 'cos it seems to be resolvable with a simple join like

 

 

select value from edge,value where edge.edge_id=value.edge_id

 

sorry....

[mpsn]

I just want to clarify: so let's say I use this:

while($curEdgeRow=mysql_fetch_assoc($edgeQuery)) {

  if cur row in table edge has text node child {

      $query=mysql_query("SELECT edge_id FROM edge LIMIT 1");//READ ME: will this always refer to current row as while loop does its traversing???

      $getEdgeId=mysql_fetch_assoc($query);

      $getEdgeIdToUse=$getEdgeId["edge_id"];

      $getValQuery=mysql_query(//USE JOIN table edge , value where value.edge_id=$getEdgeIdToUse");

      $getVal=mysql_fetch_assoc($getValQuery);

      if($getVal["val"]==NULL)

        print "null </br >";

      else

        "nay <br />";

  }

}

 

Please read the comment READ ME, b/c I still need to refer to the current row edge.edge_id, so that's the problem I am having, b/c if I know that, can't I just use:

"SELECT value from value,edge WHERE value.edge_id=$getEdgeIdToUse" where $getEdgeIdToUse refers to the current row edge.edge_id? I hope this makes sense.

 

Any help much appreciated!