[eluser]sovandy[/eluser]
Hello everyone,
Table lib_book is related to lib_categories,lib_borrowings,and lib_authors.
I want to see the available books in stock in book_management.php, so I select data from lib_book inner join with lib_categories,lib_borrowings,and lib_authors. I count and group_by the book_id. However, I can view only the books that are borrowed.
<code>
function get_all_book(){
$array=array();
$this->db->select('lib_books.boo_id,
lib_categories.cat_name AS cat_name,
lib_authors.aut_name AS aut_name,
boo_title,
boo_description,
boo_code,
boo_published_date,
boo_unit,
COUNT(lib_borrowings.boo_id)AS borrowed,
boo_date_created,
boo_date_modified,
lib_books.status',FALSE);
$this->db->from('lib_books');
$this->db->join('lib_categories','lib_books.cat_id=lib_categories.cat_id');
$this->db->join('lib_authors','lib_books.aut_id=lib_authors.aut_id');
$this->db->join('lib_borrowings', 'lib_books.boo_id = lib_borrowings.boo_id', 'inner');
$this->db->group_by('lib_borrowings.boo_id');
$query=$this->db->get();
if($query->num_rows>0){
foreach($query->result_array() as $row){
$array[]=$row;
}
}
$query->free_result();
return $array;
}
</code>
I display in view like this:
Available=boo_unit-borrowed
I know I have a wrong point, but I can not solve it now. Please help me! :down:
lib_books
