phpmyadmin Question posted in
The official documentation can be found here.

How to Fix "A Database Error Occurred" in Codeigniter for IF condition in MySQL? – Phpmyadmin

I am new to Codeigniter, Having error in mysql while using IF Statement,
Here is my Code 

$this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active');
$this->db->from('abs_city AS A');
$this->db->join('abs_state AS B', 'A.state_id = B.state_id AND B.visible = 1 AND A.country_id = B.country_id ', 'inner');
$this->db->join('abs_countries AS C', 'A.country_id = C.country_id AND B.visible = 1 ', 'inner' );
#$this->db->where('A.visible = 1');
return $this->db->get()->result();

Error in Browser
enter image description here

While removing tag(`) following query runs in phpmyadmin 

    SELECT
    `A`.`city_id`,
    `A`.`name` AS city_name,
    `B`.`name` AS state_name,
    `C`.`name` AS country_name,

IF (A.visible, "Yes", "No") AS active
FROM
    (`vbs_abs_city` AS A)
INNER JOIN `vbs_abs_state` AS B ON `A`.`state_id` = `B`.`state_id`
AND B.visible = 1
AND A.country_id = B.country_id
INNER JOIN `vbs_abs_countries` AS C ON `A`.`country_id` = `C`.`country_id`
AND B.visible = 1

Please help me solve this, Thanks on advance.

Tags:

3

Answers


  1. 0

    You can call the select method with FALSE as the last parameter, like this

    $this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active',false);

    That will prevent CI to add the ` in your query

    Login or Signup to reply.
  2. 0

    try update your query to 

    $this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(`A.visible`,"Yes","No") AS active',false);
$this->db->from('abs_city AS A');
$this->db->join('abs_state AS B', 'A.state_id = B.state_id AND B.visible = 1 AND A.country_id = B.country_id ', 'inner');
$this->db->join('abs_countries AS C', 'A.country_id = C.country_id AND B.visible = 1 ', 'inner' );
#$this->db->where('A.visible = 1');
return $this->db->get()->result();
    Login or Signup to reply.
  3. 0

    Instead of adding the false parameter to your SELECT statement (which is inherently insecure), try changing the statement to:

    $this->db->select('A.city_id, A.`name` AS city_name, B.`name` AS state_name, C.`name` AS country_name, IF(A.visible,"Yes","No") AS active');

    (just remove the backticks around A.visible)

    This is a more secure version than adding the FALSE parameter. The latter will prevent the automatic escaping that query builder adds to the built statement, rendering the whole select statement less secure, which is not advisable

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search