CodeIgniter better script flow for Query

Question

Hi there,

I've the following data:

promo_id    date    time    reg_cnt notes
2760    2010-06-02  01:30:00    2   -
2760    2010-06-02  05:30:00    2   -
2760    2010-06-03  07:00:00    1   -
2760    2010-06-03  07:30:00    2   -
2760    2010-06-03  08:30:00    3   -
2760    2010-06-04  09:00:00    1280    promo from vendor xxx
2760    2010-06-04  09:30:00    6   -
2760    2010-06-04  10:00:00    3   -
2760    2010-06-04  10:30:00    5434    TVC program xxx

I need it to be displayed like this:

PROMO   DATE    REG CNT NOTES
2760    2010-06-02  4   -
2760    2010-06-03  6   -
2760    2010-06-04  6723    promo from vendor xxx, TVC program xxx

I can't figure it out the simple way to get these data. For now, I did something like this on my model:

$data = array();
$query = $this->db->query("SELECT promo_id, date, SUM(reg_cnt) AS reg_cnt FROM promo_signup");
foreach( $query->result_array() as $q ){
    $data[ $q['promo_id'] . '-' . $q['date'] ] = $q;
    $query2 = $this->db->query("SELECT notes FROM promo_signup WHERE date = '" . $q['date'] . "' AND promo_id = '" . $q['promo_id'] . "'");
    $data2 = array();
    foreach( $query2->result_array() as $q2 ){
        $data2[] = $q2['notes'];
    }
    $data[ $q['promo_id'] . '-' . $q['date'] ]['notes'] = $data2;
}

Then, I still have to loop through $data on my view to display it.

I guess it's a waste of processing power, compared to code below (if I did it without CI):

$data = array();
$query = $this->db->query("SELECT promo_id, date, SUM(reg_cnt) AS reg_cnt FROM promo_signup");
foreach( $query->result_array() as $q ){
    $data[ $q['promo_id'] . '-' . $q['date'] ] = $q;
    $query2 = $this->db->query("SELECT notes FROM promo_signup WHERE date = '" . $q['date'] . "' AND promo_id = '" . $q['promo_id'] . "'");
    $data2 = array();

    //echo data here

    foreach( $query2->result_array() as $q2 ){
        $data2[] = $q2['notes'];

        //echo notes data here

    }
}

So, is there a better way doing it in CI?

I'm sorry if you guys found some error on code above, because it's just concept code.

Answer 1

CI is based on the MVC pattern, to do do it your way (without CI) would defeat the purpose of using CI in the first place

Answer 2

Hi silent,

Kristoffer is correct that you should put your data handling in models, your business logic in controllers, and your views in views.

For more on that, see CI's MVC page, their Controller page, their Model page, and their View page. You'll want to trust us here that using PHP the old-fashioned way will cause you many headaches. The MVC design pattern is the way to go. Before you go further, you will need to understand it. If these pages don't help you, search around for "codeigniter mvc help" or tutorial or videos or whatever.

Anyway...on to your MySQL issue. Try this:

SELECT promo_id, date, SUM(reg_cnt) AS reg_cnt, GROUP_CONCAT(DISTINCT notes SEPARATOR ', ') AS notes FROM promo_signup GROUP BY date

For more info on GROUP BY (aggregate) functions, see this page from MySQL.