SQL and CodeIgniter


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



queries belong in the model so do this:

function get_books()
  $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 :)

By : stef

Perform the loop inside the controller like:

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

By : rayed

This video can help you solving your question :)
By: admin