CODEWITHSUNDEEP
phpsql
Rich
Rich

Reputation: 39

sqlsrv_fetch_array returning 1 less than the number of rows

I'm working on some PHP stuff, and I'm trying to use sqlsrv_fetch_array() to return a set of results. I've noticed however that if the query returns x results in the management studio, it returns (x-1) results in the actual PHP query.

This becomes a real problem when I am searching for a single thing, and it returns nothing.

The code is as follows:

function sqe($colname, $aq = NULL, $init = NULL)
{
    global $connection;
    $q = "SELECT TOP 2 * FROM tblname WHERE colname='" . $colName . "' ORDER BY colname DESC";

    $statement = sqlsrv_query($connection, $q);

    if (sqlsrv_fetch_array($statement) === false) {
        echo "Error fetching...";
        die(print_r(sqlsrv_errors(), true));
    } else {
        $source = "";
        echo $q;

        while ($row = sqlsrv_fetch_array($statement, SQLSRV_FETCH_ASSOC)){
            $source .= "<li><img src='data:image/png;base64," . $row['rowname'] . "' /></li>";
        }
        echo ($source);
        sqlsrv_free_stmt($statement);
        die();
    }
}

I believe the trouble is with the while loop, but I'm not sure what's actually causing it to return 1 less than what it should.

Any ideas?

Upvotes: 1

Views: 965

Answers (1)

Chris Forrence
Chris Forrence

Reputation: 10084

Your if-statement checks if sqlsrv_fetch_array($statement) is false, but calling that method advances the internal cursor, making your while-loop start with the second row instead of the first.

To see if the query either failed or returned no rows, you can compare $statement to false and call sqlsrv_has_rows as well.

if ($statement === false || !sqlsrv_has_rows($statement)) {

    // Either the query returned an error or no matches were found in the database
}

Upvotes: 1

Related Questions