CODEWITHSUNDEEP
phpdatabasesql-server-2008
duncanrager
duncanrager

Reputation: 247

PHP with sqlsrv_num_rows

I'm attempting to execute a query of a SQL Server database, looking at a many to many relationship. I link the table I want the rows from with the relationship table, and plug in the unique id, in this case the $guid.

The query is functional, sometimes. It will work, and then I switch out solely the $guid for another one, and it fails, even though I'm staring at the table and it has values associated with that new $guid.

<?php
$guid = '{893C7BF8-E8C5-41D5-9C61-A72CF62DDBA8}';

// Connect to Database
$server = "#@#($#";     
$connectionSettings = array("Database"=>"db", "UID"=>"user", "PWD"=>"pword");
$connection = sqlsrv_connect($server, $connectionSettings);

if (!$connection){
    die("Failed Connection");
}

// Prepare and Execute Query
$sql = "SELECT * 
        FROM STREAMS.ATTACHMENTS a INNER JOIN STREAMS.RELATTACHMENTS ra
        ON a.ROWGUID = ra.ATTACHMENT_GUID
        WHERE ra.FEATURE_GUID = '" . $guid . "'";

$results = sqlsrv_query($connection, $sql);
$rowCount = sqlsrv_num_rows( $results );

if ($rowCount === false)
    echo "failure";
else
    echo $rowCount;

while($row = sqlsrv_fetch_array($results)){
    echo "loop";
}
?>

Even stranger, the output to this code is the following:

failurelooploop

So that implies that the sqlsrv_num_rows command counted no rows in the result... but it also implies that the same results set has 2 rows, since the while loop went around twice.

Can anybody explain this funky behavior?

Thanks.

Upvotes: 3

Views: 10668

Answers (2)

Atiqur
Atiqur

Reputation: 4022

This error shows for cursor type SQLSRV_CURSOR_FORWARD & SQLSRV_CURSOR_DYNAMIC . my personal preference is not to use it. if you still want to use it pass extra parameter to query function like :

$stmt = sqlsrv_query( $connection, $sql, array(), array( "Scrollable" => 'keyset' ));
// $stmt = sqlsrv_query( $connection, $sql, array(), array( "Scrollable" => 'dynamic' ));
// $stmt = sqlsrv_query( $connection, $sql, array(), array( "Scrollable" => 'static' ));

check more : https://msdn.microsoft.com/en-us/library/hh487160.aspx -- http://php.net/manual/en/function.sqlsrv-query.php

Upvotes: 1

cwallenpoole
cwallenpoole

Reputation: 82028

I will bet that you have some sort of error: sqlsrv_num_rows will return FALSE if something goes wrong. You can get the error output through:

// probably don't want this in production.
print_r( sqlsrv_errors());

I'll guess the cause has to do with your guid column, but I can't be sure. :-)

Oh, and unless you need the number of rows, don't use it. Use do... while instead:

$row = sqlsrv_fetch_array($results);
if($row)
{
    do{
        echo "loop";
    } while( $row = sqlsrv_fetch_array( $results ) );
}
else
{
    // No results found
    // you can output print_r( sqlsrv_errors() ); here
}

Upvotes: 3

Related Questions