mysqli_fetch_assoc only returning one result, even though there is more

Question

When I ran the following query in PHPMyAdmin, it returned the correct number of results. However when I try to echo the results in PHP, it only outputs one result, even when there is more thn one. How do I fix this so that every result is displayed?

$sql1 = "SELECT userFirstname FROM users WHERE userID IN (SELECT userID FROM note_editors WHERE noteID = (SELECT noteID FROM notes WHERE uniqueID = ?))";
$stmt1 = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($stmt1, $sql1)) {
    header("Location:  note-premium.php?error=sql");
    exit();
}          
                    
else {
    mysqli_stmt_bind_param($stmt1, "s", $unique);
    mysqli_stmt_execute($stmt1);
    $result1 = mysqli_stmt_get_result($stmt1);
    while ($row1 = mysqli_fetch_assoc($result1)) {
        $names = $row1['userFirstname'];
    }
}
                
echo($names);

Second attempt: I tried creating an array. But this just outputs the word array and the error message, "Notice: Array to string conversion". Why?

$sql1 = "SELECT userFirstname FROM users WHERE userID IN (SELECT userID FROM note_editors WHERE noteID = (SELECT noteID FROM notes WHERE uniqueID = ?))";
$stmt1 = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($stmt1, $sql1)) {
    header("Location:  note-premium.php?error=sql");
    exit();
}          

else {
    mysqli_stmt_bind_param($stmt1, "s", $unique);
    mysqli_stmt_execute($stmt1);
    $result1 = mysqli_stmt_get_result($stmt1);
    $column = array();
    while ($row1 = mysqli_fetch_assoc($result1)) {
        $column[] = $row1['userFirstname'];
    }
}

echo($column);

You're overwriting `$names` each time - you need to build up an array of values instead — iainn, Jun 28 '20 at 09:43
@will_kelly14 You don't know what does it mean `Notice: Array to string conversion`. — KUMAR, Jun 28 '20 at 10:02
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Jun 28 '20 at 12:55

score 1 · Accepted Answer · answered Jun 28 '20 at 09:49

As you're looping through the results and storing the value of the column 'userFirstName' in $names, you're overwriting the previous value stored in it.

You've got two options - display the value as you're looping through the results, or store the value in an array and then display that afterwards.

Option 1 - display the value as you're looping through the results:

while ($row1 = mysqli_fetch_assoc($result1)) {
   echo $row1['userFirstname'];
}

Option 2 - store the values in an array and display that after the loop

$names = [];
while ($row1 = mysqli_fetch_assoc($result1)) {
   $names[] = $row1['userFirstname'];
}

foreach($names as $name) {
   echo '<p>'.$name.'</p>';
}

Obviously you can customise how you want to loop through the array values and display them. I've wrapped each value in a <p> tag so that they display on a new line. If you just want to display the unformatted contents of the array, use print_r($names)

Why are you using `while` in one place and `foreach` in another. Why are you being inconsistent? — Dharman, Jun 28 '20 at 12:56

mysqli_fetch_assoc only returning one result, even though there is more

1 Answers1