+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem with Group_by() (/showthread.php?tid=19886)
Problem with Group_by() - El Forum - 06-22-2009
[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
Problem with Group_by() - El Forum - 06-22-2009
[eluser]Thorpe Obazee[/eluser]
Check PHPmyadmin as to the correct sql statement to use then convert to AR.
Problem with Group_by() - El Forum - 06-22-2009
[eluser]tonanbarbarian[/eluser]
you have to group by ALL of the fields except for the count field