Run query within foreach loop in Codeigniter

Question

Basically i'm trying to build a comment system. The user looks at a photo on the site, and then can view all the comments made by other members.

Each comment will be looped out using a foreach (currently working fine), but what I need to do is then run a seperate query for each comment to get the user details of the user who posted it. These details are stored on a seperate database (otherwise i'd just do a join).

My model has this in it so far:

public function get_comment($id)
{
    $db_photos = $this->load->database('photos', TRUE);
    $db_photos->select('id, comment, userid, photoid');
    $db_photos->from('comments');
    $db_photos->where('photoid', $id);

    return $db_photos->get()->result();
}

And here's the controller:

public function view($id)
    {   

        $data['comment'] = $this->viewphoto_model->get_comment($id);
        if (empty($data['comment'])) { show_404(); }

        $this->load->view('templates/header', $data);
        $this->load->view('viewphoto/viewphoto', $data);
        $this->load->view('templates/footer', $data);
    }

And then the view:

<?php foreach ($comment as $comments): ?>
        <div class="ViewPhoto-CommentsBox">
        <? echo $comments->comment; ?>
        </div>
        <?php endforeach ?>

So basically I need to grab the 'userid' value from each comment and then run a query on the 'users' database to get the user details for each comment posted.

Any help is most appreciated :)

EDIT:

Still not working, here's latest version.

Controller:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Viewphoto extends CI_Controller {


    public function __construct()
    {
        parent::__construct();
        $this->load->model('viewphoto_model');
    }


    public function view($id)
    {
        $data['photo'] = $this->viewphoto_model->get_photo($id);
        if (empty($data['photo'])) { show_404(); }

        $data['user'] = $this->viewphoto_model->get_user($data['photo']->userid);
        if (empty($data['user'])) { show_404(); }


        $comment = $this->viewphoto_model->get_comment($id);
        if($comment->num_rows() > 0)
            {
                foreach ($comment->result() as $r)
                {
                    $data['reg'][$i]['comment']=$r->comment;
                    $data['reg'][$i]['id']=$r->id;           

                   // Get user details from user table
                    $user_profile = $this->viewphoto_model->get_comment_user($r->userid);
                    if($user_profile->num_rows() > 0)
                    {
                        foreach ($user_profile->result() as $row)
                        {
                            // user details whatever you have in your db.
                            $data['reg'][$i]['id']=$row->id;
                            $data['reg'][$i]['firstname']=$row->firstname;
                            $data['reg'][$i]['lastname']=$row->lastname;
                        }
                    }

                    $i++;
                }
            }

        $data['title'] = $data['photo']->title.' by '.$data['user']->firstname.' '.$data['user']->lastname;
        $data['meta_description'] = $data['photo']->description;
        $data['directory'] = 'sub';

        $this->load->view('templates/header', $data);
        $this->load->view('viewphoto/viewphoto', $data);
        $this->load->view('templates/footer', $data);
    }
}

Model:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Viewphoto_model extends CI_Model {


    public function get_photo($id)
    {
        $db_photos = $this->load->database('photos', TRUE);
        $db_photos->select('*');
        $db_photos->select("DATE_FORMAT(uploaddate, '%d/%m/%y') as uploaddate_formatted", FALSE);
        $db_photos->from('photos');
        $db_photos->where('approved', '1');
        $db_photos->where('id', $id);

        return $db_photos->get()->row();
    }

    public function get_user($userid)
    {
        $db_users = $this->load->database('users', TRUE);
        $db_users->select('id, firstname, lastname, email, type, type_staff, count_approved, count_sales, count_comments, count_editorial, featured, subscriber');
        $db_users->from('useraccounts');
        $db_users->where('id', $userid);

        return $db_users->get()->row();
    }


    public function get_comment($id)
    {
        $db_photos = $this->load->database('photos', TRUE);
        $db_photos->select('id, comment, userid, photoid');
        $db_photos->from('comments');
        $db_photos->where('photoid', $id);

        return $db_photos->get()->result();
    }


    public function get_comment_user($userid)
    {
        $db_users = $this->load->database('users', TRUE);
        $db_users->select('id, firstname, lastname');
        $db_users->from('useraccounts');
        $db_users->where('id', $userid);

        return $db_users->get();
    }

}

View:

<?php foreach ($reg as $comments): ?>
        <div class="ViewPhoto-CommentsBox">
        <? echo $comments['comment']; ?> by <? echo $comments['firstname'];?>
        </div>
        <?php endforeach ?>

I have no experience with codeigniter but querying within a loop is I believe generally a bad idea, Personally I'd do something like get the user details as you have with comments then use some nested loop to 'join' the two. — T I
– T I, Commented Jan 3, 2012 at 11:50
The reason for the query of the loop is so that if a user changes their user details it changes for all comments they have made throughout the site. I can't put their user details into each row of the comment database becuase it means it won't change dynamically. Unless when they change thheir details I write something to change all comments in the entire database, but that sounds a bit crazy. — Noah Goodrich
– Noah Goodrich, Commented Jan 3, 2012 at 11:54
Hi, I think you have misinterpreted what I have said, I am not suggesting you change the data/database in anyway, rather obtain two models in say your controller, parse them to the view where you could loop through them appropriately. Alternatively play with your comment model to obtain/map the information from the other db. — T I
– T I, Commented Jan 3, 2012 at 12:07
Hi so at the bottom of codeigniter.com/user_guide/database/connecting.html it shows an example of multiple database connections. As I have said I have no experience of codigniter but it looks like a resolution could be handled in your get_comment method quite easily — T I
– T I, Commented Jan 3, 2012 at 12:21

srbhbarot · Accepted Answer · 2012-01-03 14:29:44Z

4

change your controller like this.

 public function view($id)
        {       
              $comment= $this->viewphoto_model->get_comment($id);

              if($comment->num_rows() > 0)
                {
                    foreach ($comment->result() as $r)
                    {
                        $data['reg'][$i]['comment']=$r->comment;
                        $data['reg'][$i]['id']=$r->id;           

                       // Get user details from user table
                        $user_profile=$this->viewphoto_model->get_user_profile_details($r->userid);
                        if($user_profile->num_rows() > 0)
                        {
                            foreach ($user_profile->result() as $row)
                            {
                                // user details whatever you have in your db.
                                $data['reg'][$i]['name']=$row->name;
                                $data['reg'][$i]['gender']=$row->gender;
                                $data['reg'][$i]['phone_no']=$row->phone_no;
                            }
                        }

                        $i++;
                    }
                }
            $this->load->view('templates/header', $data);
            $this->load->view('viewphoto/viewphoto', $data);
            $this->load->view('templates/footer', $data);
        }

View file: you can use details like below.

<?php if(isset($reg)) { foreach ($reg as $comments): ?>
        <div class="ViewPhoto-CommentsBox">
        <? echo $comments['comment']; ?>
         <? echo $comments['name']; // user detail ?>
        </div>
        <?php endforeach ?>
        <? } ?>

edited Jan 3, 2012 at 14:29

answered Jan 3, 2012 at 12:12

srbhbarot

1,31712 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Noah Goodrich Over a year ago

Hmmm...looks good but doesn't seem to work. I just get a blank white screen. I have updated my original question with the full controller and model.

srbhbarot Over a year ago

change in your model function get_comment(). return $db_photos->get(); instead of return $db_photos->get()->result();

Noah Goodrich Over a year ago

Now I get the following error: A PHP Error was encountered Severity: Notice Message: Undefined variable: i Filename: controllers/viewphoto.php Line Number: 27. Also one for line 28 as well, same error.

Noah Goodrich Over a year ago

Actually ignore my previous comment. It now gives this error: A PHP Error was encountered Severity: Notice Message: Undefined variable: reg Filename: viewphoto/viewphoto.php Line Number: 110

Noah Goodrich Over a year ago

And this one too: A PHP Error was encountered Severity: Warning Message: Invalid argument supplied for foreach() Filename: viewphoto/viewphoto.php Line Number: 110

|

Joffrey Faroux · Accepted Answer · 2013-04-03 13:33:39Z

that's working ! :

$searchquery = "SELECT * FROM your_table";
        $resultquery = $this->db->query($searchquery);
        $i = 0;
        if($resultquery->num_rows() > 0)
                {
                    foreach ($resultquery->result() as $query)
                    {
                        $data['reg'][$i]['id']=$query->id;
                        $data['reg'][$i]['name']=$query->name;
                        $data['reg'][$i]['pers']=$query->pers;
                        $data['reg'][$i]['sex']=$query->sex;
                        $i++;
                    }
            }

And for the view :

<?php if(isset($reg)) { foreach ($reg as $chambres): ?>
                    <li>
                        <input type="hidden" value="<?php echo $query['id']; ?>" />
                        <input type="text" value="<?php echo $query['name']; ?>" placeholder="numero de la chambre" />
                        <input type="text" value="<?php echo $query['pers']; ?>" placeholder="Nombre de personne" />
                        <textarea><?php echo $query['sex']; ?></textarea>
                    </li>
                <?php endforeach ?>
                <?php } ?>

Thanks srbhbarot.

Collectives™ on Stack Overflow

Run query within foreach loop in Codeigniter

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related