SQL and CodeIgniter

Question

Hello All.

I currently am creating a book inventory system with CodeIgniter (am new to CodeIgniter) and I would like each of the books to have to have tags.

Currently, I have 4 tables:

1. Books
2. Tags
3. BooksTags (matches bookid to tagid)
4. Collections (series collection)

In the controller for the main view which will show all the books, I call this:

$this->db->select('*');  
$this->db->from('books'); 
$this->db->join('collections', 'collections.collectid= books.collectionid');
$data['query'] = $this->db->get();

The join helps me get the collection each book belongs too.

So in the view, I loop through the query and get all the books and display them in a table format. What I am hoping to accomplish is to add a row under each book and put the tags. My SQL skills are lacking and I would normally just put a bunch of select statements, but I want to do all the work from the controller. I attempted a couple of different things, but I am not sure how to get all the tags for each book as it loops through.

Any help would be greatly appreciated.

Thanks!

Answer 1

Perform the loop inside the controller like:

$this->db->select('*');  
$this->db->from('books b'); 
$this->db->join('collections c', 'c.collectid= b.collectionid');
$books = $this->db->get()->result;
foreach($books as $book) {
     $this->db->select('*');  
     $this->db->from('Tags t'); 
     $this->db->join('BooksTags bt', 't.tagid= bt.tagid');
     $this->db->where('bt.bookid', $book->bookid);
     $book->tags = $this->db->get()->result;
}
$data['books'] = $books;

Then from the view you could something like:

<?php foreach($books as $book): ?>
<?= $book->name ?>  has the following tags: 
  <?php foreach($book->tags as $tag): ?>
  <?= $tag->name ?>
  <?php endforeach; ?>
<?php endforeach; ?>

You should also consider moving all DB code to a model.

Answer 2

Hi Rayed,

Do you have an example of this code in a model? Thanks

Answer 3

queries belong in the model so do this:

function get_books() { $this->db->select('*');
$this->db->from('books b'); $this->db->join('collections c', 'c.collectid= b.collectionid'); return $this->db->get()->result; }

then in your controller you call this like:

$books = $this->Model__name->get_books();

foreach ...

and the code in the view stays the same :)