Active Records

[eluser]RobertB.[/eluser]
Hello guys,
How can I do this operation with active records.

Code:

WHERE b.cat_id = 2 OR b.state_id IS NULL

Just in case, this is how I'll like to use it.

Code:

$this->db->select('s.id, s.state, COUNT(b.is_active) AS active');
$this->db->from('states AS s');
$this->db->join('businesses AS b', 's.id = b.state_id', 'left');
$this->db->where('b.cat_id', $this->uri->segment(3), 'b.state_id', null);
$this->db->groupby('s.id')

Thanks,
Robert.

[eluser]JamieBarton[/eluser]
I think that this could work:

Code:

$seg3 = $this->uri->segment(3)

Code:

$this->db->where('b.cat_id', '$seg3 OR b.state_id = null);

Give that a try, I'm also new to CI.

[eluser]stef25[/eluser]
From the manual

Code:

$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);

// Produces: WHERE name != 'Joe' OR id > 50

Note that your $this->uri->segment(3) is not sanitized.

[eluser]RobertB.[/eluser]
Thank you all, this works fine. I did not know about or_where() clause.

Code:

$this->db->select('s.id, s.state, COUNT(b.is_active) AS active');
$this->db->from('states AS s');
$this->db->join('businesses AS b', 's.id = b.state_id', 'left');
$this->db->where('b.cat_id', $this->uri->segment(3));
$this->db->or_where('b.state_id', null);
$this->db->groupby('s.id');

Stef, what do you mean by "$this->uri->segment(3) is not sanitized" and how can I sanitize the code.

Thanks,
Robert

[eluser]stef25[/eluser]
You always have to sanitize user input - anyone could type anything in to the URL and have that value fed in to your query. Even though CI's default settings don't allow funny characters (quotes etc) in the URL's and Active Record queries automatically escape values, it's always good to realize this.

I think you should be ok, just make sure you understand this

[eluser]RobertB.[/eluser]
Hello again,

Where can I define variables so the m/v/c share it with out have to declare them in each functions. Example of what I mean.

Code:

function Name()
    {
$cat = $this->uri->segment(3, 0);

$this->db->select('s.id, s.state, COUNT(b.is_active) AS active');
$this->db->from('states AS s');
$this->db->join('businesses AS b', 's.id = b.state_id', 'left');
$this->db->where('b.cat_id', $cat);
$this->db->or_where('b.state_id', NULL);
$this->db->groupby('s.id');
$this->db->order_by('s.state', "asc");

I don't want to declare the variable inside the function because I use it in multiple functions in the same model. I try to do in on the top of the model but does not work. I hope I've explained myself.

Thanks.