1

I have a table (eg the name is "page"), where each row is a child of another row, like below (see the HTML section):

http://jsfiddle.net/Fuqrb/

I want to find the topmost parent's id, by using a child's id. For example, we get a URL variable as the id of the bottommost child:

http://www.website.com/page.php?p=6

Then we store the URL variable into a variable:

$p = $_GET['p'];

To get the topmost parent's id, I have to use query function several times. For example:

if($result = $mysqli->query("SELECT * FROM page WHERE id='$p' LIMIT 1"))
{
    while($row = $result->fetch_array())
    {
        $parent = $row["parent"];
    }
}

if($result = $mysqli->query("SELECT * FROM page WHERE id='$parent' LIMIT 1"))
{
    while($row = $result->fetch_array())
    {
        $parent1 = $row["parent"];
    }
}

if($result = $mysqli->query("SELECT * FROM page WHERE id='$parent1' LIMIT 1"))
{
    while($row = $result->fetch_array())
    {
        $parent2 = $row["parent"];
    }
}

if($result = $mysqli->query("SELECT * FROM page WHERE id='$parent2' LIMIT 1"))
{
    while($row = $result->fetch_array())
    {
        $id = $row["id"];
    }
}

This way is ineffective, because if I got another URL variable as the id of another child of the higher/ lower level, this will not work.

How to get the topmost parent's id dynamically, regardless of the child's id?

Bayu
  • 437
  • 2
  • 10
  • 19
  • Use a proper [nested set](http://en.wikipedia.org/wiki/MPTT) if you aren't already. – deceze Sep 10 '12 at 07:22
  • The problem is I have uploaded too many data into the table already. Also I have coded all pages to get the data based on the parent's id – Bayu Sep 10 '12 at 07:27
  • 1
    Well, read this: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – deceze Sep 10 '12 at 07:28
  • Thanks, I've read that, but I don't understand how to implement his method to get the topmost parent's id – Bayu Sep 10 '12 at 09:11
  • Well, the main point is that it's really difficult to implement this using regular adjacent lists, but would be trivial using a nested set. See http://stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql for how to make it a nested set after the fact. – deceze Sep 10 '12 at 09:39

1 Answers1

0
DELIMITER $$

CREATE FUNCTION `fnGetRootParent`(
    page INT
) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE _parent INT;
    DECLARE _result INT;

    SET _parent = page;

    my_loop: LOOP
        SELECT 
             p.id
            ,p.parent
        INTO 
             _result
            ,_parent
        FROM page p
        WHERE id = _parent;

        IF (_parent = 0) OR (_parent = _result) THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _result;
END

SELECT fnGetRootParent(213);

Sherlock
  • 7,525
  • 6
  • 38
  • 79
  • Sorry, I only know basic MySQL. How to implement above SQL command in PHP? About the "SELECT fnGetRootParent(213)" line, the 213 is the given URL variable p, right? – Bayu Sep 10 '12 at 07:43
  • 213 is the parameter. You can execute the first piece of SQL in PHPMyAdmin. You only have to do that once, then it's registered as a function and is callable as in my example. – Sherlock Sep 10 '12 at 07:59
  • It returns error message: "Unable to save result set" and "Table 'my_database.p' doesn't exist". Why did this happen? The table name is "page", and the URL is "website.com/page.php?p=6" – Bayu Sep 10 '12 at 08:35
  • Ok, I ran the first piece with PHPMyAdmin, then the second with PHP file (page.php). The web browser kept showing loading indicator (no result nor error message), and my CPU temperature increased to 80 C, and keep increasing until I shut down the WAMP server. Why is that so? – Bayu Sep 10 '12 at 09:09
  • Probably because you hit an infinite loop. What's the value in the 'parent' column of the 'top parents'? – Sherlock Sep 10 '12 at 09:18
  • Nevermind, I noticed it's 0. It should actually be NULL instead of 0, but if you don't wanna change the content of the table: try running my updated function. If you get an error saying the function already exists, make sure you call DROP FUNCTION fnGetRootParent; first. – Sherlock Sep 10 '12 at 09:23
  • Yes the "parent" column of the top parents is 0. I dropped the previous function, then ran your updated function, yet I got the infinite loop again.... – Bayu Sep 10 '12 at 09:36
  • Are you positive that id you enter is valid? Try calling it with a top parent as input. – Sherlock Sep 10 '12 at 10:14
  • Shit, I swapped 2 variables. I changed the query and tested it, it works now. – Sherlock Sep 10 '12 at 10:29
  • I got these errors: Notice: Undefined index: id in C:\wamp\www\my_website\en\page.php. Notice: Undefined index: title in C:\wamp\www\my_website\en\page.php. Notice: Undefined index: parent in C:\wamp\www\my_website\en\page.php. I declared empty variables for these fields on the topmost of my code already. I tried with the top parent also – Bayu Sep 11 '12 at 08:14
  • Try SELECT fnGetRootParent(120); in PHPMyAdmin, you'll see that it works now. The errors you're getting now are unrelated. You're addressing array keys that are not present. – Sherlock Sep 11 '12 at 08:50
  • Can I use the select command in mysqli query from a PHP page? Eg if($result = $mysqli->query("SELECT fnGetRootParent(120)")). When I tried it, I got the errors "undefined index" – Bayu Sep 11 '12 at 10:14
  • If I use PHPMyAdmin to run the command, I get the result. But with PHP file, I got error messages "undefined index" – Bayu Sep 11 '12 at 10:21
  • Use this: $mysqli->query("SELECT * FROM page WHERE id=fnGetRootParent(130) LIMIT 1"); – Sherlock Sep 11 '12 at 10:35