Richard Downs Richard Downs - 1 month ago 8
MySQL Question

Why is this function only returning one (1st) value in what should be an array of 5?

I am running an SQL query when my user logs into my app to retrieve a list of 'communities' they are part of.

I am trying to test what values are being returned by pasting the login link directly into my address bar.

It should return an array of 5 entries, but it is only returning 1.

I have tested the SQL query in phpMyAdmin and it returns the correct result.

What am I doing wrong?

This is from

userLogin.php


$communities = array();
$communities = $dao->getCommunities($email);
echo json_encode($communities);


Which runs the following function in
MySQLDao.php


public function getCommunities($email){

$returnValue = array();
$sql = "SELECT communities.name \n"
. "FROM users \n"
. "join community_players \n"
. "on community_players.player_id=users.id \n"
. "join communities \n"
. "on communities.id=community_players.community_id \n"
. "WHERE users.user_email = '".$email."'";

$result = $this->conn->query($sql);
if($result != null && (mysqli_num_rows($result) >= 1)){
$row = $result -> fetch_array(MYSQLI_ASSOC);
if(!empty($row)){
$returnValue = $row;
}
}
return $returnValue;
}


This is currently returning
{"name":"EnclliffeT"}
in the browser.
However, there should be another 4 entries.

Answer

Your code is fetching only the first row. Replace these lines:

if($result != null && (mysqli_num_rows($result) >= 1)){
    $row = $result -> fetch_array(MYSQLI_ASSOC);
    if(!empty($row)){
        $returnValue = $row;
    }
}

with these:

if($result != null && (mysqli_num_rows($result) >= 1)){
    while($row = $result -> fetch_array(MYSQLI_ASSOC)){
       if(!empty($row)){
          $returnValue[] = $row;
       }
    }
}