Select and count record from database using codeigniter active query method

Question

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>

Parag Tyagi · Accepted Answer · 2014-09-15 09:02:38Z

2

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.

edited Sep 15, 2014 at 9:02

answered Sep 13, 2014 at 9:00

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

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

9 Comments

Xubayer pantho Over a year ago

it's not working. I cannot get the number of image count value in my view page that is "count(tbl_room_image.*) as imc" value from this query part.

Parag Tyagi Over a year ago

I got the error. Replace this count(tbl_room_image.*) as imc with this count(tbl_room_image.room_image_path) as imc. I'm sure it'll work. Check edits.

Xubayer pantho Over a year ago

i get this error in my view: A PHP Error was encountered Severity: Notice Message: Undefined property: stdClass::$imc

Xubayer pantho Over a year ago

Array ( [0] => stdClass Object ( [room_id] => 1 [room_type] => 1 [room_name] => Queen Room [room_details] => Queen Size bed Sleeps up to 2 ) [1] => stdClass Object ( [room_id] => 2 [room_type] => 1 [room_name] => Two Double room [room_details] => Two double beds Sleeps up to 2 )) still not getting Number of image form a particular room_id

Parag Tyagi Over a year ago

Its strange you are not getting imc. Can you print the query? Check edits.

|

Collectives™ on Stack Overflow

Select and count record from database using codeigniter active query method

tbl_room_info structure:

room_id | room_name | room_details

tbl_room_image structure:

room_id | room_image_path

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

tbl_room_info structure:

room_id | room_name | room_details

tbl_room_image structure:

room_id | room_image_path

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related