Adding unwanted character to comma in mysql - codeigniter
This is a codeigniter controller statement to get 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 the echo request is:
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 matches your MySQL server version for the correct syntax for using near '
) AS service_names FROM (
service_info) WHERE
Service_boid` =' 4 'on line 1
The unwanted character is appended to the delimiter as shown above. Apart from executing the query directly (sql query is done via mysqli_query ()), is there any solution to solve this problem?
Note. I want "," as a separator.
source to share
Try the following:
$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names", false)
->where('Service_boid', $boId)
->get('service');
Note that adding a second parameter to select()
will not try to escape your select statement and the comma (',') is the default delimiter.
source to share
Remove place from code:
"GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names")
^
Use this:
"GROUP_CONCAT(Service_name SEPARATOR ',' ) AS service_names")
If you want to use ',' as separator
Then use false
as second parameter.
$this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names", false)
$ this-> db-> select () takes an optional second parameter. If you set this to FALSE, CodeIgniter will not attempt to protect your field or table names with backspaces. This is useful if you need a compound selection statement.
source to share
To avoid CI protecting your request, you need to use it false
as the second parameter to the function select
. See doc $ this-> db-> select ();
SO Your request would be like this:
$result = $this->db->select("GROUP_CONCAT(Service_name SEPARATOR ', ' ) AS service_names",false)
->where('Service_boid', $boId)
->get('service');
source to share