Using variables when getting all parent nodes recursively

I try getting all parent categories for each category in select using the following query:

select @c := c.id_category,c.id_parent,
(SELECT
GROUP_CONCAT( @r := (
SELECT c1.id_parent
FROM ps_category c1
WHERE c1.id_category = @r
)) AS parent

FROM (
SELECT @r := @c
) vars,
ps_category c2
where @r<>0) p
from ps_category c
join (select @c:=0) tmp

I expect to get something like this:`
id_category_id; id_parent; p
1; 0; 0
2; 1; 1,0
3; 1; 1,0
4; 2; 2,1,0
...`
but it seems that something not working with initialisation of `@r` with `@c` , what I'm doing wrong?

SELECT
@c:=c.id_category,
c.id_parent,
(SELECT
GROUP_CONCAT(@r:=(SELECT
c1.id_parent
FROM
ps_category c1
WHERE
c1.id_category = @r)) AS parent
FROM
(SELECT @r:=@c) vars,(select @c:=0)Z, <!------- i had made changes at this line>
ps_category c2
WHERE
@r <> 0) p
FROM
ps_category c


Try above query.

As mention in comment you are initialize variable `@c` at outer query ,and you are using that variable in inner query.So i had make that initialization to inner query.So automatically updated value will populate to outer query.

Hope this will help you.

I got below output
[![enter image description here][1]][1]
Whats your expected output?

[1]:

I've sorted out this issue

SELECT
@r:=c.id_category id_category,
c.id_parent,
(select GROUP_CONCAT(@r:=(SELECT
c1.id_parent
FROM
ps_category c1
WHERE
c1.id_category = @r)) AS p
from ps_category c2
) as parents

FROM
ps_category c,
(select @r:=0) Z

group by c.id_category, c.id_parent