PHP MySQLi is just returning one row in JSON

问题:

I am working on the below code. Why am I getting only one row in my JSON output?

$items = [];
if ($stmt = $this->conn->prepare("SELECT * FROM $tbl")) {
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
         $items = $row;
     }
    $stmt->free_result();
    $stmt->close();
    }
    $this->conn->close();
}
echo json_encode($items);

回答1:

This happens because you are going through the rows and assigning each of them to $items:

while ($row = $result->fetch_assoc()) {
    $items = $row;
}

So after the loop is finished you end up with a variable which is a $row that was assigned during the last iteration of that loop.

You would need to push the values to an array like so:

$items[] = $row;

回答2:

$items[] = $row; will solve to problem.

回答3:

Try with array_push function:

$items = array();
if ($stmt = $this->conn->prepare("SELECT * FROM $tbl")) {
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
         array_push($items, $row);
     }
    $stmt->free_result();
    $stmt->close();
    }
    $this->conn->close();
}
echo json_encode($items);

According to Paolo Bergantino - What's better to use in PHP $array[] = $value or array_push($array, $value)? He says:

If you use array_push() to add one element to the array it's better to use $array[] = because in that way there is no overhead of calling a function.

回答4:

for solve your problem you must change this part of code:

while ($row = $result->fetch_assoc()) {
    $items = $row;
}

TO:

while ($row = $result->fetch_assoc()) {
    $items[] = $row;
}

But if you use PDO, I think it's better to do that change this part of code:

$items = [];
if ($stmt = $this->conn->prepare("SELECT * FROM $tbl")) {
    $stmt->execute();
    $result = $stmt->get_result();
    if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
         $items = $row;
     }
    $stmt->free_result();
    $stmt->close();
    }
    $this->conn->close();
}

TO:

if ($stmt = $this->conn->prepare("SELECT * FROM $tbl")) {
    $stmt->execute();
    $items = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

And remove while loop.

回答5:

The reason why you only get one row in JSON is because in your while loop you overwrite the same variable each time instead of pushing into an array. A simple fix would be to do:

while ($row = $result->fetch_assoc()) {
    $items[] = $row;
}

This can also be written using foreach loop.

foreach($result as $row) {
    $items[] = $row;
}

You don't need this while loop at all. You can make your code much simpler if you use fetch_all(). You can simply replace all your code from the question with:

$stmt = $this->conn->prepare("SELECT * FROM $tbl");
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($items);

fetch_all(MYSQLI_ASSOC) will give you all the rows in associative arrays.