Thursday, March 28, 2013

How to use find_in_set() mysql function in Codeigniter?

How to use find in set mysql function in Codeigniter active records?

I was very upset and frustrated when I couldn't find any good function and solution in the Codeigniter Active Record Class for FIND_IN_SET type queries.

So I have used custom tweak to use find_in_set in Codeigniter active record.

$where = "FIND_IN_SET('".$searchterm."', columnName)";  

$this->db->where( $where ); 

where columnName holds an array of values.

And  it worked.  :)

When to use FIND_IN_SET()?

FIND_IN_SET() : This function is useful where you have comma separated list of values stored in database and want to check if a value exist in that comma seperated list.

Example: WHERE FIND_IN_SET( ‘yellow’, colorlist )

One of the very important mysql string function is FIND_IN_SET and its returns the position ofa string value if it is available (as a substring) within a string. String contain comma separated characters or values.

This function returns 0 when search string does not exist in the string
FIND_IN_SET is useful when you are using explode OR implode to store multiple value.
If data stored in database with comma separated value like you have cat_id and you stored it as (1,2,3,5 ) within one field , you can use FIND_IN_SET in select query to match particular value in whole field set.

FIND_IN_SET (find string, stringList)

1 comment: