Ubuntu 20.04
Sponsored Link

SQL Server 2022 : Use with PHP2023/01/27

 
This is an example to use SQL Server with PHP.
[1] This is based on the environment PHP 7.4 has been installed.
Additionally, Install other required packages.
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/7.4/mods-available/pdo_sqlsrv.ini

root@dlp:~#
echo 'extension=sqlsrv.so' > /etc/php/7.4/mods-available/sqlsrv.ini

root@dlp:~#
phpenmod pdo_sqlsrv

root@dlp:~#
phpenmod sqlsrv

[2] These are some basic usage to connect to SQL Server with PHP.
# sample database for testing

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);

// Select data 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 data into SampleTable
$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 data in SampleTable
$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 data in SampleTable
$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']."";
    }
}
?>

ubuntu@dlp:~$
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)
Matched Content