title: PHP retrieve multiple rows with stream content at once with sqlsrv
body: <p>This is kind of a follow up of <a href="https://stackoverflow.com/questions/67998821/create-file-system-file-from-file-stored-in-microsoft-sql-database">this question</a>.</p>
  <p>This code retries a row from the database where one column is a resource and creates a file system file with it.</p>
  <pre class="lang-php prettyprint-override"><code>$query = &quot;select top(1) DESCRIPTION, FILETYPE, DOCUMENT from dbo.Documents;&quot;;
  $stmt = sqlsrv_query($this-&gt;sqlsrv_conn, $query);
  if (sqlsrv_fetch($stmt)) {
      $document = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
      // $fileName = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR));
      // $ext = sqlsrv_get_field($stmt, 1, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR));
      file_put_contents(
          // $fileName . '.' . $ext,
          'filename'.'.doc',
          stream_get_contents($document),
      );
  } 
  </code></pre>
  <p>Now I have to do this with all the records in the database, not only one. What is the best way to achieve that?</p>
  <p>I looked at <a href="https://www.php.net/manual/en/function.sqlsrv-fetch-array.php" rel="nofollow noreferrer"><code>sqlsrv_fetch_array</code></a> which has an argument &quot;$fetchType&quot; but this is to define in which format the rows are grouped together (numbered or assoc array).<br />
  How can I define the fetchType for each column under that array of rows? Like I can with <code>sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY))</code> when only one row is fetched.</p>
  <p><em>Note: I have received the feedback that my questions tend to be unclear, if you see things that can be improved to make this question better and easier to answer, please let me know.</em></p>
  <h2 id="edit-anq9">Edit</h2>
  <p>Thank you @Zhorov the while loop does work! But I face a new silly issue.<br />
  I lied, the code I posted was not exactly the code that worked for me. Apparently I didn't test it properly with fetching the file name and extension as well. What worked was only when I fetched the binary data as a stream <strong>with nothing else</strong>.</p>
  <p>I don't understand it at all. The following code works like expected:</p>
  <pre class="lang-php prettyprint-override"><code>$query = &quot;select top(1) DESCRIPTION, FILETYPE, DOCUMENT from dbo.Documents;&quot;;
  $stmt = sqlsrv_query($this-&gt;sqlsrv_conn, $query);
  if (sqlsrv_fetch($stmt)) {
      $document = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
      // $fileName = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR));
      file_put_contents(
          // $fileName . '.doc',
          'filename.doc',
          stream_get_contents($document),
      );
  } 
  </code></pre>
  <p>But if I also want to fetch the file name or anything else</p>
  <pre class="lang-php prettyprint-override"><code>$query = &quot;select top(1) DESCRIPTION, FILETYPE, DOCUMENT from dbo.Documents;&quot;;
  $stmt = sqlsrv_query($this-&gt;sqlsrv_conn, $query);
  if (sqlsrv_fetch($stmt)) {
      $document = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
      $fileName = sqlsrv_get_field($stmt, 0);
      file_put_contents(
          'filename.doc',
          stream_get_contents($document),
      );
  } 
  </code></pre>
  <p>I get the following exception:</p>
  <blockquote>
  <p>Error: stream_get_contents(): supplied resource is not a valid stream resource File</p>
  </blockquote>
  <p>I debugged it down to this with xdebug. Here are screenshots of the two scenarios. This is the run with a valid stream and no exception (you can see the &quot;type=stream&quot;):
  <a href="https://i.stack.imgur.com/JLvAI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JLvAI.png" alt="enter image description here" /></a></p>
  <p>And here, the only thing I did is remove the comment on the line and a breakpoint right before the exception happens (you can see the &quot;type=Unknown&quot;):
  <a href="https://i.stack.imgur.com/Jpn19.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Jpn19.png" alt="enter image description here" /></a>
  Why does fetching something else break the stream? (This behaviour is the same when I using while loop or not)</p>
  <h2 id="edit-2-zher">Edit 2</h2>
  <p>Behaviour does change when changing the order in which <code>sqlsrv_get_field</code> is called. If I put the binary data after the file name, the <code>$document</code> variable is <code>false</code>. Not even an &quot;Unknown&quot; stream.</p>
  <p><a href="https://i.stack.imgur.com/4dNzd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4dNzd.png" alt="enter image description here" /></a></p>
  <p>Edit: Reason for this is, as @Zhorov pointed out:</p>
  <blockquote>
  <p>From <a href="https://docs.microsoft.com/en-us/sql/connect/php/sqlsrv-get-field?view=sql-server-ver15" rel="nofollow noreferrer">documentation</a> - <em>sqlsrv_get_field retrieves data from the specified field of the current row. Field data must be accessed in order. For example, data from the first field cannot be accessed after data from the second field has been accessed</em></p>
  </blockquote>