codeigniter mutiple select queries dependent on each other

Question

I'm a newbie in codeigniter (CI) and I need to select the logged-in user's competitions and total votes , let say users and competition tables have many-to-many relation and the same goes with the competition items. I need to select the user's competitions and all items votes that relate to that user; also I need to select all competitions' votes and get the total votes for each one of them. after all that goes on , all the result must be displayed in one view (and that is the most complicated part) for example , I can't manipulate all that in one model and how to return many arrays to a controller ,so on.

here's an example to my contoller :

<?php
class User_id extends CI_Controller{

    function __construct()
    {
       parent::__construct();
       $this->load->helper(array('form', 'url'));
       $this->load->library('session');
    }

    function index(){
        $this->load->view('v_user_id');
    }

    function view_comp(){
        $c_id = $this->input->post('id');
        $this->session->set_userdata('c_id',$c_id);
        $s_id = $this->session->userdata('c_id');
        $this->load->model('comps_model');
        $data['rows'] = $this->comps_model->getComps($s_id);
    }           
}
?>

and here's my aim model that should contain 'all select queries' and return 'all results' previously mentioned :

<?php
class Comps_model extends CI_Model{

    function getComps($id){

        $this->load->database(); 
        $id = $this->db->query("select competition_id from user_competition        where user_id='".$id."'");
        if($id->num_rows() > 0){
            foreach($id->result() as $id_row){
                $comp_name = $this->db->query("select competition_title from competition where competition_id='".$id_row->competition_id."'");
                if($comp_name->num_rows() > 0) {
                    //all the stuff should go here or something like that
                }
            }
        }
    }

}
?>

I'd be grateful for some code examples :)

Answer 1

You can try like this

function getComps($id)
{
    $this->load->database();
    $data=array(); 
    $id = $this->db->query("select competition_id from user_competition where user_id='".$id."'");
    if($id->num_rows() > 0)
    {
        $data=$id->result_array();
    }
    foreach($data as $key=>$each)
    {
        //adding competition title
        $comp_name = $this->db->query("select competition_title from competition where competition_id='".$each['competition_id']."'");
        if($comp_name->num_rows()>0){
            $temp=$comp_name->row_array();    
            $data[$key]['competition_title']=$temp['competition_title'];
        }
        else
        {
            $data[$key]['competition_title']="";
        }
        //other calculations will go on 
    }
    echo "<pre>";print_r($data);die;
}

if you do like this the everytime you will calculate some value and insert the value in the existing data array.for example if you count total votes then with in the foreach loop count the total and insert to the array like

  $data[$key]['total_votes']=$temp['total_votes'];

please let me know if you face any problem.