Get all parent categories from specific product

Question

I cant figure out the right query and all the existing answers are related to Wordpress and I'm not on wordpress, I'm building it from scratch. I've got this table

I want to get all parent ID's. If product is in category "Yellow", i want to get all the parent IDs from Yellow category, so from query "Yellow" would be 70, 68, 66, 0.

So Im spinning around with all possible joins and best result is just current category (or all of them..)

     $sql2 = "SELECT c1.ID, c1.si, c2.si, c2.ID as parent_id 
              FROM v_menu c1 
              LEFT JOIN v_menu c2 
              on (c2.parent = c1.ID) 
              WHERE (c1.ID = :c1)";
      $st2 = $this->db->prepare($sql2);
      $st2->execute(array(':c1' => $row['MID'])); // $row['MID'] => product category
      $res2 = $st2->fetchAll();

This returns only category which matches product category.

Any hint, link, solution... would be in much help.

try this : http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — RJParikh, Mar 24 '17 at 10:17
Thank You! Found answer in this post -> http://stackoverflow.com/a/24901882/2269385 — Blue, Mar 24 '17 at 10:52
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) — Fabiano, Mar 24 '17 at 10:59

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

This is the correct query if your parent ID in smaller than Child ID, which usually is.

SELECT t.ID, t.si, @pv := t.parent parent
          from (select * from v_menu order by ID desc) t
          join (select @pv := 70) tmp
          where t.ID = @pv

Reference: https://stackoverflow.com/a/24901882/2269385

edited May 23 '17 at 12:32

Community

1
1

answered Mar 24 '17 at 10:54

Blue

261
1
3
14

Get all parent categories from specific product

1 Answers1