SQL Server 2017 : PHP で利用する2017/10/28 |
PHP からの SQL Server 利用例です。
|
|
[1] | PHP 7.0 インストール済みを前提として例示します。 追加で必要なパッケージをインストールしておきます。 |
# Remiからインストール [root@dlp ~]# yum --enablerepo=remi-safe -y groups install php70-php-pear php70-php-mbstring php70-php-pdo php70-php-devel [root@dlp ~]# yum -y groups install "Development Tools" [root@dlp ~]# pecl install sqlsrv pdo_sqlsrv [root@dlp ~]# echo 'extension=pdo_sqlsrv.so' > /etc/opt/remi/php70/php.d/30-pdo_sqlsrv.ini [root@dlp ~]# echo 'extension=sqlsrv.so' > /etc/opt/remi/php70/php.d/30-sqlsrv.ini |
[2] | テスト用のデータベース接続用ユーザーとデータベースを作成しておきます。 |
[cent@dlp ~]$ sqlcmd -S localhost -U SA Password: # ログインユーザー作成 1> create login cent with PASSWORD= N'password'; 2> go # [SampleDB] 作成 1> create database SampleDB; 2> go 1> use SampleDB; 2> go Changed database context to 'SampleDB'. # ログインユーザー [cent] と関連付けて DBユーザー作成 1> create user cent for login cent; 2> go # [cent] にDBオーナーロール付与 1> exec sp_addrolemember 'db_owner', 'cent'; 2> go # テストテーブル作成 1> create table SampleTable ( 2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50) 3> ); 4> insert into SampleTable ( 5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux' 6> ); 7> go |
[3] | 基本的な利用例です。データベースや接続ユーザーは上記で作成したものを使用します。 |
[cent@dlp ~]$
[cent@dlp ~]$ php -v PHP 7.0.25 (cli) (built: Oct 24 2017 17:24:01) ( NTS ) Copyright (c) 1997-2017 The PHP Group Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies mkdir MssqlTest [cent@dlp ~]$ cd MssqlTest
[cent@dlp MssqlTest]$
vi use_mssql.php <?php $serverName = "127.0.0.1"; $connectionOptions = array( "Database" => "SampleDB", "Uid" => "cent", "PWD" => "password" ); $conn = sqlsrv_connect($serverName, $connectionOptions); // SampleTable を Select $tsql= "select * from SampleTable;"; $getResults= sqlsrv_query($conn, $tsql); echo ("Reading data from SampleTable" . PHP_EOL); if ($getResults == FALSE) die(FormatErrors(sqlsrv_errors())); while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) { echo ($row['ID'] . " " . $row['First_Name'] . " " . $row['Last_Name'] . PHP_EOL); } sqlsrv_free_stmt($getResults); // SampleTable に Insert $tsql= "insert into SampleTable (First_Name, Last_Name) values (?,?);"; $params = array('Ubuntu','Linux'); $getResults= sqlsrv_query($conn, $tsql, $params); echo ("\nInserting a new row into SampleTable" . PHP_EOL); $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 $userToUpdate = 'Redhat'; $tsql= "update SampleTable set Last_Name = ? where First_Name = ?"; $params = array('Maipo', $userToUpdate); echo("\nUpdating Last_Name 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); $tsql= "select * from SampleTable;"; $getResults= sqlsrv_query($conn, $tsql); echo ("\nReading data from SampleTable" . PHP_EOL); if ($getResults == FALSE) die(FormatErrors(sqlsrv_errors())); while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) { echo ($row['ID'] . " " . $row['First_Name'] . " " . $row['Last_Name'] . PHP_EOL); } sqlsrv_free_stmt($getResults); // 特定の行を Delete $userToDelete = 'Ubuntu'; $tsql= "delete from SampleTable where First_Name = ?"; $params = array($userToDelete); $getResults= sqlsrv_query($conn, $tsql, $params); echo("\nDeleting 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); function FormatErrors( $errors ) { echo "Error information: "; foreach ( $errors as $error ) { echo "SQLSTATE: ".$error['SQLSTATE'].""; echo "Code: ".$error['code'].""; echo "Message: ".$error['message'].""; } } ?> php use_mssql.php Reading data from SampleTable 1 CentOS Linux 2 RedHat Linux 3 Fedora Linux Inserting a new row into SampleTable 1 row(s) inserted: Updating Last_Name for user Redhat 1 row(s) updated: Reading data from SampleTable 1 CentOS Linux 2 RedHat Maipo 3 Fedora Linux 8 Ubuntu Linux Deleting user Ubuntu 1 row(s) deleted: |
Sponsored Link |
|