MVC issues...trying to avoid interacting with the database from my controller

[eluser]D_Williams[/eluser]
My goal is to make a "batch letter sender" utility script that interacts with our main account database to schedule letters to be sent. All my script needs to do to schedule the letter is insert a row into a table so that's cake.

The basic flow for my script will be this:
- User fills out my form
- Form is validated and cleaned up
- Display a list of all accounts that will be affected and have them confirm

Now, I'm allowing the user to choose three constraints here for which accounts have letters sent on them:

1. Which client the account belongs to
2. What day the account was imported into our system
3. What status the account is set to

Now without explaining fully about our system, these three things can obviously just be thought of as parts of the where clause of my initial select. Now so far I have the view done and it works fine, no need to post the code here. What I'm having trouble with is how to create my models (which will be reused for other pages under this same project eventually, the letter sending is just a small function of what it will do eventually), and how to keep SQL out of my controller. I know that CI is lax on enforcing strict MVC rules but I'd like to stick to good design wherever possible.

For starters, here's my controller as it is now (rather barebones):

Code:

function batch_mail_sender()
    {
        $this->load->helper('form');
        $this->load->library('form_validation');
        
        $this->form_validation->set_rules('client', 'Client', 'trim|required');
        $this->form_validation->set_rules('type', 'Letter Type', 'trim|required');
        $this->form_validation->set_rules('calendar_dateplaced_year', 'Year', 'trim|required');
        $this->form_validation->set_rules('calendar_dateplaced_month', 'Month', 'trim|required');
        $this->form_validation->set_rules('calendar_dateplaced_day', 'Day', 'trim|required');
        
        if ($this->form_validation->run() == FALSE)
        {
            // Form has not been run or there are errors
            $this->load->library('form_elements');
            $data['content_view'] = 'utilities/batch_mail_form';
            $data['page_title'] = 'Batch Mail Sender';
            $data['javascript'][] = 'calendar.js';
            $data['css'][] = 'calendar.css';
            $this->load->view('templates/popup', $data);
        }
        else
        {
            // Form validation success, process away
        }
    }

All that's there so far is the code to set up my view and validate the form. It's time to start filling in the logic down in the "else" statement at the bottom and I'm a bit stumped on how to proceed.

Now, if I were coding with standard procedural PHP here my obvious next step would be to make a query where the client name is "this", the status name is "that", and the date imported is "whatever" and proceed from there with the results. Without making some model and model function highly specific to this particular task (like GetAccountsWhereClientIsXAndStatusIsYAndDateImportedIsZ(x,y,x) which seems a bit ridiculous), how can I query the database with those constraints?

I'm not sure if I've designed myself into a hole here? Is this a situation in which it would be OK to query the database in my controller?

[eluser]sketchynix[/eluser]
You havent designed yourself into a hole at all. You are asking the right questions to get accustomed to using MVC. Since you are interacting with the database, you will want to create a model and load the model in the controller.

Without giving too much away...

Code:

function batch_mail_sender()
    {
      $this->load->model('your_model_name');

...

Code:

 else
        {
         // Form validation success, process away
          $this->your_model_name->GetAccounts($client, $status, $imported); /*set these variables equal to the form input values (be sure to clean the data)
*/
        }

Checkout the database class a bit on how to interact with it. There are a number of ways to query

Code:

function GetAccounts($client,$status,$imported){
    $select="SELECT FROM table.accounts WHERE client = ? AND status = ? AND imported = ?";
   $run = $this->db->query($select, array($client, $status,$imported));
}

[eluser]D_Williams[/eluser]
I had considered doing the "grab everything" approach like that but the problem is the "accounts" table has something like 70 different columns. It's not my design and it's mainly used by a closed source software package so I have no option but to work with it like that. Grabbing 70 columns when I need 1 or 2 is a waste of resources, especially when I'm grabbing those 70 columns for potentially hundreds of accounts.

The first idea that comes to my mind here is having a GetAccounts function that only returns the ID, and then I can pass that ID to other functions like GetAddressByID(). That seems slightly better but I'm still left with the problem of designing a re-usable GetAccounts function. This may well be the only time in my program that I need to grab accounts with these particular three conditions. Each time I pull account information, the fields that ultimately go into the "where" clause will likely be a little bit different. Obviously I don't want to have a thousand little functions like that to cover every possible scenario, but I can't come up with a clear solution.

[eluser]Buso[/eluser]
Write a generic method, something like: get_field_by($wanted_field,$where_field,$where_field_value)

eg:

Code:

$users_ids = $this->user_m->get_field_by('id','registration_date <',time()-60*60*24*30); // gets all the id's from users that has been registered for longer than 1 month

You could also add other already existing methods from the database class, to yor base model, like 'where()'. Or just add all of them:

Code:

public function __call($name,$arguments) {
  call_user_func_array(array($this->db,$name),$arguments);
  return $this;
}

I haven't tested the code, but you get the point. The idea is to be able to do this from your controller:

Code:

$this->user_m->where('role !=','admin')->get_accounts(); // where() doesn't belong to user_m, it was created dinamically

Or if you are in a hurry, you can do this:

Code:

$this->db->select('id,name,role')->where('role !=','admin');
$this->user_m->get_accounts();

Don't worry about 'breaking MVC', as long as get_accounts does all the rest. The problem comes when you start writting foreach's and stuff like that in your controller, just to add an extra field to the results.

[eluser]D_Williams[/eluser]
[quote author="Buso" date="1285382259"]Write a generic method, something like: get_field_by($wanted_field,$where_field,$where_field_value)[/quote]

I think this is probably the most simple solution for now. Several people proposed more in-depth solutions to me that would probably work better ultimately but I think I'll keep it simple until I run into more problems.

I implemented it rather transparently with prepared statements like so: (in my model class)

Code:

function get_fields($cols, $where, $vals)
    {
        $sql = "SELECT $cols FROM dbase WHERE $where";
        $query = $this->CMAX->query($sql, $vals);
        
        return $query->result_array();
    }

I'm still writing bits and pieces of SQL in my controller but I guess it cannot be helped.