CODEWITHSUNDEEP
phpsql-serversql-server-2008sqlsrv
gergalyb
gergalyb

Reputation: 99

PHP mssql behaving weird with different statements

I have the following code:

$stmt = sqlsrv_query($conn, $sql, $params);
$stmtForCounting = $stmt;
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
*table creation code*
}

So far it works, but when I add a bit to it so it looks like this:

$stmt = sqlsrv_query($conn, $sql, $params);
$stmtForCounting = $stmt;
while (sqlsrv_fetch($stmtForCounting)){
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){
*table creation code*
}

The $stmt variable becomes empty. Using sqlsrv_fetch($stmtForCounting) shouldn't affect $stmt, right?

(The original code was longer but I stripped it down to this trying to isolate the problem.)

Edit: It is not Empty because var_dump($stmt) still says resource(9, SQL Server Statement) but the while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)){*more code*} in the table creation section won't even run once.

Upvotes: 2

Views: 71

Answers (4)

stef77
stef77

Reputation: 1000

Why should sqlsrv_fetch($stmtForCounting) not affect $stmt? You're assigning the same resource to $stmt as well as to $stmtForCounting, and by while-looping over the resource, you're traversing the table to its end, which can be done just once.7

If you var_dump both, both will yield the same output (i.e., the same resource).

Perhaps to clarify: You open the resource by executing the query and initialize a variable $stmt which points to this resource. Afterwards, you initialize another variable, which contains a copy of $stmt, which points to the resource. True, both variables are independent, you can e.g. unset one and the other still points to the resource. But the point is, they're pointing to the same resource, hence, when you perform actions on them like fetching rows, the state of the resource is modified (it points to the next row), and this change takes place regardless whether you access the resource via $stmt or via another variable pointing to the same resource.

Upvotes: 3

Narf
Narf

Reputation: 14752

It's behaving perfectly well.

Just copying the variable in that context does nothing really, it still points to the same resource, and that resource internally has a row pointer. When you fetch all of its rows the first time, you move that pointer to its last position and that's where it stays.

It should be possible to move the pointer back to the first position, but that probably requires you to make the resource 'Scrollable' via the 4th parameter to sqlsrv_query() (not really sure about that one).

Upvotes: 4

Edna
Edna

Reputation: 66

It will mess up your $stmt definetly, it is same resource, and you try to read all of it twice.

Upvotes: 1

Gabriele Carbonai
Gabriele Carbonai

Reputation: 459

try to use mssql query instead of sqlsrv query take a look here http://php.net/manual/en/ref.mssql.php

Upvotes: -3

Related Questions