SQL Server 2019 : Use from PHP2019/01/20 |
|
This is an example to use SQL Server from PHP.
|
|
| [1] | This is based on the environment PHP 7.3 has been installed. Additionally, Install other required packages. |
|
# install from Remi, EPEL [root@dlp ~]# yum --enablerepo=remi-safe,epel -y install php73-php-pdo php73-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/php73/php.d/30-pdo_sqlsrv.ini [root@dlp ~]# echo 'extension=sqlsrv.so' > /etc/opt/remi/php73/php.d/30-sqlsrv.ini |
| [2] | Create a sample User and Database for Test. |
|
[cent@dlp ~]$ sqlcmd -S localhost -U SA Password: # create login user 1> create login cent with PASSWORD= N'password'; 2> go # create [SampleDB] 1> create database SampleDB; 2> go 1> use SampleDB; 2> go Changed database context to 'SampleDB'. # create DB user 1> create user cent for login cent; 2> go # assign DB owner role to [cent] 1> exec sp_addrolemember 'db_owner', 'cent'; 2> go # create [SampleTable] 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] | These are some basic usage to connect to SQL Server from PHP. |
|
[cent@dlp ~]$
php -v PHP 7.3.13 (cli) (built: Dec 17 2019 10:29:15) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.13, Copyright (c) 1998-2018 Zend Technologies
[cent@dlp ~]$
vi use_mssql.php
<?php
$serverName = "127.0.0.1";
$connectionOptions = array(
"Database" => "SampleDB",
"Uid" => "cent",
"PWD" => "password"
);
$conn = sqlsrv_connect($serverName, $connectionOptions);
// Select from SampleTable
$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);
// Insert from SampleTable
$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 from SampleTable
$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 from SampleTable
$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 |