SQL Server 2022 : PHP から利用する2023/12/15 |
PHP からの SQL Server 利用例です。
|
|
[1] | PHP インストール済みを前提として例示します。 追加で必要なパッケージをインストールしておきます。 |
root@dlp:~# apt -y install gcc make php-pear php-pdo php-dev root@dlp:~# pecl install sqlsrv pdo_sqlsrv root@dlp:~# echo 'extension=pdo_sqlsrv.so' > /etc/php/8.2/mods-available/pdo_sqlsrv.ini root@dlp:~# echo 'extension=sqlsrv.so' > /etc/php/8.2/mods-available/sqlsrv.ini root@dlp:~# phpenmod pdo_sqlsrv root@dlp:~# phpenmod sqlsrv |
[2] | 基本的な利用例です。データベースや接続ユーザーは事前に適当に作成したものを使用します。 |
# 事前準備のテスト用データベース debian@dlp:~$ sqlcmd -S localhost -U debian -d SampleDB3 -Q 'select * from dbo.SampleTable;' Password: ID First_Name Last_Name ----------- ------------------ ----------------- 1 Ubuntu Linux 2 Debian Linux 3 RedHat Plow (3 rows affected)
debian@dlp:~$
vi use_mssql.php <?php $serverName = "127.0.0.1"; $connectionOptions = array( "Database" => "SampleDB3", "Uid" => "debian", "PWD" => "P@ssw0rd01" ); $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('Windows','Microsoft'); $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('RHEL 9 Plow', $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 = 'Windows'; $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 Ubuntu Linux 2 Debian Linux 3 RedHat Plow 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 Ubuntu Linux 2 Debian Linux 3 RedHat RHEL 9 Plow 5 Windows Microsoft Deleting user Windows 1 row(s) deleted:debian@dlp:~$ sqlcmd -S localhost -U debian -d SampleDB3 -Q 'select * from dbo.SampleTable;' Password: ID First_Name Last_Name ----------- -------------------- ------------------- 1 Ubuntu Linux 2 Debian Linux 3 RedHat RHEL 9 Plow (3 rows affected) |
Sponsored Link |