PHP에서 MSSQL사용을위한 드라이버설치 SQLSRV

https://www.php.net/manual/en/book.sqlsrv.php

PHP: SQLSRV - Manual

 

https://learn.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver16 

Microsoft Drivers for PHP for SQL Server - PHP drivers for SQL Server

드라이버 다운로드

 

https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver16 

Download the Microsoft Drivers for PHP for SQL Server - PHP drivers for SQL Server

실제다운로드 페이지

설치일기준 버전

Version information

• Release number: 5.11.0
• Released: February 28, 2023

 

ODBC 드라이버

https://learn.microsoft.com/ko-kr/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16 

SQL Server용 ODBC 드라이버 다운로드 - ODBC Driver for SQL Server

 

SQLCMD

https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver16 

Download the Microsoft Drivers for PHP for SQL Server - PHP drivers for SQL Server

 

해당부분 설치후 압축푼후 내 버전에 맞게 처리한다.

PHP 8.1버전 사용중에 TS인 상황이므로 

php_pdo_sqlsrv_81_ts_x64.dll

php_sqlsrv_81_ts_x64.dll

파일을 복사하여

PHP설치폴더의 하위폴더 ext 파일에 복사하여 넣는다.

php.ini 파일에 extension부분에 해당 파일 둘을 추가한다.

extension=php_sqlsrv_81_ts_x64.dll
extension=php_pdo_sqlsrv_81_ts_x64.dll

 

<?php
$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);

//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);

//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);


//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);
}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
?>

 

추가로 한글깨짐을 방지하기위해 접속구문에 CharacterSet을 추가한다.

$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password",
    "CharacterSet" => "UTF-8"
);