I have a question regarding db queries inside of a foreach loop. I'm making a guestbook for users on a site. I store "user_id", "owner_id" "message" in the "guestbook" table. I loop these records to display the posts on the page. I also need the "username" from the "users" table, so I am performing looped queries where id = user_id by calling a whois_model method directly from my view.
<?php foreach ($records as $record) : ?>
From: <?=anchor('profile/view/' . $record->user_id . '', $this->whois_model->_whois($record->user_id)); ?>
<p><?=$record->message; ?></p>
<?php endforeach; ?>
Is it better to store the username in the guestbook table directly, or is there a better approach to avoid running multiple queries?
Don't denormalize your data unless you have a good reason to. Not only you are wasting space, but if you also have a username column in your records table, what happens when a user changes their name?
The better way to do this is to have your model or your controller retrieve the username automatically whenever you plan to use it. This can be done using a join, eg:
$this->db->select('username, message');
$this->db->from('guestbook');
$this->db->join('users', 'guestbook.user_id = users.user_id');
$query = $this->db->get();
Remember that the view should only be used to display information; you shouldn't have to fetch anything from the database (eg: via another model) in the view.