SQL Server 2022 : PHP から利用する2023/11/24 |
|
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.1/mods-available/pdo_sqlsrv.ini root@dlp:~# echo 'extension=sqlsrv.so' > /etc/php/8.1/mods-available/sqlsrv.ini root@dlp:~# phpenmod pdo_sqlsrv root@dlp:~# phpenmod sqlsrv |
| [2] | 基本的な利用例です。データベースや接続ユーザーは事前に適当に作成したものを使用します。 |
|
# 事前準備のテスト用データベース ubuntu@dlp:~$ sqlcmd -S localhost -U ubuntu -d SampleDB3 -Q 'select * from dbo.SampleTable;'
Password:
ID First_Name Last_Name
----------- ------------------- ---------------------
1 Ubuntu Linux
3 RedHat Plow
4 Windows Microsoft
(3 rows affected)
ubuntu@dlp:~$
vi use_mssql.php
<?php
$serverName = "127.0.0.1";
$connectionOptions = array(
"Database" => "SampleDB3",
"Uid" => "ubuntu",
"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('Debian','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('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 3 RedHat Plow 4 Windows Microsoft 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 3 RedHat RHEL 9 Plow 4 Windows Microsoft 5 Debian Linux Deleting user Windows 1 row(s) deleted:ubuntu@dlp:~$ sqlcmd -S localhost -U ubuntu -d SampleDB3 -Q 'select * from dbo.SampleTable;'
Password:
ID First_Name Last_Name
----------- ------------------ --------------------
1 Ubuntu Linux
3 RedHat RHEL 9 Plow
5 Debian Linux
(3 rows affected)
|
| Sponsored Link |