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
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>
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>