PHP QUERY Loop?

Question

Basically I have a genealogy table where everybody has a child_id for themselves and a parent_id (both of which are fields in my table). My goal is to add + 1 to my vote field to each parent in a lineage.

So, I need to SELECT the parent_id from a row and update the vote. Then I need to select the parent from that row and update that parents vote. Then i need to select the parent from THAT row and do the same thing..over and over until I get to the very first ancestor and there are no more parents or children to update. Here are my select and update queries:

SELECT parent_id FROM profiles where child_id = $first_ancestor
UPDATE profiles SET votes = votes + 1 where child_id = parent_id

Do I use a loop? Or how do I do this? Thanks!

For dealing with trees of data, you may want to look into storing that data in a nested set. That makes things like this a lot easier. — deceze
– deceze ♦, Commented Jul 2, 2011 at 4:05

André Laszlo · Accepted Answer · 2011-07-02 04:15:14Z

1

I'd use a loop. To my knowledge, that's a limitation to relational databases. Some DBMS have better support for recursive queries than others though.

If you're up for it, you might want to check out something called graph databases. Some of them has much better support for these kinds of operations than most relational databases have.

Check out:

CloudGraph has an interesting "graph query language"
Neo4j uses something they call "traversals", looks neat
InfoGrid
GraphBase

edited Jul 2, 2011 at 4:15

answered Jul 2, 2011 at 4:09

André Laszlo

15.6k3 gold badges68 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP QUERY Loop?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related