CodeIgniter Forums
Active Records - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: Active Records (/thread-28217.html)



Active Records - El Forum - 03-04-2010

[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.


Active Records - El Forum - 03-05-2010

[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.

Smile


Active Records - El Forum - 03-05-2010

[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.


Active Records - El Forum - 03-05-2010

[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


Active Records - El Forum - 03-05-2010

[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 Smile


Active Records - El Forum - 03-05-2010

[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.