CODEWITHSUNDEEP
codeigniter
Noah Goodrich
Noah Goodrich

Reputation: 227

Run query within foreach loop in Codeigniter

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

Upvotes: 0

Views: 13194

Answers (2)

Joffrey Faroux
Joffrey Faroux

Reputation: 1

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.

Upvotes: 0

srbhbarot
srbhbarot

Reputation: 1317

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 ?>
        <? } ?>

Upvotes: 4

Related Questions