Find last loop of while($stmt->fetch())

Question

Is it possible to find when is the last loop of :

$stmt = $connection->stmt_init();

if ($stmt->prepare("SELECT `ProductId` FROM Products"))
{
    $stmt->execute();
    $stmt->bind_result($product_id);

    while($stmt->fetch())
    {
       if ($lastloop)
       {
          echo 'The last id is -> ';
       }

       echo $product_id.'<br />';
    }
}

I want to output something like :

1
2
...
9
The last id is -> 10

Cfreak · Answer 1 · 2013-07-19T14:52:32.750

1

You could keep count and compare to the number of rows

$stmt->store_result();
$rows = $stmt->num_rows;
$count = 1;
while( $stmt->fetch() ) {
    if( $count == $rows ) {
        echo 'The last id is -> ';
    }
    $count ++;
    echo $product_id . '<br />';
}

edited Jul 19 '13 at 14:52

answered Jul 19 '13 at 14:01

Cfreak

19,191
6
49
60

this is not working. EDIT it does need an $stmt->store_result(); – zPuls3 Jul 19 '13 at 14:06
edited. Sorry I'm more familiar with PDO that does it slightly differently – Cfreak Jul 19 '13 at 14:53

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

You could use $stmt->num_rows^, but ONLY if you $stmt->store_result() the result set and keep counting the active row offset. Storing the results is not viable for large sets.
Use SELECT SQL_CALC_FOUND_ROWS ProductId FROM Products; like in this answer here^. And then use FOUND_ROWS() to know the row number WITHOUT storing the result set initially. Requires one extra query... which is a small price to pay.

That's all you've got as viable and easy solutions.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 19 '13 at 14:05

CodeAngry

12,760
3
50
57

About solotuion 1. If i got a limit 100 in my query, will be considered as a large set? – zPuls3 Jul 19 '13 at 14:10
@zPuls3 If you have `TEXT/BLOB` columns with KBs/MBs of data and you do `SELECT *`... it is. **If you only `SELECT` specific small columns, like here... it's not.** – CodeAngry Jul 19 '13 at 14:11
1

@zPuls3 *Just an extra comment.* If you `SELECT` specific small columns within reasonable bounds... it's MUCH better to store always as this limits round trips to server. Otherwise, for each `fetch`... a new communication with the server is necessary. Which is good for large sets but not that good for smaller ones. – CodeAngry Jul 19 '13 at 14:21

Find last loop of while($stmt->fetch())

2 Answers2