Issue with connection to MSSQL from PHP

Question

I'm having problems putting data into MSSQL with PHP - I get this error:

sqlsrv_query() expects parameter 1 to be resource, boolean given in [path] on line 13

I can't really see where the problem is, or if I have a syntax error.

<?php
    $host = "127.0.0.1"; 
    $dbusername = "humid"; 
    $dbpassword = "humid";  
    $db = "test";
    $t = $_GET['t'];
    $h = $_GET['h'];


    $connectionInfo = array("UID" => $dbusername, "PWD" => $dbpassword, "Database" => $db) or die("Couldnt connect to server");
    $dbconnect = sqlsrv_connect($host, $connectionInfo);
    $sql = "INSERT INTO historic VALUES(NULL,$t,$h,CURRENT_TIMESTAMP)";
    sqlsrv_query($dbconnect, $sql);
?>

ANY help would be greatly appreciated! I'm not familiar with MSSQL, only MySQL

Okay, now I've enabled error messages, and I've got this one now:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 126 [code] => 126 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid pseudocolumn "$t". [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid pseudocolumn "$t". ) )

However, I have no idea what that could be? Is it because $t is a SQL syntax that I don't know of? Long story short, I'm building a temp and humid logging device from an Arduino, and I need it to post the values into a DB (using PHP) - the syntax for the PHP looks like this:

test.php?t=1&h=2

What have i done wrong now? When testing it on my MySQL server private, it all worked fine >_<

-- edit, solution found! --

Okay, I found the solution, I'm not sure if any of the other answers had a direct fix, however. MSSQL apparently doesn't accept "NULL" like MySQL does. But that was ofc. the last thing i edited, so I'm not sure if there were any errors in the prior code. Hoever, below is the final (and working) code. Arduino posts and everything. Eureka!

<?php
    $host = "WDKTO560\TEST"; 
    $dbusername = "humid"; 
    $dbpassword = "humid";  
    $db = "test";
    $t = $_GET['t'];
    $h = $_GET['h'];



    $connectionInfo = array("UID" => $dbusername, "PWD" => $dbpassword, "Database" => $db) or die("Couldnt connect to server");
    $dbconnect = sqlsrv_connect($host, $connectionInfo);
    $sql = "INSERT INTO historic (temperature, humidity, date) VALUES($t,$h,CURRENT_TIMESTAMP)";
    sqlsrv_query($dbconnect, $sql) or die( print_r( sqlsrv_errors(), true));
?>

Possible duplicate of [Reference - What does this error mean in PHP?](https://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php) — miken32, Sep 13 '17 at 22:52

score 0 · Answer 1 · answered Sep 11 '17 at 06:03

0

sqlsrv_connect() is failing to get a connection and returning FALSE. You will have to debug it.

Check if database and Username is correct .
User has access permission to write into DATABASE
You have installed the MSSQL driver for PHP.

Hope this helps.

answered Sep 11 '17 at 06:03

Vijay Rathore

593
8
16

Just double checked - seems like the user is right, and all permissions are set. I'll try using ODBC instead in a test. – j.andreasen Sep 11 '17 at 06:07

score 0 · Answer 2 · answered Sep 11 '17 at 06:09

Connect using Windows Authentication.

<?php
    $serverName = "serverName\sqlexpress"; //serverName,instanceName


    $ConInfo = array( "Database"=>"dbName");
    $conn = sqlsrv_connect( $serverName, $ConInfo);

    if( $conn ) {
         echo "Connection established.<br />";
    }else{
         echo "Connection could not be established.<br />";
         die( print_r( sqlsrv_errors(), true));
    }
    ?>

Connect by user name and password.

 <?php
    $serverName = "serverName\sqlexpress";
    $ConInfo = array( "Database"=>"dbName", "UserId"=>"userName", "PWD"=>"password");
    $conn = sqlsrv_connect( $serverName, $ConInfo);

    if( $conn ) {
         echo "Connection established.<br />";
    }else{
         echo "Connection could not be established.<br />";
         die( print_r( sqlsrv_errors(), true));
    }
    ?>

Hope this helps

Using user and password: Connection could not be established. Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -1 [code] => -1 [2] => Invalid option UserId was passed to sqlsrv_connect. [message] => Invalid option UserId was passed to sqlsrv_connect. ) ) — j.andreasen, Sep 11 '17 at 06:27

score 0 · Accepted Answer · answered Sep 14 '17 at 15:03

The answer was actually quite simple, it was a syntax error. MSSQL does NOT accept NULL to af inserted, but MySQL does. I wasn't familiar with MSSQL, so thats why I didn't saw the error.

So, the correct syntax for that line is:

$sql = "INSERT INTO historic (temperature, humidity, date) VALUES($t,$h,CURRENT_TIMESTAMP)";

I simply had to specify the columns (the first table was ID, auto incremented) and remove the NULL. In MySQL NULL would skip that column.

Issue with connection to MSSQL from PHP

3 Answers3