php function not returning all results from a MySQL query in a foreach

Question

Hey guys I have a little issue with a function that retrieves data from a MySQL Database and then I iterate over the results with a foreach loop, checking a value to see if it is null and if it is, replacing it with another value.

The problem with this function is this, that after returning the data I'm only able to view one record retrieved from the database. Probably something simple but it's beyond me.

I would like to do this before passing it to the controller or view. Maybe this isn't possible with the foreach loop? What am I missing?

Here is an example of my code.

public function get_basic_user_data(){
    $sql = 'SELECT Account.First_Name, Account.Last_Name, Account.User_Name, Profile_Photos.Thumb_Url 
            FROM Account 
            LEFT JOIN Profile_Photos ON Account.idAccount = Profile_Photos.Account_Id 
            AND Profile_Photos.Active = 1
            WHERE Account.idAccount != ?';
    $account_id = $this->get_account_id();
    $data = $this->db->query($sql, $account_id);

    foreach($data->result() as $row){

            if($row->Thumb_Url == NULL){
                $image = base_url().'assets/images/no_photo_thumb.png';
            }else{
                $image = $row->Thumb_Url; 
            }


    $new_data = new stdClass;
    $new_data->First_Name = $row->First_Name;
    $new_data->Last_Name = $row->Last_Name;
    $new_data->User_Name = $row->User_Name;
    $new_data->Thumb_Url = $image;

    }   

    return $new_data;

}

Hopefully someone can help me with this? Thanks!

Benjamin Paap · Accepted Answer · 2013-01-25 08:02:33Z

1

At the moment you are just returning the last data row. Change your code like this to return an array of all your rows from that function:

$rows = array()
foreach($data->result() as $row){

    if($row->Thumb_Url == NULL){
        $image = base_url().'assets/images/no_photo_thumb.png';
    }else{
        $image = $row->Thumb_Url; 
    }


    $new_data = new stdClass;
    $new_data->First_Name = $row->First_Name;
    $new_data->Last_Name = $row->Last_Name;
    $new_data->User_Name = $row->User_Name;
    $new_data->Thumb_Url = $image;

    $rows[] = $new_data;
}   

return $rows;

This way every row returned from the database will be added to an array named $rows. At the end you have to return your new array.

answered Jan 25, 2013 at 8:02

Benjamin Paap

2,7442 gold badges24 silver badges33 bronze badges

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

1 Comment

Kyle Coots Over a year ago

Awesome! Thanks so much, this worked great! lol should have know it was something simple.

hank · Accepted Answer · 2013-01-25 08:03:31Z

0

You are overwriting $new_data each iteration. Try this

$new_data = new stdClass
...
$all_data[] = $new_data;

Instead of checking for null value in the code, you could just use a IFNULL statement in the SQL query, this does separate the logic a bit but it might just be worth it in this case.

answered Jan 25, 2013 at 8:03

hank

3,7681 gold badge26 silver badges37 bronze badges

Comments

Eike Pierstorff · Accepted Answer · 2013-01-25 08:05:42Z

0

The function returns only the last row in the result because the new_data variable is overwritten in every step of your loop. Declare new_data an array at the start of your function and add rows as array elements

...
$new_data[] = new stdClass;
...

answered Jan 25, 2013 at 8:05

Eike Pierstorff

32.8k4 gold badges48 silver badges66 bronze badges

Comments

ejamadsen · Accepted Answer · 2013-01-25 08:11:55Z

Each iteration of the foreach overwrites $new_data so in the end when the function returns, only the last fetched row will be returned. To return more than one row you could store all the rows in an array and then return the array in the end. It would look something like this:

public function get_basic_user_data(){
    $sql = 'SELECT Account.First_Name, Account.Last_Name, Account.User_Name, Profile_Photos.Thumb_Url 
            FROM Account 
            LEFT JOIN Profile_Photos ON Account.idAccount = Profile_Photos.Account_Id 
            AND Profile_Photos.Active = 1
            WHERE Account.idAccount != ?';
    $account_id = $this->get_account_id();
    $data = $this->db->query($sql, $account_id);

    $data = array();
    foreach($data->result() as $row){

        if($row->Thumb_Url == NULL){
            $image = base_url().'assets/images/no_photo_thumb.png';
        }else{
            $image = $row->Thumb_Url; 
        }


        $new_data = new stdClass;
        $new_data->First_Name = $row->First_Name;
        $new_data->Last_Name = $row->Last_Name;
        $new_data->User_Name = $row->User_Name;
        $new_data->Thumb_Url = $image;

        $data[] = $new_data;
    }   

return $data;

}

To be able to use this function you have to change the code that uses it to loop through the array of objects.

Collectives™ on Stack Overflow

php function not returning all results from a MySQL query in a foreach

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related