phpmysqlcodeigniterwhere-clausefind-in-set

MySQL SELECT query in CodeIgniter WHERE a comma-separated column contains a specific whole value

This is my mysql query:

$acountry = 1;
$this->db->where_in('varcountry', $acountry);
$val = $this->db->get('tblagencies')->result();

In database table the varcountry filed is stored like this 1,2,3,4; its type is varchar. Each row in the table has multiple countries -- that is the reason to use varchar datatype.

I want to select table rows which have $acountry value in the field varcountry.

How can I do that? Is the above code correct?

Solution

  • You have chosen a suboptimal data type for storing a comma-separated value 1,2,3,4 into varchar. You should choose a data-type of set, or normalize into a separate table like:

    CREATE TABLE country (id, name ...);

CREATE TABLE agencies_country ( agency_id, country_id);

INSERT INTO agencies_country (agency_id, country_id)
    VALUES (x,1), (x,2), (x,3), (x,4);

    This means effectively converts your 4 delimited values into column values across four rows.

    This sets up grabbing the desired records using an INNER JOIN.

    Using set is easier, but common practice is to normalize the data (which requires some understanding).

    I don't like the active record in CodeIgniter -- it is easy to use (no doubt with this), but it disallows lots of flexibility.

    Personally, I like to construct and execute my own SQL query string. This is a suitable approach provided you have the understanding of the table schema and best security practices.