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 >
            <td ><?php echo $value['book_id'] ?></td>
            <td ><?php echo $value['book_title'] ?></td>
            <td ><?php echo $value['section_name'] ?></td>
            <td ><?php echo $value['author_name'].','.$value['author_name'] ?> 
          </td>
            <td ><?php echo $value['book_serial'] ?></td>
            <td ><?php echo $value['book_qty'] ?></td>
            <td >

            <a href="<?php echo base_url('Admin/Editbook/'.$value["book_id"]) ?>"><button 
           ><i ></i> Edit</button> 
            <a href="<?php echo base_url('Admin/Deletebook/'.$value["book_id"]) ?>">  <button 
           ><i ></i> Delete</button>
            </td>
            </tr>
            <?php endforeach ?>
            </tbody>

CodePudding user response：

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 ><?php echo $value['concat_names'] ?></td>

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

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