How to select data from 4 tables

I want to select data from 4 tables using the Codeigniter framework. The 4 tables have a similar column structure. I want to get the table data corresponding to certain year and month.

here is my table structure:

table t1

accid     uid      month    year        ccbalance
--------------------------------------------------------
101         19       May      1996        4545
-----------------------------------------------------
101         19       sept      1998         1500
--------------------------------------------------------

table t2

accid     uid      month      year        insbalance
--------------------------------------------------------
102         19       May       1995         2059
-----------------------------------------------------
102         19       july       1998         2500
--------------------------------------------------------

table 3

accid     uid      month    year        ccbalance
--------------------------------------------------------
109         19       June      1999         10000
-----------------------------------------------------
109         19       Aug       1990        1500
--------------------------------------------------------

table t4

accid     uid      month    year        ccbalance
--------------------------------------------------------
105         19       Aug      1995         10000
-----------------------------------------------------
105        19       May       1995         3333
--------------------------------------------------------

If I select May 1995, I want to get this result:

accid     uid      month    year        ccbalance
--------------------------------------------------------
105         19       May     1995         3333

102         19       May     1995         2059

Answer:

there is no built in union function in Codeigniter 2.0 and 3.0

you can create your own sql query and execute it like this:

$sql="(SELECT * from t1 where month='May' AND year=1995)
       UNION
      (SELECT * from t2 where month='May' AND year=1995)";
$query = $this->db->query($sql);    
return $query->result();

more information on mysql union syntax you find here (official documentation)

Read More

MongoWriteConcernException. the (immutable) field '_id' was found to have been altered to _id

while making updat in mongodb in codeigniter I have the following error plz help me to solve this issue, Type: MongoWriteConcernException

Message: localhost:27017: After applying the update to the document {_id: ObjectId('55ee98543bd7af780b000029') , ...}, the (immutable) field '_id' was found to have been altered to _id: "55ee98543bd7af780b000029"

Filename: C:\xampp\htdocs\CI\application\models\mongo_model.php

here is my controller code

public function  update()
{
    $userdata['firstname'] = $this->input->post('txtfirstname');
    $userdata['lastname'] = $this->input->post('txtlastname');
    $userdata['email'] =  $this->input->post('txtemail');
    $userdata['password'] = md5($this->input->post('txtpassword'));
    $userdata['_id'] = $this->input->post('hiddenId');
    $collection=  $this->mongo_model->updateuserdb($userdata);
    if ($collection)
    {
        header('location:'.base_url()."index.php/user".$this->index());
    }
}

and model code is

public function updateuserdb($userdata)
{
    $id = $userdata['_id'];
    $collection = $this->mongo_db->db->selectCollection('myfirstCollection');
    $query = $collection->update(array('_id' => new MongoId($id)), array('$set' => $userdata), array('upsert' => FALSE));
    return $query;
}

any help is appreciable, thanks in advance

Answer:

You cannot update the _id field.

Notice that your $userdata object variable contains the _id field and then you proceed to pass that $userdata object as the value to be updated. As a result, you are attempting to update the _id field.

You need to remove the _id from $userdata when doing '$set'=>$userdata.

$collection->update(array('_id'=>new MongoId($id)),array('$set'=>$userdata),array('upsert'=>FALSE));




Read More

Query database in helper file Codeigniter 3

I was trying to add a function to a helper file to collect extra info from the Db. This got errors as the the document id I am trying to use to call the function is put out as an array and not what I want...

My Model looks as follow:

public function get_categoryads()
{

    $this->db->select('ads.id AS id, ads.userid AS userid, ads.adnr AS adnr,
  ads.location AS town, search_town.townFileName AS townlink, search_prov.provLabel AS province, 
  search_prov.subLink AS provlink,text, ad_image.image AS image,addate, r_option, R_rand,
  adcat.name AS catname,  adcat.clinkname AS clinkname, adsubcat.name AS subcatname, adsubcat.linkname AS subcatlink,
  adcat.id AS catid, adsubcat.id AS subcatid, ads.r_option AS r_option, ads.R_rand AS R_rand, 
  adcat.catcol1 AS catcol1, adcat.catcol2 AS catcol2, search_town.townId,ads.townId');
  $this->db->from('ads');
  $this->db->join('adsubcat', 'adsubcat.id=ads.subcatid');
  $this->db->join('adcat', 'adcat.id=ads.catid');
  $this->db->join('search_town', 'search_town.townId=ads.townId');
  $this->db->join('search_region', 'search_region.regionId=search_town.relRegionId');
  $this->db->join('search_prov', 'search_prov.provId=search_region.relProvId');
  $this->db->join('ad_image', 'ad_image.adid=ads.id');
  $array = array(
  'ad_image.picorder'=> 1,
  'ad_image.aproved'=> 1,
    'adcat.id' => 9,
   'scam' => 0,
    'adactive' => 1
    );

  $this->db->where($array);
  $this->db->group_by("ads.id");
  $this->db->order_by('addate','desc');
  $this->db->limit(5); 
  $query = $this->db->get(); 
  return $query->result_array();
}

My controller looks as follow:

class Categories extends CI_Controller {
          public function __construct()
          {
                parent::__construct();
                $this->load->model('categorylist_model');
                $this->load->helper('text');
          }
            public function index()
            {
            $this->load->helper('url');
            $data = array('sitename'=>'Title', 'page_title' => 'Categories');
          $data['categories'] = $this->categorylist_model->get_categoryads();
          $this->load->view('templates/header', $data);
            $this->load->view('templates/navbar');
            $this->load->view('templates/breadcrumbs');
            //$this->load->view('templates/category_selector');
            $this->load->view('content_categories');
            $this->load->view('templates/footer');
          }
}

I am now not to sure how to add the extra query to the Controller? Or should I add it inside the model itself and how?

I tried to add the function to the controller as follow (Not correct):

class Categories extends CI_Controller {
          public function __construct()
          {
                parent::__construct();
                $this->load->model('categorylist_model');
                $this->load->helper('text');
          }
            public function index()
            {
            $this->load->helper('url');
            $data = array('sitename'=>'Title', 'page_title' => 'Categories');
          $data['categories'] = $this->categorylist_model->get_categoryads();
          $this->load->view('templates/header', $data);
            $this->load->view('templates/navbar');
            $this->load->view('templates/breadcrumbs');
            //$this->load->view('templates/category_selector');
            $this->load->view('content_categories');
            $this->load->view('templates/footer');
          }
          public function tLink( $aid ) {  
          $ci=& get_instance();
          $ci->load->database(); 

          $sql ="SELECT adfields.f_value AS fvalue FROM adfields
                JOIN field_name ON field_name.id=adfields.f_id
                WHERE adid='$aid' GROUP BY adfields.f_id ORDER BY field_name.keyf ASC"; 

          $query = $ci->db->query($sql);
          $row = $query->row();
          return $row->fvalue;
          }

}

and calling it in the with tLink($adid) but gives me Fatal error: Call to undefined function tLink()

Answer:

Your PHP Error mentioned in comments is correct, either you're:

• Passing through an array instead of a string for $aid For example, you're passing through array( 0 => 45 ) instead of 45.
• Attempting to echo the results, which is a an array/object. Instead, use Code Igniter's row() command, then cherry pick the result you require.

---

function tLink( $aid ) {  
    $ci=& get_instance();
    $ci->load->database(); 

    $sql ="SELECT adfields.f_value AS fvalue FROM adfields
          JOIN field_name ON field_name.id=adfields.f_id
          WHERE adid='$aid' GROUP BY adfields.f_id ORDER BY field_name.keyf ASC"; 

    $query = $ci->db->query($sql);
    $row = $query->row();
    return $row->fvalue;
}

Lastly, it's better to load this model within the Controller rather than having to require it at the helper(Normally used for the view functions)

/**
 *
**/
class Link_model extends CI_Model { 

    public function getLink( $aid ) { 
          $sql ="SELECT adfields.f_value AS fvalue FROM adfields
                 JOIN field_name ON field_name.id=adfields.f_id
                 WHERE adid='$aid' GROUP BY adfields.f_id ORDER BY
                 field_name.keyf ASC"; 

         $query = $ci->db->query($sql);
         $row = $query->row();
         return $row->fvalue;
    }
}

---

/**
 *
**/
class Categories extends CI_Controller {

    public function index() {
        $this->load->model( 'Link_model' );

        $categories = array();
        $getCategoryIds = $this->categorylist_model->get_categoryads();

        foreach( $getCategoryIds as $aid ){
             $categories = array(
                 "id"   => $aid,
                 "link" => $this->link_model->getLink( $aid )
             );
        }

        $data = array(
            "categories" => $categories
        );

        $this->load->view('templates/header', $data );
    }

}

---

Now finally in your view:

<div class="container">
    <?php if ( isset( $categories ) && count( $categories ) > 0 ): ?>
        <?php foreach( $categories as $category ): ?>
            <?=( $category["link"] )?>
        <?php endforeach; ?>
    <?php endif; ?>
</div>




Read More