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 :)

May I interest you in using ORM package for CodeIgniter? DataMapper Many PHP framework now ships with ORM so there is definitely no harm in looking into it especially if you are just getting into it. It solves your relationship problem, passing values to the view, etc. — JofryHS
– JofryHS, Commented Sep 18, 2013 at 11:37
oh thank you ! I'll check for it right now and I wish it helps :) — Jasmine
– Jasmine, Commented Sep 18, 2013 at 11:40

ABorty · Accepted Answer · 2013-09-18 11:44:32Z

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.

Collectives™ on Stack Overflow

codeigniter mutiple select queries dependent on each other

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related