Search code examples
mysqlgroup-concat

PHP MySQL Hierarchy Data group_concat

any help would be amazing. currently got the following MySQL query:

SELECT 
  GROUP_CONCAT( sp.`slug` SEPARATOR  '/' ) 
FROM  
  `category` sn,  `category` sp 
WHERE 
  sn.lft BETWEEN sp.`lft` AND sp.`rgt` 
  AND sn.`id` =3 
ORDER BY 
  sp.`lft` ASC;

Without the group_concat function this returns the desired results in the correct order. as soon as I apply group_contact the order is mashed up and incorrect. Does anyone know how to rewrite the query to give me the concatenated result desired and in the correct order?

Im at a loss on this. On a side note does anyone know how to rewrite it using the "inner join" statements as opposed to the two table names quoted next to each other as I dont understand how its joining the two tables.

Solution

  • Try this:

    SELECT 
  GROUP_CONCAT( sp.`slug` ORDER BY  sp.`lft` ASC SEPARATOR  '/' ) 
FROM  
  `category` sn,
INNER JOIN  `category` sp 
  ON sn.lft BETWEEN sp.`lft` AND sp.`rgt`
WHERE    
  sn.`id` =3