14

I'm running into problems knowing when and what to call after mysqli_stmt_execute
How do you know when to call

  1. mysqli_stmt_bind_result
  2. mysqli_stmt_store_result
  3. mysqli_stmt_fetch
Celeritas
  • 14,489
  • 36
  • 113
  • 194
  • short answer: store_result stores it, bind_result organizes it, fetch pulls it out so it can be used for output http://php.net/manual/en/mysqli-stmt.store-result.php – Jeff Hawthorne Jun 13 '13 at 19:46

2 Answers2

16

mysqli_stmt_bind_result() tells mysqli which variable you want to be populate when you fetch a row, but it doesn't fetch() anything yet. This is necessary to call once, before you call fetch.

mysqli_stmt_store_result() sets an optional behavior so that the client downloads all rows when you fetch() the first row, and caches the whole result set in the client (i.e. PHP). Subsequent fetch() calls will simply iterate over this client-cached result set. But setting this option itself does not cause a fetch() yet either. This function is totally optional.

mysqli_stmt_fetch() returns the next row in the result set, and causes it to be stored in a bound variable. You must call this function in a loop, for each row of the result set. That is, until the fetch returns false.

hakre
  • 193,403
  • 52
  • 435
  • 836
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • If I know the query will return at most 1 row then `mysqli_stmt_store_result()` is pointless, right? – Celeritas Jun 14 '13 at 05:41
  • Right; the alternative is `mysqli_use_result()`, which fetches one row at a time from the MySQL server at the time you call fetch. If your query result is known to have only one row, there's no difference between these two strategies. – Bill Karwin Jun 14 '13 at 13:50
  • 1
    Wait something's not right; [as discussed here](http://stackoverflow.com/questions/17054131/determining-if-no-rows-are-returned-from-a-prepared-statment) `mysqli_stmt_num_rows()` doesn't work unless `mysqli_stmt_store_result()` had been called. Why is this? Is it because PHP "isn't smart enough" to download the rest of the rows so it can't count them all where as calling `mysqli_stmt_store_result()` downloads all rows? – Celeritas Jun 14 '13 at 17:48
  • @Celeritas, it's not going to download all rows if you didn't say it's okay to do so, by calling store_result. The client can't know if there's a billion rows in the result set. And the client can't calculate num_rows until it downloads all rows. This is true in the underlying C client API, not only in PHP. – Bill Karwin Jun 14 '13 at 18:04
5

The process of querying an SQL database contains these steps:

  • sending of the query to the SQL server
  • the SQL server parsing the query and gathering the requested result set
  • transferring of the found result set data from the SQL server to PHP
  • putting the data into PHP variables somehow so the script can work with them

The functions you ask about have these roles in this process:

  • store: transfer all rows immediately from the MySQL database into PHP's memory; typically has no practical effect and is done automatically at some point anyway
  • bind: bind variables, so that when you call fetch() those variables contain result data; i.e. tell PHP which variables it should put the result into
  • fetch: "read" a row from the result set and store it in the variables you previously bound; if the data is not in PHP's memory yet it will be transferred from the SQL server at this point
deceze
  • 510,633
  • 85
  • 743
  • 889