How can I write subqueries in codeigniter on view page?

Question

I am new to codeigniter. Having problem in making queries in it. How can I write following query in view page of codeigniter?

select *
  from registration_status
 where registtration_card_id = (select Max(registration_card_id)
                                  from registration_status
                                 where registration_id = 67);

registration_card_id increments automatically and registration_id is fetched from registration table.

Possible duplicate of [subquery in codeigniter active record](https://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record) — Muhammed Imran Hussain, Sep 15 '17 at 05:18

score 0 · Answer 1 · answered Sep 15 '17 at 06:12

0

to that you need to load instance of CI into view .Just follow the step

$ci = & get_instance();
$ci->db->query("Your custome query");

answered Sep 15 '17 at 06:12

Rajat Masih

537
1
6
19

score 0 · Answer 2 · answered Sep 15 '17 at 07:24

No, no, no.

First of all, you don't write a query inside a view page of CodeIgniter.

You must do it in Model, that's why CI is MVC framework.

Second, you can utilised database View. If you are using MySQL, you can create a View like:

CREATE VIEW last_card_id AS
select * 
from registration_status

Third, you can use aggregate function in the first place without using sub-query, so the View will already contain the maximum registration_card_id based on registration_id. So we edit the View above:

CREATE VIEW last_card_id AS
selet max(registration_card_id) as 'max_id'
from registration_status
group by registration_id

then in your model file you can call the model:

public function get_max_card_id($registration_id){
    $this->db->where('registration_id',$registration_id);
    return $this->db->get('last_card_id')->row_array();
}

then in your controller:

public function max_card(){
    $data['registration'] = $this->registration_model->get_max_card_id($registration_id);
    $this->load->view('view_page',$data);
}

in view file:

<?php echo $registration['max_id'];?>

score 0 · Answer 3 · answered Sep 15 '17 at 08:04

Try this

$this->db->select("*")
    ->from("registration_status")
    ->where("registtration_card_id = (select Max(registration_card_id) from registration_status where registration_id = 67 )",false,false)
            ->get()->result();

This is using active records

How can I write subqueries in codeigniter on view page?

3 Answers3