获取特定产品的所有父类别

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.

Answer 1

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