Query in a loop how to get result array in Codeigniter

Question

i am trying to fetch result of a query that is in a loop but i cant get all the records returned by query that is in the loop please guide me.

  $qry = '';
    $qry = "SELECT    
            `user_id`  FROM 
            `view_property_user_lab`";
    $qry .= " WHERE property_id = '" . $propId . "'";
    $result = $this->db->query($qry);
    $result_count = $result->result_array();
    foreach ($result_count as $row)
    {
        $user_name_qry = "SELECT
                                 `name`
                                    FROM USER
                                    WHERE user_id =  '" . $row['user_id'] . "'";
                                   $user_name_result = $this->db->query($user_name_qry);
    }

    $result_count_user = $user_name_result->result_array();
    print_r($result_count_user);

This is the dump of array

 Array ( [0] => Array ( [name] => abc) )

queries in a loop is very bad idea, especially since your loop will grow with each new user. — tereško
– tereško, Commented Feb 8, 2012 at 6:03

Damien Pirsy · Accepted Answer · 2012-02-08 06:23:31Z

2

I think your problem is that you're using a foreach loop, but everytime you overwrite the same variable ($user_name_result) and you use only the last one assigned to get the data from the result resource (since you're calling that outside the loop!).

But all in all, as tersko said in the comments, doing queries in loops isn't a good thing (hitting your db the more your users increase).

You can simplify everything with a JOINed query (just guessing your table schema here, though):

$sql = "SELECT u.name FROM view_property_user_lab AS v JOIN user AS u ON u.user_id = v.user_id WHERE v.property_id = ?";
$query = $this->db->query($sql,array($propID));

print_r($query->result_array());

answered Feb 8, 2012 at 6:23

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

Query in a loop how to get result array in Codeigniter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related