sqlsrv array doesn't return all rows

Question

I can't seem to figure out why sqlsrv_fetch_assoc only returns 1 row when there are 2 rows in the result set according to sqlsrv_num_rows. I've tried to hard code 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 receive

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

sqlsrv_num_rows

echo sqlsrv_num_rows( $query );
//Returns 2

score 7 · Accepted Answer · answered Apr 22 '13 at 19:39

When I try to view the result set

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

Since you listed this separately, I hope you're not doing this (showing all 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's obvious you're consuming sqlsrv_fetch_array twice per loop iteration, once for the condition check and once inside the loop.

Remove all fluff from the while loop and have just this - and absolutely nothing else, not even the ... or comments.

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

Yep. There was another `$stmt / $query` inside the `while` loop. This wrote over the previous result set and ended the loop. Figured it out then tried to delete my question. Have 25 rep. — Kermit, Apr 22 '13 at 19:40

score 3 · Answer 2 · answered Apr 22 '13 at 19:27

3

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

Emphasis by me.

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

You'll need to do a while loop to fetch all records, like this:

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

answered Apr 22 '13 at 19:27

Maerlyn

33,687
18
94
85

Should have mentioned that this is already inside a `while` loop. – Kermit Apr 22 '13 at 19:29
does this do 1 query for each row then? – David Work Apr 22 '13 at 19:30
Nope, 1 query altogether, and *rownum* fetch calls. – Maerlyn Apr 22 '13 at 19:35

score 0 · Answer 3 · answered Apr 22 '13 at 19:27

0

I'm under the impression that fetch_array only retrieves a row at a time (a row being the NEXT row)... Try something like...

while($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC );){ 

//do something awesome 

}

answered Apr 22 '13 at 19:27

brbcoding

13,378
2
37
51

score 0 · Answer 4 · answered May 29 '19 at 14:48

A simple way is to use sqlsrv_fetch_array and use multi dimensional array to store each row data. But on doing so make sure to use $i++ instead of $i += 1 If we use $i +=1 then only single row 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);

score 0 · Answer 5 · answered May 20 '20 at 14:07

This code serve me finally Thanks to "Ashish". I don't understand why its not upvoted.

$Result = array();
$sql  = "EXEC CRM_TestSP;";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

$i = 0;
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $Result[$i]["Description"] = $row['Description'];
    $i++;
}

sqlsrv_free_stmt($stmt);
return $Result;

It returns an array like this:

array(2) { [0]=> array(1) { ["Description"]=> string(4) "Text 1" } [1]=> array(1) { ["Description"]=> string(4) "Text2" } } 1

That's exactly what I'm looking for.

Reference: https://stackoverflow.com/a/56363304/7208109 – Carlos Eduardo Salazar Mori Jul 15 '20 at 12:33 — Carlos Eduardo Salazar Mori, Jul 15 '20 at 12:33
All regards for Ashish. – Carlos Eduardo Salazar Mori Jul 15 '20 at 14:55 — Carlos Eduardo Salazar Mori, Jul 15 '20 at 14:55

score 0 · Answer 6 · answered Aug 11 '21 at 06:30

0

$sql  = "SELECT id, description FROM dbo.tbl";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}
$data = array();
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
    $data[] = array(
                "description" => $row['description']
                );  
}

return $data;

answered Aug 11 '21 at 06:30

Kishan Sonani

1
1

1

Hy, welcome to [StackOverFlow](https://stackoverflow.com/). Please add some description to define your code also see [How to write a good anser](https://stackoverflow.com/help/how-to-answer). – a.ak Aug 11 '21 at 08:26

sqlsrv array doesn't return all rows

6 Answers6