[eluser]D_Williams[/eluser]
I think I've made a similar topic to this before and came out with a slightly better solution than I had before. I was going to settle for it but after trying to use it for a bit I've decided I'm not happy with it still.
A little background: I'm in the process of making a report generation program. Obviously the most fundamental part of that is pulling data from the database to put on the reports. The data for the reports needs to come from a secondary database (in other words, not the primary database that I have CI auto connecting to). That part isn't a problem, the problem is that each report is varied in what information it needs to grab. Most of the data comes from the massive "dbase" table. It has at least 75 columns and contains most of the data I need to report on (any comments about "bad database design" are just preaching to the chior, I didn't design it and don't have the option of changing it).
This is the model function I came up with to retrieve bits and pieces of the dbase table from my controllers ($this->CMAX points to the secondary database):
Code:
function get_fields($cols, $where = NULL, $vals = NULL)
{
$sql = "SELECT $cols FROM dbase";
if(!empty($where) && !empty($vals))
$sql .= " WHERE $where";
$query = $this->CMAX->query($sql, $vals);
return $query->result_array();
}
This technically works but in usage it ends up something like this:
Code:
$this->load->model('account_model');
$select = 'long, list, of, fields, to, select';
$where = "clientname = ? AND str_to_date(dateplaced, '%c/%e/%Y') >= ? AND str_to_date(dateplaced, '%c/%e/%Y') <= ?";
$vals = array($this->post('client'), $this->post('startdate'), $this->post('enddate'));
$this->account_model->get_fields($select, $where, $vals);
It's essentially the same thing as just outright executing the SQL in my controller, only more limiting.
This problem has been frustrating me for a while. I was hoping somebody could share some experiences of give advice on how I can implement some elegant MVC solution here. Maybe I shouldn't bother and just execute the SQL from my controller anyway?