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'].""; } } ?> 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 |