2

I have a codeigniter-mysql requirement to select values from table_2 which joins table_1 and need to apply where statement in a comma separated field value. Tried as follows,

$where  =  $this->db->escape("FIND_IN_SET(table_1.id,table_2.places_id)<>0");

    $this->db->select('table_2.*,GROUP_CONCAT(table_1.location)AS location');
    $this->db->from('table_2');
    $this->db->join('table_1', $where);
    $this->db->where('ltable_2.packages_id', $id);
    $results = $this->db->get();
    return $results->result();

But the above codeigniter db object returns following mysql query and its not working :(

SELECT `table_2`.*, GROUP_CONCAT(table_1.location)AS location FROM `table_2` JOIN `table_1` ON 'FIND_IN_SET(table_1.id,table_2.places_id)<>0' WHERE `ltable_2`.`packages_id` = <id-goes-here>

It seems the quotes around 'FIND_IN_SET(table_1.id,table_2.places_id)<>0' creates the problem! All helps are appreciated to resolve the issue.

vishuB
  • 4,173
  • 5
  • 31
  • 49
Aadi
  • 6,959
  • 28
  • 100
  • 145
  • 1
    Try with add false at last `$this->db->select('table_2.*,GROUP_CONCAT(table_1.location)AS location',FALSE);` – Saty Sep 11 '15 at 08:09
  • Why you are escaping `FIND_IN_SET` part ? just put as it is in `join()` – M Khalid Junaid Sep 11 '15 at 08:16
  • It would be nice to see the db error returned – Vali S Sep 11 '15 at 08:59
  • tried $this->db->join('table_1', $where, false); and $this->db->select('table_2.*,GROUP_CONCAT(table_1.location)AS location', FALSE); but failed! this will build query as follows, SELECT table_2.*, GROUP_CONCAT(lgk_places.location)AS location FROM `table_2` JOIN `lgk_places` ON `FIND_IN_SET`(`lgk_places`.`id,table_2`.`places_id)<>0` WHERE `table_2`.`packages_id` = 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 'WHERE `table_2`.`packages_id` = '34' LIMIT 0, 30' at line 1 – Aadi Sep 11 '15 at 09:31
  • Did you find any answer for this? – Tariq Dec 12 '15 at 18:16

1 Answers1

1

You could try $this->db->join('table_1', $where, false);. That would get rid of the quotes, and if otherwise your query is good, it should be fine.

Vali S
  • 1,471
  • 2
  • 10
  • 18