0

I am trying to create a php file to access a mssql database in my desktop and display the data in this webpage. The Web Server is running linux. I can access the mssql DB using sql or windows authentication, throught SQL managemnet studio from public IP with no problem. But cannot display any data in my page. The port is open and server is configured for remote access.

My question is, do i have to install php and microsoft drivers in my PC to make it work? My PC runs with Vista 32 and i use Microsoft SQL Server 2008 for the DB. Thanks.

MY code is...

 <?php

$connectionInfo = array( "UID" => "user", "PWD" => "123456", "Database"  => "TestDB" );
$link = sqlsrv_connect( "111.222.333.444", $connectionInfo );
if( $link ) {
 echo "Connection established.<br />";
} else{
 echo "Connection could not be established.<br />";
 die( print_r( sqlsrv_errors(), true ) );
}
$sql = "SELECT *  FROM table";

$stmt = sqlsrv_query( $link, $sql );
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC ) ) {
echo $row['value']."<br />";
}

if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}
 ?>
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Jim
  • 11
  • 4

2 Answers2

1

If I understand your question correctly, the answer is no.

PHP and PHP Driver for SQL Server should be installed on this machine, where the WEB Server is running. In your case this is the Linux machine. If all is installed and configured correctly, you should be able to connect to your instance of SQL Server, running on Vista machine. Of course, you should be able to connect to any instance of SQL Server, that allows remote access.

You need to choose the correct version of PHP Driver for SQL Server based on support matrix and follow the installation steps.

Your PHP code is correct, but it's good to check for errors after every execution of sqlsrv_ function:

<?php
# Connection info
$connectionInfo = array(
    "UID" => "user", 
    "PWD" => "123456", 
    "Database"  => "TestDB"
);

# Connection
$link = sqlsrv_connect("111.222.333.444", $connectionInfo);
if ($link === false) {
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true ) );
} else {
    echo "Connection established.<br />";
}

# Statement
$sql = "SELECT * FROM table";
$stmt = sqlsrv_query($link, $sql);
if ($stmt === false) {  
    echo "Error executing query.<br />";
    die( print_r( sqlsrv_errors(), true));
}

# Fetch data
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row['value']."<br />";
}

# End
sqlsrv_free_stmt($stmt);
sqlsrv_close($link);
?>

Update: Example using ODBC:

<?php
$server   = "111.222.333.444";
$user     = "user";
$password = "123456";
$database = "testDB";
$conn_string = 'Driver={SQL Server Native Client 11.0};Server='.$server.';Database='.$database.';client_charset=UTF-8;';
if ($conn = odbc_connect($conn_string, $user, $password)) {
    $sql = "SELECT * FROM table";

    $result = odbc_exec($conn, $sql);
    while (odbc_fetch_row($result)) {
        $value = odbc_result($result, "value");
        echo $value.'</br>';
    }
}
?>
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • Dear Zhorow thanks for your reply. I tryed your code above but had no luck once again. My PHP Version is 7.0.33, so drivers installed in webserver as displayed are: Server API :CGI/FastCGI , cURL support enabled , ODBC Support enabled, PDO support: enabled, PDO Driver for MySQL: enabled. I guess something i missing in my configuration so to make it work. Thanks. – Jim Apr 11 '19 at 17:25
  • @Jim Execute a simple PHP file with this line `` and check for `sqlsrv` or `pdo_sqlsrv` sections in the output. If they exist, your PHP Driver for SQL Server is enabled. And MySQL is not the same as MSSQL - it's MySQL extension. Thanks. – Zhorov Apr 11 '19 at 17:36
  • No sqlsrv or pdo_sqlsrv sections displayed im my panel. It means that no connection can be made for MSSQL? – Jim Apr 11 '19 at 18:10
  • @Jim Yes, you need to install PHP Driver for SQL Server to make a connection. I think that this must be your first option. Another option is probably ODBC. – Zhorov Apr 11 '19 at 19:11
  • Hi Zhorov, thanks for advice me . I tryed to access my DB via php using ODBC connection string, According to my webserver phpinfo() response the ODBC Support are enabled and PDO support: enabled but have no succes yet. – Jim Apr 12 '19 at 09:05
  • @Jim If you want to use `ODBC`, then you need to use different functions - see updated answer. But I think, that you should try to isntall PHP Driver for SQL Server. Thanks. – Zhorov Apr 12 '19 at 09:27
  • Οκ Zhorow I will give a try and let you know. I use shared hosting server. I believe they wouldn't make any change in hosting settings. Thanx. – Jim Apr 13 '19 at 12:22
0
Finally i made it to work with following code...

  <?php
 $server   = "192.168.1.111,50052"; // DB SERVER IP AND PORT
$user     = "XXXXXX";
 $password = "YYYYYY";
$database = "MYdb";
   $conn = 'Driver={SQL Server Native Client 11.0};Server='.$server.';                   Database='.$database.';client_charset=UTF-8;';

 $today = date("Y-m-d"); 

 if ($conn = odbc_connect($conn, $user, $password)) {
$sql = "SELECT * FROM MTRL WHERE MY_TABLE >= '$today'";

  $result = odbc_exec($conn, $sql);
 while (odbc_fetch_row($result)) {

    $product_id = odbc_result($result, "ID");
    $name = odbc_result($result, "NAME");
    $dateModified = odbc_result($result, "INSERTDATE");
    echo $id . " - " . $product_id. " - " . $name . " - " . $dateModified .     '</br>';

   }
}

 ?>
Jim
  • 11
  • 4
  • I'm glad you have a solution to your problem. You may consider the following for your next questions and answers: 1) It's good to post only one answer or if you need to add more information, edit your existing answer. 2) If you think that this or any other answer is the best solution to your problem, you may accept it. Thanks and good luck! – Zhorov Apr 23 '19 at 06:43
  • Dear Zhorov thanks again for your help and also the community. I will keep your advice in my mind, for future posts. Good luck to you also. – Jim Apr 24 '19 at 07:32