SQL and CodeIgniter

Question!

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!



Answers

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

By : stef


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.

By : rayed


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