Geek Answers Handbook

Sqlsrv array does not return all rows

I cannot understand why sqlsrv_fetch_assoc returns only 1 row if there are 2 rows in the result set according to sqlsrv_num_rows . I tried hard-coded the parameter, but I still get the same result.

SSMS Result Set

  id description
 ------ -------------
 2 Administrator
 3 User

Php

 $col = 'abcd'; $stmt = "SELECT id, [description] FROM dbo.tbl WHERE col = ?"; $params = array( $col ); $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET ); $query = sqlsrv_query( $conn, $stmt, $params, $options ); if( $query === false ) { print( print_r( sqlsrv_errors() ) ); } while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) { ... }

When I try to view the result set

 $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC ); print_r($row);

I get

  Array
 (
     [id] => 2
     [description] => Administrator
 )

sqlsrv_num_rows

 echo sqlsrv_num_rows( $query ); //Returns 2
+5
source share
5 answers

When I try to view the result set

$ row = sqlsrv_fetch_array ($ query, SQLSRV_FETCH_ASSOC);
print_r ($ string); `

Since you specified this separately, I hope you do not (showing all the code):

 $col = 'abcd'; $stmt = "SELECT id, [description] FROM dbo.tbl WHERE col = ?"; $params = array( $col ); $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET ); $query = sqlsrv_query( $conn, $stmt, $params, $options ); if( $query === false ) { print( print_r( sqlsrv_errors() ) ); } while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) { ... $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC ); print_r($row); }

Because then it is obvious that you consume sqlsrv_fetch_array twice for each iteration of the loop, once to check the status, and once inside the loop.

Remove the entire fluff from the while loop and get just that - and absolutely nothing, not even ... or comments.

 while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) { print_r($row); }
+6
source

sqlsrv_fetch_array : returns the next available row of data as an associative array, a numeric array, or both (default).

Emphasis on me.

Return Values: returns an array with success, NULL if there are no more rows to return, and FALSE if an error occurs.

You will need to do a while loop to retrieve all the records, for example:

 while ($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) { //process $row }
+3
source

I get the impression that fetch_array only retrieves the string at a time (the string is a NEXT string) ... Try something like ...

 while($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC );){ //do something awesome }
0
source

I will join the topic - I have a SQL query:

 SELECT a.tw_Nazwa, c.st_Stan, d.zd_Glowne , d.zd_Zdjecie, a.tw_Id, a.tw_Symbol,e.cht_IdCecha,b.tc_CenaNetto1,b.tc_CenaBrutto1 FROM tw__Towar a LEFT JOIN tw_CechaTw e ON a.tw_Id = e.cht_IdTowar LEFT JOIN tw_Cena b ON a.tw_Id = b.tc_IdTowar LEFT JOIN tw_Stan c ON b.tc_IdTowar=c.st_TowId LEFT JOIN tw_ZdjecieTw d ON d.zd_IdTowar=c.st_TowId WHERE tw_SklepInternet = 1 AND st_MagId = 1

It is assumed that this query will have 261 results and what it needs if it comes with a database program Dbeaver.

Via:

 $getResults = sqlsrv_query($conn1, $Query); if( $getResults === false ) { die( print_r( sqlsrv_errors(), true) ); }

and

 while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) { //blablabla $i++; } print $i //is showing only 12 results

This works, but is not a solution:

 for($i=0;$i<=260;$i++){ $row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC) //blablabla $i++; }

where is the mistake and how to get around it.

0
source

An easy way is to use sqlsrv_fetch_array and use a multidimensional array to store the data of each row. But be sure to use $ i ++ instead of $ i + = 1. If we use $ i + = 1, then only one line is saved.

  $i = 0; while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) { $result[$i][0] = $row['id']; $result[$i][1] = $row['description']; $i++; //works // $i += 1; //does not work with this ! } var_dump($result);
-1
source

More articles:


All Articles