insert result of query into another query in codeigniter

Question

I need to insert query result as input of another query Here is the code that I have tried,Please I need help immediately I need to get id from query1 and insert it in query2 as $id

class Get_db extends CI_Model{
    public function get_All(){
        $query1 = $this->db->query("SELECT name,id from companydetails");
        return $query->result();
    }

    public function getBranches(){
        $query2 = $this->db->query("SELECT branches.name FROM branches WHERE           branches.companyid=$id ");
        return $query2->result();
    }
}

I need to get id from query1 and insert it in query2 as $id

If the two tables are in the same database, you only need one query. The key is joining tables. If you don't know what that means, I've heard good things about the book, Teach Yourself SQL in 10 Minutes. — Dan Bracuk
– Dan Bracuk, Commented Jul 1, 2014 at 11:35
get_all() will return u array of data.... from that u can send array of id's as a parameter in getBranches() use foreach create an array of branches and return that — user3426968
– user3426968, Commented Jul 1, 2014 at 11:38

Luigi T. · Accepted Answer · 2014-07-01 12:06:55Z

1

The method get_All() returns an object, so you could use :

$resultQuery1 = get_All();

$output = array();

foreach($resultQuery1 as $q)
    $output[$q->id] = getBranches($q->id);

In the getBranches method you should accept a parameter

public function getBranches($id) {...}

The $output variable is an array with all the results of the getBranches method.

answered Jul 1, 2014 at 12:06

Luigi T.

5554 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VishnuPrasad · Accepted Answer · 2014-07-01 12:25:33Z

1

try this code:

    <?php 
$this->db->select('name,id')
    ->from('companydetails');
$qry = $this->db->get();
$res = $qry->result();

foreach($res as $val){
    $this->db->select('name')
        ->from('branches')
        ->where('companyid', $val->id);
    $qry2 = $this->db->get();
    $res2 = $qry->result();
}   

return $res2;

?>

the $res array is the executed result of the first query that is going to put as input to the second query

edited Jul 1, 2014 at 12:25

answered Jul 1, 2014 at 12:19

VishnuPrasad

1,0886 gold badges18 silver badges37 bronze badges

1 Comment

VishnuPrasad Over a year ago

under a function and access it in controller as, $res = $this->model_name->function_name(parameters);

Craig · Accepted Answer · 2014-07-01 12:21:20Z

This will do it...

      public function get_All()
      {
                $this->db->select('name, id');
                $query = $this->db->get('companydetails');
                if ($query->num_rows () > 0)
                {

                         $result = $query->result();
                           foreach($result as $company)
                           {
                                    $company->branches = $this->getBranches($company->id);                                       
                           }

                          return $result;
                }
                else
                {
                    return false;
                }
      }

      public function getBranches($id)
      {
                $query = $this->db->get_where('branches', array('id' => $id));
                return $query->result();
      }

Parag Tyagi · Accepted Answer · 2014-07-01 13:01:46Z

Its possible in one simple function. Use JOIN-

<?php
class Get_db extends CI_Model
{
   public function get_company_branches()
   {
      $this->db->select('companydetails.name','companydetails.id','branches.name');
      $this->db->from('companydetails');
      $this->db->join('branches', 'companydetails.id = branches.companyid');

      $query = $this->db->get();

      $result = array();
      if($query->num_rows() > 0)
          $result = $query->result_array();

      echo '<pre>'; print_r($result);    // Your expected result
   }
}

OR if you don't want to use JOIN and go w.r.t your question asked, then here you go:

Model:

...
...
public function get_All()
{
    $this->db->select("id","name");
    $this->db->from("companydetails");

    $query = $this->db->get();
    return $query->result();
}

public function getBranches($id)
{
    $this->db->select("name");
    $this->db->from("branches");
    $this->db->where("companyid", $id)

    $query = $this->db->get();
    return $query->result();
}

Controller:

...
...
public function somename()
{
    $companies = $this->get_db->get_All();

    $branches = array();
    foreach($companies as $company)
    {
       $branches[] = $this->get_db->getBranches($company->id);
    }

    echo '<pre>'; print_r($branches);   // Expected result
}

Gihan Wijethunga · Accepted Answer · 2014-07-02 04:28:25Z

controller

 public function home(){
        $this->load->model('get_company_model');
        $this->load->view('view_header');
        $data['results'] = $this->get_company_model->get_all();

        $this->load->view('view_nav',$data);
        $this->load->view('view_content');
        $this->load->view('view_footer');
    }

model

 public function get_All(){
        $query = $this->db->query("SELECT name,id from companydetails");
        return $query->result();

    }
    public function get_branch($companyID){
        $query = $this->db->query("SELECT name,id from branches WHERE companyid='".$companyID."'");
        return $query->result();

    }

view

<div>
        <ol class="tree">
    <li>
        <label for="folder1"><?=$row->name?></label> <input type="checkbox"  id="folder1" /> 
        <ol>
              <?php
                $myresult=$this->get_company_model->get_branch($row->id);
                foreach($myresult as $row2):
                  ?>  
            <li class="file"><a href="#"><?=$row2->name?></a></li>
            <?php
                 endforeach;
                ?>
</ol>
    </div>

    <?php
    endforeach;
    ?>

Collectives™ on Stack Overflow

insert result of query into another query in codeigniter

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related