CODEWITHSUNDEEP
phpmysqlcodeigniter
Rob
Rob

Reputation:

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

Upvotes: 1

Views: 768

Answers (2)

stef
stef

Reputation: 27749

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

Upvotes: 0

rayed
rayed

Reputation: 655

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.

Upvotes: 4

Related Questions