CODEWITHSUNDEEP
mysqlcodeignitercodeigniter-2
Xubayer pantho
Xubayer pantho

Reputation: 329

Select and count record from database using codeigniter active query method

I have two tables in my database "tbl_room_info" & "tbl_room_image".

tbl_room_info structure:

room_id | room_name | room_details

tbl_room_image structure:

room_id | room_image_path

now i want get all room information from my tbl_room_info table and also count my room image number from tbl_room_image table. for example consider tbl_room_info has 1 record having room_id '2' and tbl_room_image also have same room_id who has 5 room images. i want to achieve all information in my view page like this:

Room Name | Room Details | Room Image Number

 1          room details     5

 2          room details     6

my problem is that i can get this by normal mysql query like:

     SELECT `tbl_room_info`.*, COUNT('tbl_room_image.*') AS imc FROM (`tbl_room_info`, `tbl_room_image`) WHERE `tbl_room_info`.`room_id` = tbl_room_image.room_id GROUP BY `tbl_room_info`.`room_id`

but i want this codeigniter active record format. here is my query it's not worked properly. plz help

    $this->db->select('tbl_room_info.*', 'count(tbl_room_image.*) as imc');
    $this->db->from('tbl_room_info, tbl_room_image');
    $this->db->where('tbl_room_info.room_id = tbl_room_image.room_id');
    $this->db->group_by('tbl_room_info.room_id');
    return $this->db->get()->result();

here is my view:

    <tbody>
    <?php foreach($roomlists as $roomlist){?>
        <tr>
            <td><?php echo $roomlist->room_type;?></td>
            <td><?php echo $roomlist->room_name;?></td>
            <td><?php echo $roomlist->room_details;?></td>
            <td>Total Pictures <span class="badge"><?php echo $roomlist->imc;?></span></td>
        </tr>
    <?php }?>
    </tbody>

Upvotes: 0

Views: 1332

Answers (1)

Parag Tyagi
Parag Tyagi

Reputation: 8960

Use Active Record's JOIN - $this->db->join();. Try below - 

$this->db->select('tbl_room_info.*, count(tbl_room_image.room_image_path) as imc', FALSE);
$this->db->from('tbl_room_info');
$this->db->join('tbl_room_image', 'tbl_room_info.room_id = tbl_room_image.room_id');
$this->db->group_by('tbl_room_info.room_id');
$query = $this->db->get();

echo $this->db->last_query(); exit;  // print resulting query
print_r($query->result()); exit;     // check resulting array

if($query->num_rows() > 0)
    return $query->result();
return false;


Edit (explanation of the answer):

The second parameter (optional) in $this->db->select(); is actually a boolean value (should be either TRUE/FALSE). By default the value is TRUE. TRUE means protect whatever inside the select with backticks. FALSE means the opposite i.e prevent protecting it from backticks. Why we did this, since we were adding COUNT in the select as well, it is getting protected by backticks i.e COUNT(tbl_room_image.room_image_path) as imc which is definitely no such column found error.

Upvotes: 2

Related Questions