php function not returning all results from a MySQ

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!

Answer 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.

Answer 2

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.

Answer 3

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;
...

Answer 4

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.