How to Connect to SQL Server With Php

How to Connect to SQL Server With Php

by

You want to be able to connect to your SQL Server through php. But how? There are a lot of ways to do it, and this guide is going to show you how to connect using one of the most common methods – thephp connection class. In addition, this guide will also show you How to Connect to SQL Server With Php, how to connect php to sql server management studio, how to connect remote sql server database in php and how to connect sql server with php 7.

How to Connect to SQL Server With Php

How to Connect to SQL Server.

To connect to your SQL Server, you first need to open a new command prompt and type the following:

C:\Program Files (x86)\Microsoft SQL Server\Server Byrd\Bin

In this example, C:\Program Files (x86)\Microsoft SQL Server\Server Byrd\Bin is the location where your SQL Server installation is located. You can also use the path variable to refer to your installation while connecting, for example:

C:\ProgramData\Microsoft SQL Server\Instances\

The Connector will automatically find and initialize the appropriate Microsoft sql server instance on startup. If you are not using Microsoft sql server 2012 or later, you will need to install the provider package from Microsoft. For more information on how to connect to yourSQL server using php see http://msdn.microsoft.com/en-us/library/azure/azure-sqlserver-php-connections.aspx . As of version 7.1 of the PHP Runtime there are a number of different ways that php can connect to sql servers depending on what particular version of sql server you are running (see https://phpappliance.org/en/blog/#php7-sqlserver).

How to Use the Connector.

To connect to your SQL Server, you first need to create a new connection. To do so, open the command prompt and type:

CREATE CONNECTION sqlserver;.

After creating the connection, you can use it in a script or in a web application. In Example 2 below, we are going to connect to our SQL server and run some tests.

Example 2: Connecting to our SQL server using php

SELECT * FROM TestTable;

This will return the following output:

Table “TestTable” Column “1” not found.

How to Use the Command Prompt To Connect to SQL Server.

To connect to your SQL Server, use the following command:

C:\Windows\system32\cmd.exe Connect to SQL Server using the following credentials:

SQL Server is a Microsoft Corporation product. To connect to it from an operating system, run the following command: C:\WINDOWS\system32\cmd.exe sqlserverconnect The output of the sqlserverconnect command should show something like this: Connection successful. You can now access your server through the Windows System Management Interface (SMI).

Use the Command Prompt To Connect To Your Database

To connect to your database, use the following command: C:\WINDOWS\system32\cmd.exe sqldbconnect The output of the sqldbconnect command should show something like this: Connection successful. You can now access your database through the Windows System Management Interface (SMI).

Use the Command Prompt To Connect to Your Scripting Language

To connect to your scripting language, use the following command: C:\WINDOWS\system32\cmd.exe sqlscriptor The output of the sqlscriptor command should show something like this: Connection successful; you can now access your scripting language through SMI and SQL scripts.

Use the Command Prompt To Connect to Your Reports

To connect to reports, use the following command: C:\Windows\system32\cmd.exe sprocmd The output of sprocmd will show you how to create and connect to reports on your SQL Server instance with PHP): CREATE REPORT –database=MyDatabase(name) –output-file=/root/reports/MyReport1.txt –username=’USERNAME’ –password=’PASSWORD’ SELECT * FROM MyReport1; You can also connect to reports using the following command: C:\Windows\system32\cmd.exe sqldbreports The output of the sqldbreports command will show you how to connect to reports on your SQL Server instance with PHP): SELECT * FROM MyReport1;

How To Connect Php To Sql Server Management Studio

Step 1: Connect

This OpenConnection function is called near the top in all of the functions that follow.

PHPCopy

Step 2: Execute query

The sqlsrv_query() function can be used to retrieve a result set from a query against SQL Database. This function essentially accepts any query and the connection object and returns a result set, which can be iterated over with the use of sqlsrv_fetch_array().

PHPCopy

Step 3: Insert a row

In this example, you’ll see how to execute an INSERT statement safely and pass parameters. Parameter values protect your application from SQL injection.

PHPCopy

Step 4: Roll back a transaction

This code example demonstrates the use of transactions in which you:

  • Begin a transaction
  • Insert a row of data, Update another row of data
  • Commit your transaction if the insert and update were successful and roll back the transaction if one of them wasn’t

How To Connect Sql Server With Php 7

1 Introduction

It is required to connect to SQLServer(MSSQL) databases via PHP script on a Windows system.

This document describes how to enable extension for PHP 7.0.30.

2 Download SQL Server extension for PHP

The Microsoft Drivers for PHP for SQL Server require the following versions of PHP:

For PHP7.0.0+,We have to download Drive4.0 version or up.

We download Drive4.3(SQLSRV43.EXE) here as example. Drive4.3(SQLSRV43.EXE) contains below .dll files:

After decompression, select 32 or 64 bit extensions according to PHP version, and notice Thread safe(NTS and TS).

TS refers to Thread Safety, which is generally selected when IIS is loaded in ISAPI mode.

NTS refers to None-Thread Safe,which is generally selected when running in fast CGI mode, which has better performance.

3 Copy the files needed to the PHP extension directory

Put them in the corresponding version of PHP directory.

 (For example, use php_pdo_sqlsrv_7_nts_x86.dll and php_sqlsrv_7_nts_x86.dll).

4 Modify php.ini

Add the following two lines in “php.ini” to enable extensions：

extension=php_sqlsrv_7_nts_x86.dll

extension=php_pdo_sqlsrv_7_nts_x86.dll

 or      

5 Install ODBC Driver(if not installed)

ODBC Driver：

Microsoft® ODBC Driver 11 for SQL Server® – Windows (SQL Server® 2005):

Microsoft® ODBC Driver 13 for SQL Server® – Windows + Linux (SQL Server® 2016)

6 How to check if we have enabled SQL extension in PHP7.0.0+ or not

6.1 Create file “phpinfo.php” in the root directory of the website

Browser access xxx.xxx.xxx.xxx/phpinfo.php

6.2 Create file “test.php” in the root directory of the website

<?php

$serverName = “xxx.xxx.xxx.xxx”; //serverName\instanceName

$connectionInfo = array( “Database”=>”databasename_here”, “UID”=>”username_here”, “PWD”=>”password_here”);

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {

echo “Connection established.<br />”;

}else{

echo “Connection could not be established.<br />”;

die( print_r( sqlsrv_errors(), true));

}

?>

Php Sqlsrv Example

The AdventureWorks Product Reviews example application is a Web application that uses the SQLSRV driver of Microsoft Drivers for PHP for SQL Server. The application lets a user search for products by entering a keyword, see reviews for a selected product, write a review for a selected product, and upload an image for a selected product.

Running the Example Application

  1. Install the Microsoft Drivers for PHP for SQL Server. For detailed information, see Getting Started with the Microsoft Drivers for PHP for SQL Server.
  2. Copy the code listed later in this document into two files: adventureworks_demo.php and photo.php.
  3. Put the adventureworks_demo.php and photo.php files in the root directory of your Web server.
  4. Run the application by starting https://localhost/adventureworks_demo.php from your browser.
Requirements

To run the AdventureWorks Product Reviews example application, the following must be true for your computer:

  • Your system meets the requirements for the Microsoft Drivers for PHP for SQL Server. For detailed information, see System Requirements for the Microsoft Drivers for PHP for SQL Server.
  • The adventureworks_demo.php and photo.php files are in the root directory of your Web server. The files must contain the code listed later in this document.
  • SQL Server 2005 or SQL Server 2008, with the AdventureWorks2008 database attached, is installed on the local computer.
  • A Web browser is installed.
AdventureWorks example

The AdventureWorks Product Reviews example application demonstrates the following:

  • How to open a connection to SQL Server by using Windows Authentication.
  • How to execute a parameterized query with sqlsrv_query.
  • How to prepare and execute a parameterized query by using the combination of sqlsrv_prepare and sqlsrv_execute.
  • How to retrieve data by using sqlsrv_fetch_array.
  • How to retrieve data by using the combination of sqlsrv_fetch and sqlsrv_get_field.
  • How to retrieve data as a stream.
  • How to send data as a stream.
  • How to check for errors.

This example returns product information from the database for products whose names contain a string entered by the user. From the list of returned products, the user can see reviews, see an image, upload an image, and write a review for a selected product.

Put the following code in a file named adventureworks_demo.php:

PHPCopy

<!--=============  
This file is part of a Microsoft SQL Server Shared Source Application.  
Copyright (C) Microsoft Corporation.  All rights reserved.  
  
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY  
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE  
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A  
PARTICULAR PURPOSE.  
============= *-->  
  
<!--Note: The presentation formatting of the example application -->  
<!-- is intentionally simple to emphasize the SQL Server -->  
<!-- data access code.-->  
<html>  
<head>  
<title>AdventureWorks Product Reviews</title>  
</head>  
<body>  
<h1 align='center'>AdventureWorks Product Reviews</h1>  
<h5 align='center'>This application is a demonstration of the   
                   procedural API (SQLSRV driver) of the Microsoft  
                   Drivers for PHP for SQL Server.</h5><br/>  
<?php  
$serverName = "(local)\sqlexpress";  
$connectionOptions = array("Database"=>"AdventureWorks");  
  
/* Connect using Windows Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionOptions);  
if( $conn === false )  
die( FormatErrors( sqlsrv_errors() ) );  
  
if(isset($_REQUEST['action']))  
{  
switch( $_REQUEST['action'] )  
{  
/* Get AdventureWorks products by querying   
   against the product name.*/  
case 'getproducts':  
$params = array(&$_POST['query']);  
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice   
FROM Production.Product   
WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0";  
/*Execute the query with a scrollable cursor so  
  we can determine the number of rows returned.*/  
$cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET);  
$getProducts = sqlsrv_query($conn, $tsql, $params, $cursorType);  
if ( $getProducts === false)  
die( FormatErrors( sqlsrv_errors() ) );  
  
if(sqlsrv_has_rows($getProducts))  
{  
$rowCount = sqlsrv_num_rows($getProducts);  
BeginProductsTable($rowCount);  
while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC))  
{  
PopulateProductsTable( $row );  
}  
EndProductsTable();  
}  
else  
{  
DisplayNoProdutsMsg();  
}  
GetSearchTerms( !null );  
  
/* Free the statement and connection resources. */  
sqlsrv_free_stmt( $getProducts );  
sqlsrv_close( $conn );  
break;  
  
/* Get reviews for a specified productID. */  
case 'getreview':  
GetPicture( $_GET['productid'] );  
GetReviews( $conn, $_GET['productid'] );  
sqlsrv_close( $conn );  
break;  
  
/* Write a review for a specified productID. */  
case 'writereview':  
DisplayWriteReviewForm( $_POST['productid'] );  
break;  
  
/* Submit a review to the database. */  
case 'submitreview':  
/*Prepend the review so it can be opened as a stream.*/  
$comments = "data://text/plain,".$_POST['comments'];  
$stream = fopen( $comments, "r" );  
$tsql = "INSERT INTO Production.ProductReview (ProductID,  
   ReviewerName,  
   ReviewDate,  
   EmailAddress,  
   Rating,  
   Comments)   
 VALUES (?,?,?,?,?,?)";  
$params = array(&$_POST['productid'],  
&$_POST['name'],  
date("Y-m-d"),  
&$_POST['email'],  
&$_POST['rating'],   
&$stream);  
  
/* Prepare and execute the statement. */  
$insertReview = sqlsrv_prepare($conn, $tsql, $params);  
if( $insertReview === false )  
die( FormatErrors( sqlsrv_errors() ) );  
/* By default, all stream data is sent at the time of  
query execution. */  
if( sqlsrv_execute($insertReview) === false )  
die( FormatErrors( sqlsrv_errors() ) );   
sqlsrv_free_stmt( $insertReview );  
GetSearchTerms( true );  
  
/* Display a list of reviews, including the latest addition. */  
GetReviews( $conn, $_POST['productid'] );  
sqlsrv_close( $conn );  
break;  
  
        /* Display a picture of the selected product.*/  
        case 'displaypicture':  
            $tsql = "SELECT Name   
                     FROM Production.Product   
                     WHERE ProductID = ?";  
            $getName = sqlsrv_query($conn, $tsql,   
                                      array(&$_GET['productid']));  
            if( $getName === false )  
die( FormatErrors( sqlsrv_errors() ) );  
            if ( sqlsrv_fetch( $getName ) === false )  
die( FormatErrors( sqlsrv_errors() ) );  
            $name = sqlsrv_get_field( $getName, 0);  
            DisplayUploadPictureForm( $_GET['productid'], $name );  
            sqlsrv_close( $conn );  
            break;  
  
        /* Upload a new picture for the selected product. */  
        case 'uploadpicture':  
            $tsql = "INSERT INTO Production.ProductPhoto (LargePhoto)  
                     VALUES (?); SELECT SCOPE_IDENTITY() AS PhotoID";  
            $fileStream = fopen($_FILES['file']['tmp_name'], "r");  
            $uploadPic = sqlsrv_prepare($conn, $tsql, array(  
                       array(&$fileStream,   
                             SQLSRV_PARAM_IN,   
                             SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),  
                             SQLSRV_SQLTYPE_VARBINARY('max'))));  
            if( $uploadPic === false )  
die( FormatErrors( sqlsrv_errors() ) );  
            if( sqlsrv_execute($uploadPic) === false )  
die( FormatErrors( sqlsrv_errors() ) );  
  
/*Skip the open result set (row affected). */  
$next_result = sqlsrv_next_result($uploadPic);  
if( $next_result === false )  
die( FormatErrors( sqlsrv_errors() ) );  
  
/* Fetch the next result set. */  
if( sqlsrv_fetch($uploadPic) === false)  
die( FormatErrors( sqlsrv_errors() ) );  
  
/* Get the first field - the identity from INSERT. */  
$photoID = sqlsrv_get_field($uploadPic, 0);  
  
/* Associate the new photoID with the productID. */  
$tsql = "UPDATE Production.ProductProductPhoto  
 SET ProductPhotoID = ?  
 WHERE ProductID = ?";  
  
$reslt = sqlsrv_query($conn, $tsql, array(&$photoID, &$_POST['productid']));  
if($reslt === false )  
die( FormatErrors( sqlsrv_errors() ) );  
  
GetPicture( $_POST['productid']);  
DisplayWriteReviewButton( $_POST['productid'] );  
GetSearchTerms (!null);  
sqlsrv_close( $conn );  
break;  
}//End Switch  
}  
else  
{  
    GetSearchTerms( !null );  
}  
  
function GetPicture( $productID )  
{  
    echo "<table align='center'><tr align='center'><td>";  
    echo "<img src='photo.php?productId=".$productID."'   
      height='150' width='150'/></td></tr>";  
    echo "<tr align='center'><td><a href='?action=displaypicture&  
          productid=".$productID."'>Upload new picture.</a></td></tr>";  
    echo "</td></tr></table></br>";  
}  
  
function GetReviews( $conn, $productID )  
{  
    $tsql = "SELECT ReviewerName,   
             CONVERT(varchar(32), ReviewDate, 107) AS [ReviewDate],  
 Rating,   
 Comments   
             FROM Production.ProductReview   
             WHERE ProductID = ?   
             ORDER BY ReviewDate DESC";  
/*Execute the query with a scrollable cursor so  
  we can determine the number of rows returned.*/  
$cursorType = array("Scrollable" => SQLSRV_CURSOR_KEYSET);  
$getReviews = sqlsrv_query( $conn, $tsql, array(&$productID), $cursorType);  
if( $getReviews === false )  
die( FormatErrors( sqlsrv_errors() ) );  
if(sqlsrv_has_rows($getReviews))  
{  
$rowCount = sqlsrv_num_rows($getReviews);  
echo "<table width='50%' align='center' border='1px'>";  
echo "<tr bgcolor='silver'><td>$rowCount Reviews</td></tr></table>";  
while ( sqlsrv_fetch( $getReviews ) )  
{  
$name = sqlsrv_get_field( $getReviews, 0 );  
$date = sqlsrv_get_field( $getReviews, 1 );  
$rating = sqlsrv_get_field( $getReviews, 2 );  
/* Open comments as a stream. */  
$comments = sqlsrv_get_field( $getReviews, 3,   
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR));  
DisplayReview($productID,  
  $name,  
              $date,  
              $rating,  
              $comments );  
}  
}  
    else  
    {   
DisplayNoReviewsMsg();  
}  
    DisplayWriteReviewButton( $productID );  
    sqlsrv_free_stmt( $getReviews );  
}  
  
/*** Presentation and Utility Functions ***/  
  
function BeginProductsTable($rowCount)  
{  
    /* Display the beginning of the search results table. */  
$headings = array("Product ID", "Product Name",  
"Color", "Size", "Price");  
    echo "<table align='center' cellpadding='5'>";   
    echo "<tr bgcolor='silver'>$rowCount Results</tr><tr>";  
    foreach ( $headings as $heading )  
    {  
        echo "<td>$heading</td>";  
    }  
    echo "</tr>";  
}  
  
function DisplayNoProdutsMsg()  
{  
    echo "<h4 align='center'>No products found.</h4>";  
}  
  
function DisplayNoReviewsMsg()  
{  
    echo "<h4 align='center'>There are no reviews for this product.</h4>";  
}  
  
function DisplayReview( $productID, $name, $date, $rating, $comments)  
{  
    /* Display a product review. */  
    echo "<table style='WORD-BREAK:BREAK-ALL' width='50%'   
                 align='center' border='1' cellpadding='5'>";   
    echo "<tr>  
            <td>ProductID</td>  
            <td>Reviewer</td>  
            <td>Date</td>  
            <td>Rating</td>  
          </tr>";  
      echo "<tr>  
              <td>$productID</td>  
              <td>$name</td>  
              <td>$date</td>  
              <td>$rating</td>  
            </tr>  
            <tr>  
              <td width='50%' colspan='4'>";  
                 fpassthru( $comments );  
     echo "</td></tr></table><br/><br/>";  
}  
  
function DisplayUploadPictureForm( $productID, $name )  
{  
    echo "<h3 align='center'>Upload Picture</h3>";  
    echo "<h4 align='center'>$name</h4>";  
    echo "<form align='center' action='adventureworks_demo.php'  
                    enctype='multipart/form-data' method='POST'>  
<input type='hidden' name='action' value='uploadpicture'/>  
<input type='hidden' name='productid' value='$productID'/>  
<table align='center'>  
         <tr>  
           <td align='center'>  
             <input id='fileName' type='file' name='file'/>  
           </td>  
         </tr>  
         <tr>  
           <td align='center'>  
            <input type='submit' name='submit' value='Upload Picture'/>  
           </td>  
         </tr>  
</table>  
</form>";  
}  
  
function DisplayWriteReviewButton( $productID )  
{  
    echo "<table align='center'><form action='adventureworks_demo.php'   
                 enctype='multipart/form-data' method='POST'>  
          <input type='hidden' name='action' value='writereview'/>  
          <input type='hidden' name='productid' value='$productID'/>  
          <input type='submit' name='submit' value='Write a Review'/>  
          </p></td></tr></form></table>";  
}  
  
function DisplayWriteReviewForm( $productID )  
{  
    /* Display the form for entering a product review. */  
    echo "<h5 align='center'>Name, E-mail, and Rating are required fields.</h5>";  
    echo "<table align='center'>  
<form action='adventureworks_demo.php'   
                enctype='multipart/form-data' method='POST'>  
<input type='hidden' name='action' value='submitreview'/>  
<input type='hidden' name='productid' value='$productID'/>  
<tr>  
<td colspan='5'>Name: <input type='text' name='name' size='50'/></td>  
</tr>  
<tr>  
<td colspan='5'>E-mail: <input type='text' name='email' size='50'/></td>  
</tr>  
<tr>  
<td>Rating: 1<input type='radio' name='rating' value='1'/></td>  
<td>2<input type='radio' name='rating' value='2'/></td>  
<td>3<input type='radio' name='rating' value='3'/></td>  
<td>4<input type='radio' name='rating' value='4'/></td>  
<td>5<input type='radio' name='rating' value='5'/></td>  
</tr>  
<tr>  
<td colspan='5'>  
<textarea rows='20' cols ='50' name='comments'>[Write comments here.]</textarea>  
</td>  
</tr>  
<tr>  
<td colspan='5'>  
                 <p align='center'><input type='submit' name='submit' value='Submit Review'/>  
</td>  
</tr>  
</form>  
          </table>";  
}  
  
function EndProductsTable()  
{   
    echo "</table><br/>";   
}  
  
function GetSearchTerms( $success )  
{  
    /* Get and submit terms for searching the database. */  
    if (is_null( $success ))  
    {  
echo "<h4 align='center'>Review successfully submitted.</h4>";}  
echo "<h4 align='center'>Enter search terms to find products.</h4>";  
echo "<table align='center'>  
            <form action='adventureworks_demo.php'   
                  enctype='multipart/form-data' method='POST'>  
            <input type='hidden' name='action' value='getproducts'/>  
            <tr>  
               <td><input type='text' name='query' size='40'/></td>  
            </tr>  
            <tr align='center'>  
               <td><input type='submit' name='submit' value='Search'/></td>  
            </tr>  
            </form>  
            </table>";  
}  
  
function PopulateProductsTable( $values )  
{  
    /* Populate Products table with search results. */  
    $productID = $values['ProductID'];  
    echo "<tr>";  
    foreach ( $values as $key => $value )  
    {  
        if ( 0 == strcasecmp( "Name", $key ) )  
        {  
            echo "<td><a href='?action=getreview&productid=$productID'>$value</a></td>";  
        }  
        elseif( !is_null( $value ) )  
        {  
            if ( 0 == strcasecmp( "ListPrice", $key ) )  
            {  
                /* Format with two digits of precision. */  
                $formattedPrice = sprintf("%.2f", $value);  
                echo "<td>$$formattedPrice</td>";  
            }  
            else  
            {  
                echo "<td>$value</td>";  
            }  
        }  
        else  
        {  
            echo "<td>N/A</td>";  
        }  
    }  
    echo "<td>  
            <form action='adventureworks_demo.php'   
                  enctype='multipart/form-data' method='POST'>  
            <input type='hidden' name='action' value='writereview'/>  
            <input type='hidden' name='productid' value='$productID'/>  
            <input type='submit' name='submit' value='Write a Review'/>  
            </td></tr>  
            </form></td></tr>";  
}  
  
function FormatErrors( $errors )  
{  
    /* Display errors. */  
    echo "Error information: <br/>";  
  
    foreach ( $errors as $error )  
    {  
        echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";  
        echo "Code: ".$error['code']."<br/>";  
        echo "Message: ".$error['message']."<br/>";  
    }  
}  
?>  
</body>  
</html>

Conclusion

Connecting to SQL Server can be a very difficult process, but it’s important to take the time to learn how to do it. By using the Connector and using the Command Prompt, you can connect to your sql server in a variety of ways. By using these tools, you can increase your productivity and efficiency while working with your sql server.

Leave a Comment