CodeIgniter GROUP_CONCAT and join

Question

I am trying to figure a way to join these two tables together, which I was able to do, but if it found more than one value that matched, it showed everything from the product table again. Now I am trying to use the MySQL group_concat together to be able to list all of tName in one field in the array but I keep getting an error with MySQL:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.`tCat' at line 2

SELECT sp_product.name, sp_product.price, sp_product.perm_name, sp_product.description, GROUP_CONCAT(product_type.tName SEPARATOR FROM (sp_product) LEFT OUTER JOIN sp_product_type ON sp_product_type.tCategory = sp_product.type WHERE perm_name = 'bacon'

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName'); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get();

Any ideas what I am doing wrong?

We should not see `$this->uri->segment(2)` in a model method. Submitted data should be received ONLY in the controller, then distributed to other layers as needed. — mickmackusa, Mar 26 '21 at 07:33

score 10 · Accepted Answer · answered Feb 17 '12 at 05:15

10

Seems problem with improper quotes.

It should be GROUP_CONCAT(product_type.tName SEPARATOR ",")

Try below :

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName'); 
    $this->db->from('product');
    $this->db->where('perm_name', $this->uri->segment(2));
    $this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
    $query = $this->db->get();

answered Feb 17 '12 at 05:15

1

And maybe should pass a `false` parameter to `select` - because of wrong protect algorithm... – uzsolt Feb 17 '12 at 10:09
I still get an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sp_product`) LEFT OUTER JOIN `sp_product_type` ON `sp_product_type`.`tCategory` ' at line 1 – Claremont Feb 17 '12 at 20:18
Once I did this I get only 1 row of data including other row's ids together. How can I get a separate row for each data with separate column? – Kusal Kithmal Dec 29 '19 at 09:04
We should not see `$this->uri->segment(2)` in a model method. Submitted data should be received ONLY in the controller, then distributed to other layers as needed. – mickmackusa Mar 26 '21 at 07:33

score 4 · Answer 2 · answered Nov 05 '13 at 06:23

4

You should use false as the second parameter in your select to skip escaping:

$this->db->select('GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName', false);

answered Nov 05 '13 at 06:23

Mehmed

2,880
4
41
62

score 1 · Answer 3 · answered Dec 18 '13 at 18:31

user319198 was off to a good start but his answer does not work. The working answer is as follows.

$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR) as tName'); 
    $this->db->from('product');
    $this->db->where('perm_name', $this->uri->segment(2));
    $this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
    $query = $this->db->get();

Notice I removed SEPARATOR "," and renamed the field in (as product_type.tName') to a field name that is not in the database. This has been tested and is working.

We should not see `$this->uri->segment(2)` in a model method. Submitted data should be received ONLY in the controller, then distributed to other layers as needed. — mickmackusa, Mar 26 '21 at 07:33

score 1 · Answer 4 · answered Dec 27 '13 at 05:04

1

no need to mention separator as comma(,) in group concat, as by default it will take comma(,) only in group concat

answered Dec 27 '13 at 05:04

kekin c chheda

11
1

score 0 · Answer 5 · edited Mar 26 '21 at 05:56

For #Claremont, #user319198, #FredTheLover, #Mosty Mostacho Please keep in mind while developing any project using CodeIgniter that to select multiple data or using compound statements in the select query of the Codeigniter you need to define the second parameter of the select query. Like

$this->db->select('(SELECT SUM(company.amount) FROM payments WHERE company.invoice_id=4') AS amount_paid', FALSE);

Because $this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

score 0 · Answer 6 · answered Nov 04 '14 at 17:57

0

I think it should be like this:

$this->db->select('product.name, product.price, product.perm_name, product.description')
   ->select(' GROUP_CONCAT(product_type.tName SEPARATOR ",") as product_type.tName', FALSE); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get();

answered Nov 04 '14 at 17:57

alisher.abdusattorov

21
3

We should not see `$this->uri->segment(2)` in a model method. Submitted data should be received ONLY in the controller, then distributed to other layers as needed. – mickmackusa Mar 26 '21 at 07:33

Reza Rouf · Answer 7 · 2018-08-08T05:51:24.640

0

try this ,assuming you use id to sort, use GROUP_BY instead of ORDER_BY and also use DISTINCT .. this will work

 $this->db->group_by("product.id","ASC");
$this->db->select('product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(DISTINCT product_type.tName SEPARATOR ",") as tName',FALSE); 
$this->db->from('product');
$this->db->where('perm_name', $this->uri->segment(2));
$this->db->join('product_type', 'product_type.tCategory = product.type', 'LEFT OUTER');
$query = $this->db->get();

edited Aug 08 '18 at 05:51

answered Aug 07 '18 at 13:42

Reza Rouf

11
3

You should always format your code, this is borderline unreadable – Michael Aug 07 '18 at 14:05
We should not see `$this->uri->segment(2)` in a model method. Submitted data should be received ONLY in the controller, then distributed to other layers as needed. – mickmackusa Mar 26 '21 at 07:32

Mosty Mostacho · Answer 8 · 2012-02-17T04:46:10.563

Take a look at the GROUP_CONCAT documentation:

Change this:

GROUP_CONCAT(product_type.tName SEPARATOR

To this:

GROUP_CONCAT(product_type.tName SEPARATOR ', ')

You're mixing the '

'product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName');

Try this:

"product.name, product.price, product.perm_name, product.description, GROUP_CONCAT(product_type.tName SEPARATOR ',') as product_type.tName");

CodeIgniter GROUP_CONCAT and join

8 Answers8

Linked