CODEWITHSUNDEEP
phpmysqlcodeigniter
Kichu
Kichu

Reputation: 3267

Mysql Query In codeigniter

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 table have multiple countries that is the reason to use varchar datatype.

Here i want to select table rows which have $acountry value in the filed varcountry.

How can i do that?The above code is it correct?

Upvotes: 0

Views: 1328

Answers (2)

Naseer Panhwer
Naseer Panhwer

Reputation: 169

use this query..

$search_field = array('varcountry'=>$acountry)
$result = $this->db->get_where('tblagencies' , $search_field );

but in codeignator you can use your own queries like

$sql = "select * from tblagencies where varcountry like '%acountry%'";
$result = $this->db->query($sql);

Upvotes: 0

ajreal
ajreal

Reputation: 47321

You have choosen a wrong data type for storing a comma separated value 1,2,3,4 into varchar,
you should chose 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);
// meaning 1,2,3,4 = 4 rows

// grabbing result using inner join

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

I don't like the active record in codeigniter,
is easy to use (not doubt with this),
but it dis-allowed lost of flexibility

Personally I like the construct my own query,
provided you have the understanding of the table schema (which you have to anyway)

Upvotes: 1

Related Questions