php warnings connecting to SQL server database

Question

I've built my first PHP site and I'm interacting with an SQL database.The code is supposed to insert a record into a table. The code does complete the insert, but throws 3 warning messages. I do not know how to remove the messages. I tried to suppress the warnings in the php.ini by turning off error reporting, but that did not stop the warnings. I read that it's better to fix the errors rather than suppress them, but I know not how to do that.

PHP Warning: sqlsrv_query() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\Parishes_prod\administration\add_location.php on line 49

PHP Warning: sqlsrv_free_stmt() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\Parishes_prod\administration\add_location.php on line 50

PHP Warning: sqlsrv_close() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\Parishes_prod\administration\add_location.php on line 51

41.   $tsql = "insert into tbllocation (event_ID, Date, Location ) values(?,?,?)";  
42.   $dt = $_POST['date'];
43.   if ($dt == ""){
44.     $dt = null; 
45.   }
46.   $params = array($_POST['eventID'], $dt, $_POST['location']);  
47.   $serverName = "localhost";
48.   $connectionInfo = array( "Database"=>"database", "UID"=>"User", "PWD"=>"password" );
49.   $conn = sqlsrv_connect( $serverName, $connectionInfo);
50.   $stmt = sqlsrv_query($conn, $tsql, $params);  
51.   sqlsrv_free_stmt($stmt);

Your line number dont seem to match up properly, but I would say that the `sqlsrv_connect()` failed. **Never** try and ignore errors, always fix them — RiggsFolly, Dec 11 '18 at 15:07
Possible duplicate of https://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php — misorude, Dec 11 '18 at 15:08
The code complete the insert? Are you sure? Did you confirm that data is inserted by querying the database afterwards? It seems that your connection failed. — Felippe Duarte, Dec 11 '18 at 15:09
Your `sqlsrv_connect()` fails - exec `sqlsrv_errors()` after this call and post errors. Thanks. — Zhorov, Dec 11 '18 at 15:10
Read [Example 1 in the manual](http://php.net/manual/en/function.sqlsrv-connect.php) and add the error checking code that should already have been in your code. If the error is not obvious, show us the error message — RiggsFolly, Dec 11 '18 at 15:14

score 0 · Answer 1 · answered Dec 11 '18 at 18:09

Try this solution:

<?php
 //1. Connect:
$serverName = "localhost";
$connectionInfo = array( "Database"=>"database", "UID"=>"User", "PWD"=>"password" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
 die( print_r( sqlsrv_errors(), true));
}
//Query
  $tsql = "insert into tbllocation (event_ID, Date, Location ) values(?,?,?)";  
  $dt = $_POST['date'];
     if ($dt == ""){
      $dt = null; 
     }
     $params = array($_POST['eventID'], $dt, $_POST['location']); 
    $stmt = sqlsrv_query($conn, $tsql, $params);
   if( $stmt === false ) {
   die( print_r( sqlsrv_errors(), true));
  }
   sqlsrv_free_stmt($stmt);

RCDAWebmaster · Answer 2 · 2018-12-14T15:07:50.670

I checked the database and the record is definitely getting inserted into the table. I'm not sure why I was getting the warnings. I found out that I could tell PHP to only report errors with the following code: error_reporting(E_ERROR ); which turned off the warnings and all was good. I know its not good to suppress warnings as they indicate a problem that could become an error down the road.

Today I was going to try and fix the warning, but after removing the suppress warning code, the warning message is not appearing. No sense fixing what isn't broken. Right?

Yesterday, I adjusted my code to always point to the live database server even when looking at the local copy of the site on the development server. This way there is no need to run a separate database locally and try to keep the 2 synchronized. Maybe changing the settings for the connection fixed everything. My new connection strings are:

$serverName = "livedata";
$connectionInfo = array( "Database"=>"database", "UID"=>"User", "PWD"=>"password", "LoginTimeout"=>60 );
$conn = sqlsrv_connect( $serverName, $connectionInfo);

Livedata represents the production VPS which resides in the cloud. I setup livedata in the hosts file on both the DEV machine and prod machine. This was necessary since the IP of the production machine is different depending if I'm on the VPS on my local DEV PC on the corporate network.

I can now say that I get no errors or warnings and all is good. :-)

I still wish I knew what the exact cause was.

Edgard, I did not get a chance to try your code, but thanks for posting it. I'm sure it would have helped if I hadn't already fixed the error.

php warnings connecting to SQL server database

2 Answers2