MySQL: select a row and all related rows.

Question

I have a table setup categories, for example:

ID    CatName      CatParent
1     Websites     NULL
2     Recipes      NULL
3     Programming  1
4     Helpful      3
5     Useless      3
6     Desserts     2

If I have a category identifier, I would like to query the database to select the category and all parents in ancestral order. Each category has CatParent, which is the parent, or NULLif the parent does not exist.

So, for example, if I have a category 4 identifier, I would like the request to return:

array('4','3','1'); // Helpful > Programming > Websites

Or if I have a category 6 identifier:

array('6','2'); // Desserts > Recipes

Or category 1 identifier:

array('1');

How do I build this query?

Answer 1

left join , , . PHP. , :

select c1.id, c2.id, c3.id
from categories c1 
left join categories c2 on c2.id = c1.catparent
left join categories c3 on c3.id = c2.catparent
where c1.id = 4

4 , (c3.id) NULL. .

Answer 2

. phpmyadmin, , SQL :

DELIMITER //
CREATE PROCEDURE get_parents(IN cid int)
BEGIN
    DECLARE child_id int DEFAULT 0;
    DECLARE prev_id int DEFAULT cid;
    DECLARE loopran int DEFAULT 0; 

    SELECT CatParent into child_id 
    FROM categories WHERE ID=cid ;

    create TEMPORARY  table IF NOT EXISTS temp_table as (select * from categories where 1=0);
    truncate table temp_table;

    WHILE child_id <> 0 OR loopran <> 1 DO
        SET loopran = 1;

        insert into temp_table select * from categories WHERE ID=prev_id;
        SET prev_id = child_id;
        SET child_id=0;
        SELECT CatParent into child_id
        FROM categories WHERE ID=prev_id;
    END WHILE;

    select * from temp_table;
END //

. loopran , , , .

, :

$id = 5;

$result = "
CALL get_parents($id)
"; // Call the procedure just like as if it were a php function

$query = mysqli_query($conn, $result) or die(mysqli_error($conn));

$x = 0;

while ($row = mysqli_fetch_array($query)) {
    if ($x > 0) echo ", ";
    echo $row['ID'] . " | " . $row['CatParent'];
    $x++;
}

$id = 4 : 4 | 3, 3 | 1

$id = 6 : 6 | 2

$id = 1 : 1 |

$id = 9 ( , .)

. , , id , . , while, , -, . , . ( , , , ParentID)

: @Meherzad - fooobar.com/questions/57384/...