Adding unwanted character to comma in mysql - codeigniter

Question

This is the codeigniter controller statement to get the data from mysql

$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names")
                           ->where('Service_boid', $boId)
                           ->get('service');

Here the problem is, a syntax error in mysql because while echoed the query:

SELECT GROUP_CONCAT(Service_name SEPARATOR ', `'` ) AS service_names FROM (`service_info`) WHERE `Service_boid` = '4'

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 ') AS service_names FROM (service_info) WHEREService_boid` = '4' at line 1

An unwanted character is appending to the separator as shown above. Other than direct query execution (sql query is executing through mysqli_query()) any solution to solve this?

Note : I want ', ' as the separator.

Manwal · Accepted Answer · 2015-04-21 08:50:45Z

2

Remove space from code:

"GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names")
                                       ^

Use like this:

"GROUP_CONCAT(Service_name SEPARATOR ',' ) AS service_names")

If You want to use ', ' as the delimiter

Then use false as second parameter.

$this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names", false)

$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.

edited Apr 21, 2015 at 8:50

answered Apr 21, 2015 at 7:19

Manwal

23.9k15 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanjay Kumar N S Over a year ago

No I want that as the delimiter which I have already added as a note.

Nil'z · Accepted Answer · 2015-04-21 07:30:43Z

1

Try this:

$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names", false)
->where('Service_boid', $boId)
->get('service');

Please note that adding a second parameter to the select() will not try to escape your select statement, and comma(',') is the default separator.

edited Apr 21, 2015 at 7:30

answered Apr 21, 2015 at 7:25

Nil'z

7,4831 gold badge20 silver badges28 bronze badges

Comments

Shaiful Islam · Accepted Answer · 2015-04-21 07:41:59Z

1

To avoid protecting your field query by CI you need to use false as second parameter to the select function. See the doc $this->db->select();

SO your query will be like this

$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names",false)
->where('Service_boid', $boId)
->get('service');

answered Apr 21, 2015 at 7:41

Shaiful Islam

7,12612 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

Adding unwanted character to comma in mysql - codeigniter

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related