0

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);
Improve this question
5
  • 1
    You're overwriting $names each time - you need to build up an array of values instead Commented Jun 28, 2020 at 9:43
  • use mysqli_fetch_array($result1) Commented Jun 28, 2020 at 9:45
  • @will_kelly14 not echo($column); print_r($column); Commented Jun 28, 2020 at 9:57
  • @will_kelly14 You don't know what does it mean Notice: Array to string conversion. Commented Jun 28, 2020 at 10:02
  • You need to stop manually checking for errors. Please read: Should we ever check for mysqli_connect() errors manually? and Should I manually check for errors when calling “mysqli_stmt_prepare”? Commented Jun 28, 2020 at 12:55

1 Answer 1

Reset to default
1

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)

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

1 Comment

Why are you using while in one place and foreach in another. Why are you being inconsistent?