Search code examples
phpcodeigniter

Codeigniter: E-Library, Display books with Authors


Good Day Maam/Sir. I need help in Displaying the all the books along with author. Since some books have 1 author I need to display the books along with all authors but it only display 1 author using group by even though the book have 2 or more than authors

The authors are tom and jerry but it only display tom

Table Authors Table Books

public function getbooks()
 {
   return $this->db->select('*')->from('tbl_books')
   ->join('tbl_section','tbl_books.section_id=tbl_section.section_id')
   ->join('tbl_authors','tbl_books.book_id=tbl_authors.book_id')
   ->group_by('tbl_authors.book_id')
    ->get()->result_array();

 }
View:
          <tbody>                              
             <?php foreach ($booklists as $value): ?>
            <tr class="odd gradeX">
            <td class="center"><?php echo $value['book_id'] ?></td>
            <td class="center"><?php echo $value['book_title'] ?></td>
            <td class="center"><?php echo $value['section_name'] ?></td>
            <td class="center"><?php echo $value['author_name'].','.$value['author_name'] ?> 
          </td>
            <td class="center"><?php echo $value['book_serial'] ?></td>
            <td class="center"><?php echo $value['book_qty'] ?></td>
            <td class="center">

            <a href="<?php echo base_url('Admin/Editbook/'.$value["book_id"]) ?>"><button 
           class="btn btn-primary"><i class="fa fa-edit "></i> Edit</button> 
            <a href="<?php echo base_url('Admin/Deletebook/'.$value["book_id"]) ?>">  <button 
           class="btn btn-danger"><i class="fa fa-trash"></i> Delete</button>
            </td>
            </tr>
            <?php endforeach ?>
            </tbody>

Solution

  • If i remember correctly when you group_by author's table book_id you will only have one result of book which each of them only have a single author name column. You can use group_concat to append the author names directly.

    $this->db->select("*, group_concat(author_name separator ', ') as concat_names")
         ....
         ->group_by('tbl_authors.book_id')
         ->get()->result_array();
    

    Then you can retrieve the author names using concat_names as such:

     <td class="center"><?php echo $value['concat_names'] ?></td>
    

    Also, mandatory PSA, you should use htmlspecialchar to avoid Cross-Site Scripting and issues later on.

     <td class="center"><?php echo htmlspecialchar($value['concat_names']) ?></td>