While looping in mysqli prepared statement

Question

Is it possible to do something like we do in normal mysqli queries in prepared statements in while lopping. For example

$sql = "SELECT * FROM users"*;
$query = mysqli_query($link, $sql);
while($row = mysqli_fetch_assoc($query)){
   echo $row['username'];
   echo $row['name'];
}

In prepared statements, it goes like this

$fetch_comments = $friend_zone->prepare("SELECT item_poster, comment FROM status_comments WHERE status_id = ? and item_poster = ?");
$fetch_comments->bind_param("is", $status__id, $item_poster);
$fetch_comments->execute();
$fetch_comments->store_result();
$fetch_comments->bind_result($item_poster_fetched, $comment_fetched);
while($fetch_comments->fetch(){
  echo $item_poster;
}

What i mean is i want to do the echo like so

echo $row['something'];

The solution i came up with now is to fetch them using bind result, then put them into an array inside the loop and then foo['bar']; or something of that sort. Is there a better way?

score 3 · Answer 1 · edited Sep 28 '21 at 20:52

3

Doesn't this work?

$fetch_comments = $friend_zone->prepare("SELECT item_poster, comment FROM status_comments       WHERE status_id = ? and item_poster = ?");
$fetch_comments->bind_param("is", $status__id, $item_poster);
$fetch_comments->execute();
$result = $fetch_comments->get_result();
while ($row = $result->fetch_assoc()) {

    echo $row['something'];

}

edited Sep 28 '21 at 20:52

Dharman

30,962
25
85
135

answered Mar 20 '12 at 09:40

ab_dev86

1,952
16
21

im gonna try this out, i thought this way would not work because in the manual the example was different... – Marshall Mathews Mar 20 '12 at 09:43
i dont have the mysqlnd driver, so is there any other solution? – Marshall Mathews Mar 20 '12 at 09:58
1

So in this case the $fetch_comments->get_result(); instruction fails... So probabily the solution you originally proposed works good enough... – ab_dev86 Mar 20 '12 at 10:27

Jeremy Harris · Answer 2 · 2012-03-20T09:53:24.200

1

~~You're almost there with your code. You just need to assign the value to $row within the while loop's condition check:~~

while( $row = $fetch_comments->fetch() ) {
   echo $row['somefield'];
}

The data is now bound to the $item_poster_fetched variable from what I understand...

while($fetch_comments->fetch())
{
    $item_poster_fetched['something'];
}

edited Mar 20 '12 at 09:53

answered Mar 20 '12 at 09:37

Jeremy Harris

24,318
13
79
133

thanks i was a bit confused as the while loop example in the manual was a tad bit different – Marshall Mathews Mar 20 '12 at 09:43
@cillosis `mysqli_stmt::fetch` returns a boolean, please read the question again. – xdazz Mar 20 '12 at 09:45
1

@xdazz Thanks for pointing that out, I was unaware of that behavior. I guess I've gotten too used to PDO. – Jeremy Harris Mar 20 '12 at 09:49
@cillosis i dont think thats how it works, because with bind_result, im binding a variable to one field. example: item_poster is binded to $item_poster_fetched and comment is binded to $comment_fetched – Marshall Mathews Mar 20 '12 at 10:02

While looping in mysqli prepared statement

2 Answers2