returning an array in Mysqli prepared statement

Question

I have function in my database class that returns the id of the people that are from the a specific country such as spain. But for some reason I only get one value, but there are many people with the same country. here is the function:

Class DbAb {
private $db;

public function sameCountry($country) {

    $query = "SELECT id FROM users WHERE country = ? ";
    $stmt = $this->db->prepare($query);
    $stmt->bind_param("s",  $country);
    if ($stmt->execute()) {

        $stmt->bind_result($sameCountry);
        $stmt->fetch();

        return $sameCountry;
    }
    return false;
}



}

$sameC = new DbAb();

$samePeople = $sameC->samecountry("spain");

print_r($samePeople);

Does anyone know how to return an array of results? I have tried to define the variable as an array but still doesn't work...

Answer 1

The bind_result($var) + fetch() inserts a single row into the $var variable.

If you want to return an array of ids from your method, you need to first create an empty array, then for each row, insert into it.

eg. replace this:

$stmt->bind_result($sameCountry);
$stmt->fetch();
return $sameCountry;

with this:

$arr = array();
$stmt->bind_result($id);
while ( $stmt->fetch() ) {
    $arr[] = $id;
}
return $arr;