CODEWITHSUNDEEP
phpsqlsql-serversqlsrv
Nickolas Casalinuovo
Nickolas Casalinuovo

Reputation: 23

SQLSRV skips a row when returning a query

function ambassadorPrimaryMerchantContacts(){
        //Load global MSSQL connection
            $mssql = mssql_connect();
            
        
        //Perform query
            $result = sqlsrv_query($mssql,
                "
                EXECUTE Ambassador_MerchantPrimaryContact @AmbassadorID = 1
                ");
            
            if( $result === false ) {
                if( ($errors = sqlsrv_errors() ) != null) {
                    foreach( $errors as $error ) {
                        print("SQLSTATE: ".$error[ 'SQLSTATE']."<br />");
                        print("code: ".$error[ 'code']."<br />");
                        print( "message: ".$error[ 'message']."<br />");
                    }
                }
            }
            
             
            
               sqlsrv_next_result($result);
               sqlsrv_next_result($result);
               sqlsrv_next_result($result);
            
            $merchantContacts = array();
            while($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC))  
            {  
                array_push($merchantContacts, array($row[0],$row[1],$row[2],$row[3],$row[4]));
            }  

            return $merchantContacts;
            sqlsrv_free_stmt($result);
    }

The code above does what I want and returns number of rows that matches what the query returns.

However, I hardcoded the three sqlsrv_next_result($result);. The query will change depending on @AmbassadorID, so that piece needs to be dynamic. I tried the following loop:

while(!sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC))  
            {  
               sqlsrv_next_result($result);
            }

But this loop skips the first row. How do I fix this?

Thanks!

Upvotes: 1

Views: 267

Answers (2)

Zhorov
Zhorov

Reputation: 30023

As an additional option you may try to use a do-while loop and fetch the data from all result sets. Note, that mssql_connect() and sqlsrv_query() are functions from two different PHP extensions, so you need to use sqlsrv_connect().

<?php
function ambassadorPrimaryMerchantContacts(){
    // Load global MSSQL connection
    $mssql = sqlsrv_connect($server, $cinfo);
        
    // Perform query
    $result = sqlsrv_query($mssql, "EXECUTE Ambassador_MerchantPrimaryContact @AmbassadorID = 1");
    if ($result === false) {
        if (($errors = sqlsrv_errors()) != null) {
            foreach ($errors as $error) {
                print("SQLSTATE: ".$error['SQLSTATE']."<br />");
                print("code: ".$error['code']."<br />");
                print("message: ".$error['message']."<br />");
            }
        }
    }
    
    // Fetch data   
    $merchantContacts = array();
    do {
        while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC))  
            $merchantContacts[] = $row;
        }
    } while (sqlsrv_next_result($result));

    // End
    sqlsrv_free_stmt($result);
    return $merchantContacts;
}
?>

Upvotes: 0

Barmar
Barmar

Reputation: 782785

Use sqlsrv_has_rows(). This just returns whether there are any rows, but it doesn't fetch one of them.

while (!sqlsrv_has_rows($result)) {
    sqlsrv_next_result($result);
}

Upvotes: 1

Related Questions