codeigniter use concat regex in where clause

Question

I have a custom query like this

SELECT `wo_number`,`request_date`,`wo_type`,`requestor` 
FROM `work_orders` 
WHERE CONCAT(",", `assigned_to_enggs`, ",") REGEXP ",(21),"

It works fine for me I want to use it in Codeigniter Active Record. I have tried something like

$this->db->select('wo_number,request_date,wo_type,requestor')
->from('work_orders')
->where("CONCAT(',', assigned_to_enggs, ',') REGEXP ',(21),'");

Don't know what I am doing wrong in syntax. can someone guide me. Thanks

I ran your active record script and printed out the compiled query with CI3 and got: SELECT `wo_number`, `request_date`, `wo_type`, `requestor` FROM `work_orders` WHERE CONCAT(',', assigned_to_enggs, ',') REGEXP ',(21),' How is your built query rendered? We don't know. Are you using the correct db driver to suit your database dialect? Notice the difference between your raw query with double-quoted values versus the where() method where you have hardcoded single-quotes around values. — mickmackusa
– mickmackusa ♦, Commented Jun 3 at 5:15
See this answer for fully dynamic (driver-dictated) quoting around values and entities. — mickmackusa
– mickmackusa ♦, Commented Jun 3 at 5:17

Vishal Dudhatra · Accepted Answer · 2019-05-15 01:57:31Z

2

Please try below case in where and use result() for get result array

$this->db->select('wo_number,request_date,wo_type,requestor');
$this->db->where("CONCAT(',', assigned_to_enggs, ',') REGEXP ',(21),'", NULL, FALSE);
$this->db->select->from('work_orders');    
$query = $this->db->get()->result();

If not work than you also write query like below in codeigniter.

$query = $this->db->query('YOUR QUERY HERE');
$query->result()

answered May 15, 2019 at 1:57

Vishal Dudhatra

946 bronze badges

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

1 Comment

Muhammad Asif Raza Over a year ago

thanks it worked.. BTW find_in_set() method is another way of doing it ->where("find_in_set($get_engineer_id, wo.assigned_to_enggs)");

Collectives™ on Stack Overflow

codeigniter use concat regex in where clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related