PHP with sqlsrv_num_rows

Question

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.

cwallenpoole · Accepted Answer · 2011-08-11 20:21:42Z

3

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
}

answered Aug 11, 2011 at 20:21

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

duncanrager Over a year ago

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -50 [code] => -50 [2] => This function only works with statements that have static or keyset scrollable cursors. [message] => This function only works with statements that have static or keyset scrollable cursors. ) ) 1looploop . /// Thats for the working GUID... now for the non working GUID I got the exact same error message but without the looploop at the end... does that suggest that both arrays have 0 elements? in the database they do have results from that query. I don't really understand the error.

duncanrager Over a year ago

Ok, so the reason that I was getting an error on the sqlsrv_num_rows was because I was using the default forward cursor, so thats fine, but it still doesn't explain the other behavior, the sql query only working for some GUIDS and not others... what could be wrong with the GUID column?

MsNichols Over a year ago

After 12 hours of searching... because of this post telling me to rid my sqlsrv_num_rows i was able to finally get my login script to work. I spend an entire day debugging my code only to find this post and it it working without effort! YIPEEEE...

Atiqur · Accepted Answer · 2016-01-12 10:45:04Z

1

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

answered Jan 12, 2016 at 10:45

Atiqur

4,0321 gold badge28 silver badges27 bronze badges

Collectives™ on Stack Overflow

PHP with sqlsrv_num_rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related