php - left join only fetches one row

Question

This SQL only fetches one row from the table category when I'm using fetchAll(), I know there is atleast two rows with the same info. Any ideas?

Click here to see a more detailed explanation of what I thought the problem was

        $query = "        SELECT        
                                category.*,
                                GROUP_CONCAT('category_hierarchy.category_id' SEPARATOR ',') AS subcategories   

                FROM            category
                LEFT JOIN       category_hierarchy      ON      category.category_id = category_hierarchy.category_parent_id
                WHERE           category.type = '1'
                ORDER BY        category.sort_order ASC";


// Prepare.
    $stmt = $dbh->prepare($query);

// Execute.
    $stmt->execute();

// Fetch results.
    $categories = $stmt->fetchAll();

    $countedRows = count($categories);

    foreach($categories as $category) {
        $parent_arr = '';
        if(!empty($category['subcategories'])) {
            $parent_arr = array(display_children($category['subcategories']));
        } 

        $arr[] = array(
                'category_id'   => $category['category_id'],
                'title'         => $category['title'],
                'slug'          => $category['slug'],
                'url'           => $category['url'],
                'type'          => $category['type'],
                'sort_order'    => $category['sort_order'],
                'categories'    => $parent_arr
        );
    }

Answer 1

You have a aggregate function group_concat and without group by clause it will always return one row, you may need to add a group by at the end.

SELECT      
category.*,
GROUP_CONCAT('category_hierarchy.category_id' SEPARATOR ',') AS subcategories   
FROM            category
LEFT JOIN       category_hierarchy      ON      category.category_id = category_hierarchy.category_parent_id
WHERE           category.type = '1'
group by         category.category_id
ORDER BY        category.sort_order ASC