MySQL: Select row and all related rows

Question

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?

Is there a maximum category depth? If not, you can't do it with only one query. — Quagaar, Dec 10 '16 at 21:07
@Quagaar No, no maximum category depth, unless perhaps it is defined as something unusually high... say, 20. — MultiDev, Dec 10 '16 at 21:09
this can be done without having the numbers defined... I'm testing my hypothesis, will be back later today. — , Dec 10 '16 at 21:19
To do these kinds of queries you'll need to store more of the hierarchy, e.g. by using MPTT (https://www.sitepoint.com/hierarchical-data-database-2/), but I've found it much easier to store the materialized path to the node, e.g. mpath for `Helpful` would be `1/3/4` (a concatenation of the primary keys). — thebjorn, Dec 10 '16 at 21:24
Possible duplicate of [How to do the Recursive SELECT query in MySQL?](http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql) — Reeno, Dec 10 '16 at 23:06
what you're looking for is a hierarchical recursive query , which currently isn't supported in MySQL. but you can find an alternative solution here http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Avindu Hewa, Dec 10 '16 at 21:25
@JROB did you resolve your issue, or do you still require help? — , Dec 16 '16 at 23:19

Quagaar · Accepted Answer · 2016-12-11T18:50:43.940

3

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.

edited Dec 11 '16 at 18:50

answered Dec 10 '16 at 21:16

Quagaar

1,257
12
21

Thank you for posting that, but I can't ever get this to return any results. – MultiDev Dec 11 '16 at 18:22
There was a typo in the table names of the joins (catgories instead of categories). But apart from that the query works. – Quagaar Dec 11 '16 at 18:51

score 1 · Answer 2 · edited May 23 '17 at 10:29

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

MySQL: Select row and all related rows

2 Answers2