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));
}
 ?>
Improve this question
0

2 Answers 2

Reset to default
2

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>';
    }
}
?>
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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 Execute a simple PHP file with this line <?php phpinfo();?> 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.
No sqlsrv or pdo_sqlsrv sections displayed im my panel. It means that no connection can be made for MSSQL?
@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.
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.
|
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>';

   }
}

 ?>
Improve this answer

2 Comments

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!
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.

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.