Hi all
I’m using a prepared statement using a fetch result, but when I return my result on the page the first row is always missing. I realised the problem was because I called $stmt->fetch() in two different instances:
$stmt->fetch() // at the top of the page becasue I need some values for the title
while($stmt->fetch()){ } //further down the page to show the list of items
How do I show the result inside the while loop from $stmt->fetch() without using it again?
Any ideas?
Thanks,
Barry
Maybe grab what you need from within the loop. Possibly:-
$first = true;
while($stmt->fetch()){
if($first) {
// Do what you gotta do with the first row
$first = NULL;
}
// Do whatever else you do for every row
}
Thanks Sam
I see what your saying, though seems like a work around, I think there is a possible solution using an array if I remeber correctly from a post I googled.
Example:
First instance
$stmt->bind_result($id, $caption...);
$stmt->store_result();
$rows = array();
$row_cnt = $stmt->num_rows;
$stmt->fetch();
And the loop
<?php
foreach($rows as $data) {
echo '<li>' . $id . '</li>';
}
$stmt->free_result();
$stmt->close();
?>
This seems to work, no errors, but no < li >'s are showing from the foreach?
Above the foreach, I have: <title>$caption</title> which shows ok.
Barry
You can also simple do:
$rows = $stmt->fetchAll();
And now you just have a regular array of data.
The PDO statement object actually has a rewind method as long as you use a buffered query. You can search for how to do one.
But simply fetching everything instead of fooling around with $stmt inside of other loops is the way I do it.
OK, then $stmt->fetch_all() would do it?
if mysqli uses the mysqlnd driver.
Well then I guess we have stumbled across yet another reason to use PDO over mysqli. Seems very strange.
You could always use do-while instead, which checks the condition at the end of the loop rather than the start:
$stmt->fetch();
do {
// whatever
} while ($stmt->fetch());this one will fail in case no rows returned
Thanks all!
I was hoping to keep this as simple as I can, using the array was my prefered choice if this can be achieved.
As a few have suggested - $stmt->fetch_all() seems to be the best approach. And yes using mysqli.
If I use $stmt->fetch_all() would I then replace $stmt->fetch() ?
Maybe if we can work with #3 - what would I need to change?
Appreciate the guidance here thanks 
Barry
Yes, I didn’t think of that, though presumably the OP would check whether the query returned any rows before trying to do anything with them.
Just to recap, as below.
I can only get the values from bind result for title, h1 by using $stmt->fetch() before I run the foreach/loop.
<title><?php echo $caption ?></title>
<h1><?php echo $someOtherBindedVariable ?></h1>
Then I need to run the foreach from the first instance of $stmt->fetch() further down the page:
<ul>
<?php
foreach($rows as $data) {
echo '<li>' . $id . '</li>';
}
$stmt->free_result();
$stmt->close();
?>
</ul>
How could I use the $stmt->fetch_all() to make this work?
Meaning, what do I need to change below, and remove?
$stmt->bind_result($id, $caption...);
$stmt->store_result();
$rows = array();
$row_cnt = $stmt->num_rows;
$stmt->fetch();
Thanks, Barry
Reading a bit more (I don’t use mysqli, so have to look it up) it will retrieve all the results as a numeric array, or an associative array, or both. So that suggests that it won’t be suitable for using with bound results.
Thanks droopsnoot,
So, back to the rows array…
$rows = array();
Is this something we could work with by using the current setup with $stmt->fetch() ?
foreach($rows as $data) {
echo '<li>' . $id . '</li>';
}
Or maybe scrap the bind approach and use a more general query?
Though I did think the bind result was more secure and the way forward 
Barry
If you scrap the bound results altogether and just use an associative array, you could use fetch_all() if your driver supports it.
...
$rows = $stmt->fetch_all();
echo "<h1>" . $rows[0]['caption'] . "</h1>";
foreach($rows as $row) {
echo $row['whatever'];
}and so on.
I think.
Yes this looks very familiar, something I’ve used in the past, albeit - feels like I’m moving backwards
I’ll have to dig a little deeper, if I can’t find a solution using the current setup I’ll have to think about using fetch_all without bind.
Anyhow, thanks for the help!
Will post back if I managed to fix it.
Barry
I must admit that I just do this stuff along the lines of post #2 as @Sam74 said, just set a flag and do special things while it’s set.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.