Mysqli prepared stmt - using object multiple times?

**traq** · 09-04-2012, 04:55 AM

this is highly oversimplified, and I don't completely understand all of it myself, but think of it like this: when you get a resultset, it's organized in rows (this is true even if there is only one result row).

Code:

resultset: {
>   result1: [col2, col2, col3]
   ,result2: [col1, col2, col3]
   ,etc.
}

and, there's this thing called a "pointer" (which most people who use higher-level languages - like PHP as opposed to, say, C/C++ - have never heard of) which we use constantly, usually without realizing it exists. I marked the pointer position in the resultset above with a >.

when you use certain methods (like fetch(), in this case), it returns the item the the pointer is pointing at (first time: first row), and then advances the pointer.

so, after one call to fetch(), the resultset looks like this:

Code:

resultset: {
    result1: [col2, col2, col3]
>  ,result2: [col1, col2, col3]
   ,etc.
}

if you call the method multiple times, the pointer keeps advancing. eventually (or quickly, if there's only one row), the pointer is at the end of the resultset:

Code:

resultset: {
    result1: [col2, col2, col3]
   ,result2: [col1, col2, col3]
   ,etc.
>
}

we're kinda in limbo, here. PHP doesn't really know that there's no more results until it tries to get them, so it won't tell MySQL to close the resultset. As a consequence, MySQL can't run some other query in the meantime. "commands out-of-sync."

what you're doing with the while() loop if running fetch() repeatedly. when fetch() gets to that last position, it will see there's no more rows, and return FALSE. the while() loop will end, and PHP will tell MySQL to close the resultset.

A drawback of this is that, if you ever have more than one row in your resultset, your while() code will run on all of them. This probably isn't expected or desired. Unless it's *really won't work* for some reason, I'd suggest using the close() method instead. if needed, the while( more_results() ) loop I suggested earlier will do the same thing as your loop, but won't run any code on the rows it passes over.