CODEWITHSUNDEEP
phpmysql
MultiDev
MultiDev

Reputation: 10649

MySQL: Select row and all related rows

I have a categories table setup like:

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 ID, I would like to query the database to select the category and all parents, in order of ancestors. Each category has a CatParent which is it's parent, or NULL if no parents exist.

So, for example, if I have a category ID of 4, I would like a query that returns:

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

Or if I have a category ID of 6:

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

Or a category ID of 1:

array('1');

How would I build this query?

Upvotes: 4

Views: 1586

Answers (2)

Quagaar
Quagaar

Reputation: 1287

You could use left join to get the parent categories, but this only makes sense if there is a limit. For unlimited category depth I would do it in PHP. Nevertheless, here is an example query:

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

If there was only one parent for category 4, the last ID (c3.id) would be NULL. You would have to consider that in your code.

Upvotes: 3

user2506641
user2506641

Reputation:

To achieve this, you can create a procedure. If using phpmyadmin, you can go to your database, to SQL and insert the following: 

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 //

The procedure creates a temporary table to store the data. The variable loopran, is just to make sure that, even if the category doesn't have a child, the parent will be returned as a result.

Next, retrieving the results:

$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 returns: 4 | 3, 3 | 1

$id = 6 returns: 6 | 2

$id = 1 returns: 1 |

$id = 9 returns nothing (if the row doesn't exist, of course.)

There is one huge problem. And that is, if you end up in a cycle that in the end points to a previous id in the cycle, it will result in an infinite loop. To fix that, you would have to exit the while loop, in the condition that it tries to add something that it already added. But I assume that this will never happen naturally. (depending on what you use it for, of course, and how the ParentID is set)

source & credit: @Meherzad - https://stackoverflow.com/a/16514403/2506641

Upvotes: 1

Related Questions