[FIXED] how fetch multiple rows from mysql using prepared statements

Issue

With prepare statement i am fetching only one row , i tried while to loop all rows but is only one row witch is being fetched.please assist me on how i fetch all rows from database instead of one

PHP function : .....

  public function StudentsOfParent($mobile){
 $stmt = $this->conn->prepare("SELECT
                         a.id,
                         a.name, 
                         a.mobile, 
                         c.id as sutdentId, 
                         c.user_id, 
                         c.full_name, 
                         c.school, 
                         c.level,
                         c.year,
                         c.id                    
                         from users a 
                         join students c 
                         on a.id = c.user_id where a.mobile= ?");

    $stmt->bind_param("i", $mobile);
    if ($stmt->execute()) {

   while ($user = $stmt->get_result()->fetch_assoc())
            {
        $stmt->close();

            // return user's results
            return $user;
                }
            }
        else {
        return NULL;
    }
}
  .....

External php file to access above function : retrieve.php:

  <?php 
  include './DbHandler.php';
   $db = new DbHandler(); 
    // json response array
  $response = array("error" => FALSE);
     if (isset($_POST['mobile'])){
  $mobile = $_POST['mobile']; 
   $user = $db->StudentsOfParent($mobile);
    if ($user != false) {            
      // user found successfully
        $response["error"] = FALSE;
        $response["user"]["id"] = $user["id"];
 $response["user"]["sutdentId"] = $user["sutdentId"];
 $response["user"]["user_id"] = $user["user_id"];
 $response["user"]["full_name"] = $user["full_name"];
 $response["user"]["school"] = $user["school"];
 $response["user"]["level"] = $user["level"];
 $response["user"]["year"] = $user["year"];
        // $response["user"]["photo"] = $user["photo"];
                     echo json_encode($response);
         // $json = json_encode($response);
    }    else {
    // user is not found with the credentials
    $response["error"] = TRUE;
    $response["error_msg"] = "Sorry we could not find you !";
    echo json_encode($response);
   }
    } 
   else {
    // required post params is missing
   $response["error"] = TRUE;
    $response["error_msg"] = "Required parameter is missing!";
   echo json_encode($response);
   }
  ?>

Solution

With prepare statement i am fetching only one row , i tried while to loop all rows but is only one row witch is being fetched.

That's because you're returning $user in the first iteration of while loop itself, the loop won't even go on for the 2nd iteration. Plus, you're also closing the statement object $stmt->close(); in the first iteration itself. Instead your code block should be like this:

// your code
$stmt->execute();
$result = $stmt->get_result();
$usersArr = array();
while ($user = $result->fetch_assoc()){
    $usersArr[] = $user;
}
return $usersArr;

Now the returned $usersArr array is a multidimensional array, which you need to appropriately loop through to get all users' details. If you want to see the complete array structure, do var_dump($usersArr);.

Answered By - Rajdeep Paul

Answer Checked By - Clifford M. (PHPFixing Volunteer)