15

I am writing a query in codeigniter with FIND_IN_SET() function.

   $this->db->where(FIND_IN_SET('".$value."',employer_job_location));
    $query_res= $this->db->get("employer_posted_jobs");
   echo $this->db->last_query(); exit;

It is yielding 

SELECT * 
FROM (`employer_posted_jobs`) 
WHERE (FIND_IN_SET('Delhi',employer_job_location)) IS NULL

In above query "IS NULL" is extra and it is very annoying. Can anyone tell why this is coming with the query? Thank you..

Nabin Kunwar
  • 1,965
  • 14
  • 29
Aabshar Pasha
  • 194
  • 2
  • 10

2 Answers2

18

You must always check the results from the FIND_IN_SET() function somehow to make it work, try this:

$this->db->where("FIND_IN_SET('$value',employer_job_location) !=", 0);
Michael Krikorev
  • 2,126
  • 1
  • 18
  • 25
  • Thanks for your answer, However, it has worked with slightly different syntax: $this->db->where(FIND_IN_SET('".$value."',employer_job_location) !=0); – Aabshar Pasha Jul 08 '13 at 18:31
  • You can always do `$this->db->where(sprintf("find_in_set('%d', employer_job_location) !=",$value), 0);` – seangates Apr 09 '15 at 02:51
  • can you please suggest any reference regarding this to understand it little deep. @MichaelK – always-a-learner Aug 17 '17 at 07:09
  • @ankit suthar: You can find the documentation of MySQL find_in_set function here: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set The rest is just basic syntax for Codeigniters Query Builder Class: https://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data – Michael Krikorev Aug 17 '17 at 11:38
0

$this->db->where("FIND_IN_SET('$value',employer_job_location) !=", 0);

Manvendra Sharma
  • 1