how fetch multiple rows from mysql using prepared statements

Question

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);
   }
  ?>

$stmt->close(); - You are "closing" the statement after the first loop. — Paul Spiegel
– Paul Spiegel, Commented Jul 9, 2017 at 14:00
I removed $stmt->close(); , $user and i get this error : Fatal error: Call to a member function fetch_assoc() on boolean — gramanda grandish
– gramanda grandish, Commented Jul 9, 2017 at 14:05
Just skip the while loop and use $stmt->get_result()->fetch_all(MYSQLI_ASSOC) — Paul Spiegel
– Paul Spiegel, Commented Jul 9, 2017 at 14:10

Your Common Sense · Accepted Answer · 2022-05-09 19:46:41Z

2

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

edited May 9, 2022 at 19:46

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

answered Jul 9, 2017 at 14:09

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

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

2 Comments

gramanda grandish Over a year ago

Thank you it seems that this solution is going to work for me but i get an error which shows that all variables in retrieve.php are undefined from id to year help me to check why , i changed $response["user"]["id"] = $user["id"] to $response["usersArr"]["id"] = $usersArr["id"] but doesn't work too

Rajdeep Paul Over a year ago

@gramandagrandish As I said in my answer, you need to loop through $usersArr array to get all users' details. Here's the solution snippet for your reference: https://pastebin.com/QhMLXNux

Collectives™ on Stack Overflow

how fetch multiple rows from mysql using prepared statements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related