CodeIgniter Forums

Full Version: [Solved] GROUP CONCAT not work correct
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
On my categories project I am trying a new method for my categories list.

My GROUP_CONCAT not working for some reason my sub category name not showing correct but it is going to the correct page when I click on edit button just the name not printing out correct.

I have attached image of what it looks like at the moment. The links are correct and every thing else just name having issue

Currently what's showing


  1. Example
  2. Example
  3. Example
  4. PHP
  5. PHP

It should produce like this below

  1. Example
  2. Example > Some Example 1
  3. Example > Some Example 2
  4. PHP
  5. PHP > Functions

Model

PHP Code:
public function get_categories() {
    $language_id '1';

    $this->db->select('cp.category_id AS category_id, GROUP_CONCAT(cd1.name SEPARATOR "  >  ") as name');
    
    $this
->db->from('category_path cp''LEFT');
 
   
    
// Parent Cat
    $this->db->join('category c1''c1.category_id = cp.category_id''LEFT');

 
   // Sub Cat
    $this->db->join('category c2''c2.category_id = cp.path_id''LEFT');

 
   // Parent Cat Desc
    $this->db->join('category_description cd1''cd1.category_id = cp.path_id''LEFT');

 
   // Sub Cat Desc
    $this->db->join('category_description cd2''cd2.category_id = cp.category_id''LEFT');

    $this->db->group_by('cp.category_id');
    $this->db->order_by('name''ASC');
    $this->db->where('cd1.language_id', (int)$language_id);
    $this->db->where('cd2.language_id', (int)$language_id);
    $query $this->db->get();
    return $query->result_array();




Any thing I am doing wrong please can you share examples thanks in advance.
PHP Code:
$this->db->select("cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, c1.sort_order"); 

Have you looked at the SQL being generated by query builder? If I had to guess, I would consider it pretty likely that the select statement is getting mangled when it tries to escape this query. More than likely, you'll have to disable escaping in the select method by passing false as the second argument.

There is one other problem I can see right away, but it may not be causing any issues at all:

PHP Code:
$this->db->from('category_path cp''LEFT'); 

The from() method doesn't take a second argument.
Have you tried replacing   with a blank space?
PHP Code:
$this->db->select('cp.category_id AS category_id, GROUP_CONCAT(cd1.name SEPARATOR "  >  ") as name'); 
(09-24-2015, 12:33 AM)Martin7483 Wrote: [ -> ]Have you tried replacing   with a blank space?

PHP Code:
$this->db->select('cp.category_id AS category_id, GROUP_CONCAT(cd1.name SEPARATOR "  >  ") as name'); 

Does nothing still same result.
(09-23-2015, 08:41 AM)mwhitney Wrote: [ -> ]
PHP Code:
$this->db->select("cp.category_id AS category_id, GROUP_CONCAT(cd1.name ORDER BY cp.level SEPARATOR '  >  ') AS name, c1.parent_id, c1.sort_order"); 

Have you looked at the SQL being generated by query builder? If I had to guess, I would consider it pretty likely that the select statement is getting mangled when it tries to escape this query. More than likely, you'll have to disable escaping in the select method by passing false as the second argument.

There is one other problem I can see right away, but it may not be causing any issues at all:


PHP Code:
$this->db->from('category_path cp''LEFT'); 

The from() method doesn't take a second argument.

I have tried the suggestions but no luck I have also updated my Question
Maybe '  >  ' would have a better chance of working.

If not:
If you take the query generated by query builder (output the result of get_compiled_select() or use the profiler or a debugger) and execute it against your database (i.e. in MySQL Workbench), do you get the expected results? Have you tried using a separator which doesn't include any special characters?
(09-24-2015, 06:38 AM)mwhitney Wrote: [ -> ]Maybe '  >  ' would have a better chance of working.

If not:
If you take the query generated by query builder (output the result of get_compiled_select() or use the profiler or a debugger) and execute it against your database (i.e. in MySQL Workbench), do you get the expected results? Have you tried using a separator which doesn't include any special characters?

Hi @mwhitney

I have found the cause of the issue is on my add category function.

When ever I create a category if it is a sub category in my category_path table

If category id is lets say 22 and parent id 19

Then  on table row should have two rows

22 19
22 22

For some reason on my model add function it's not inserting the path id correct now I know what to do

I add image of working category list now.