How to echo and retrive multiple rows with php

February 20, 2022

First off all hello to you all. The problem is I am making an ajax call from javascript to php file. And in that php file there is a prepared statement which returns 15 rows. I need to json_encode() these rows and send back to the javascript file so that I can use all of these information in each row which are info about users’ profile. What’s wrong with my code? I call fetch_assoc() constantly until all of the rows are processed yet when I display it on the console it only shows one row.

This is get_info.php:

 // Get the profile info
    $stmt = $conn->prepare("SELECT teachers.fname, teachers.lname, profile.img_url, profile.gender, profile.introduction, profile.city, profile.preference, profile.keyword FROM profile INNER JOIN teachers ON profile.user_id = teachers.id WHERE profile.keyword=? ORDER BY RAND() LIMIT 15");
    $stmt->bind_param("s", $keyword);
    $stmt->execute();
    $result = $stmt->get_result(); // get the mysqli result
    $resultArray = [];
    if ($result->num_rows > 0) 
    {
        while ($row = $result->fetch_assoc()) 
        {   
            $resultArray = $row;    
        }
        $result = json_encode($resultArray);
        echo $result;
    }

And this is the javascript file where the ajax request is:

$(document).ready(function() 
{
  $.getJSON("get_info.php", function(result)
  {
    console.log(result);
  }
)})

>Solution :

You keep overwriting $resultArray every time you loop, so you’ll only ever see the last row returned by the query.

You need to make assign $row to a new element of $resultArray each time you loop – then adds rows to the array, instead of overwriting the variable with a single row.

It’s very simple to do that: