1

I am trying to make a very simple query to a SQL database using PHP, but no matter what I do, it seems it can't pull any proper results. The following is the code I have written:

$loDate = '2013-10-01';
$hiDate = '2015-01-01';
$queryStr = "select TimeCol from dbo.SUBPUMP where TimeCol between " . $loDate . " and " . $hiDate;
$conn = sqlsrv_connect($serverName, $conInfo);
$result = sqlsrv_query($conn, $queryStr);

while ($row = sqlsrv_fetch_array($result)) {
    // This never prints.
    echo "row:<br>"; var_dump($row); echo "<br><br>";
}

echo "<br><br><br>";

// Prints "conInfo: resource(2) of type (SQL Server Connection)"
echo "conn: "; var_dump($conn); echo "<br><br>";

// Prints "result: resource(3) of type (SQL Server Statement)"
echo "result: "; var_dump($result); echo "<br><br>";

// Prints "row: NULL"
echo "row: "; var_dump($row); echo "<br><br>";

What I am confused on is how it can establish a proper connection to the database.

Since $conn and $result do not print any errors, but yet do not seem to actually fetch any data for me.

So far, my best guess is that since I am attempting to grab the data from a database through a VPN, and thus might only have a tenuous connection, that the sqlsrv_connect() will attempt to try many times to connect to the database until it receives a single good connection. While the sqlsrv_query() will only attempt this once, and thus fails to gather any data.

Does anyone else have any ideas as to what might be going wrong here?

Improve this question

5 Answers 5

Reset to default
2

The documentation, for sqlsrv_fetch_array, states:

Returns an array on success, NULL if there are no more rows to return, and FALSE if an error occurs.

A NULL result can therefore only mean that there are 0 results from your query. So while ($row = sqlsrv_fetch_array($result)) will never loop.

You could handle this with a conditional check with sqlsrv_has_rows(). This returns a boolean true if there are more than 0 rows.

For example:

if (sqlsrv_has_rows($result)) {
  while ($row = sqlsrv_fetch_array($result)) {
    echo "row:<br>"; var_dump($row); echo "<br><br>";
  }
} else {
  echo "<br/>No Results were found."; 
}
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I figured as such. However, then this means that my sqlsrv_query() method is returning nothing, but yet the sqlsrv_connect() method has established connection. The query is syntactically correct since I can copy-paste it into my MS SQL Server Management Studio and run it fine.
It will run fine (as in no errors) - However there are no results - Which is not actually an error (hence NULL rather than false)
@imprisoned243 what results are you getting for the same query in MS SQL Server Management Studio?
But there are results I receive when I make the same query in my MS SQL Server Management Studio. So it seems something is not working, since it is not grabbing the proper data that I know exists. The results I get in the Management Studio are 569 rows of date-time stamps.
@imprisoned243 Id the SQL generated identical? Might be worth var_dump($queryStr) to see the statement.
2

Since you are referring to date column, try setting "ReturnDatesAsStrings" option in connection option(http://msdn.microsoft.com/en-us/library/ff628167.aspx).

Sample connection:-

$connection['UID'] = $username;
$connection['PWD'] = $password;
$connection['Database'] = $database_name;
$connection['ReturnDatesAsStrings'] = true;
$connection['ConnectionPooling'] = false;
$connection['CharacterSet'] = 'UTF-8';

if (!empty($port)) {
  $hostname = $hostname.', '.$port;
}

$conn = sqlsrv_connect($hostname, $connection);

Also try setting cursor type to "SQLSRV_CURSOR_STATIC" in sql_query function (http://msdn.microsoft.com/en-us/library/hh487160.aspx)

Sample Query:-

$result_id = sqlsrv_query($conn, $queryStr, null, array(
                'Scrollable' => SQLSRV_CURSOR_STATIC,
                'SendStreamParamsAtExec'=> true
            ));


while($row = sqlsrv_fetch_object($result_id)) {

}
Improve this answer

Comments

0

Even your result is equal to null, $result returns this

// Prints "result: resource(.) of type (SQL Server Statement)"
echo "result: "; var_dump($result); echo "<br><br>";

Your query may returns null, because of your date format. Did you try this query without dates

$queryStr = "select TimeCol from dbo.SUBPUMP";

Improve this answer

1 Comment

Interestingly, if I remove the entire WHERE clause of the query and modify it slightly so that it appears as "select top 10 TimeCol from dbo.SUBPUMP" so that I forgo the use of the timestamps, it actually prints the results properly - so now I have narrowed the issue down to how PHP wants to interpret my date-like strings. Unfortunately this is an issue, because the next step I will be taking will be interpreting user input and date ranges and figuring those into my query strings, so I will need this working at somehow.
0

I'm a little surprised this has gone so long without an answer. The problem here is very simple: The dates don't have single quotes around them.

$loDate = '2013-10-01';
$hiDate = '2015-01-01';
$queryStr = "select TimeCol from dbo.SUBPUMP where TimeCol between '" . $loDate . "' and '" . $hiDate . "'";

SQL Server requires date strings to be quoted to interpret them as dates.

Also, probably worth noting that you should be using a prepared statement. That is safer and doesn't require you to quote your string.

Improve this answer

Comments

0

I know this is an old answer, but I was having he same problem with it today.

Turns out I was using an old version of PHP. Updating to a newer version fixed this for me. In my case it was going from version 5.6 to 7.2.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.