Microsoft sql server with php

Create PHP apps using SQL Server on Windows

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

Step 2.1 Install the PHP Drivers for SQL Server

Download the Microsoft PHP Drivers for SQL Server from the download page.

Pick the appropriate dll — for example php_pdo_sqlsrv_74_nts.dll for the PDO Driver and php_sqlsrv_74_nts.dll for the SQLSRV driver.

Copy the dll files to the C:\Program Files\iis express\PHP\v7.4\ext folder.

Register the dll files in the php.ini file.

 cd C:\Program^ Files\iis^ express\PHP\v7.4\ext echo extension=php_sqlsrv_74_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.4\php.ini echo extension=php_pdo_sqlsrv_74_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.4\php.ini 

Step 2.2 Create a database for your application

Create the database using sqlcmd.

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;" 

Step 2.3 Create a PHP app that connects to SQL Server and executes queries

mkdir SqlServerSample cd SqlServerSample 

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

 $serverName = "localhost"; $connectionOptions = array( "Database" => "SampleDB", "Uid" => "sa", "PWD" => "your_password" ); //Establishes the connection $conn = sqlsrv_connect($serverName, $connectionOptions); if($conn) echo "Connected!" ?> 

Run your PHP script from the terminal.

Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;" sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));" sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');" sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;" 

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

 $serverName = "localhost"; $connectionOptions = array( "Database" => "SampleDB", "Uid" => "sa", "PWD" => "your_password" ); //Establishes the connection $conn = sqlsrv_connect($serverName, $connectionOptions); //Insert Query echo ("Inserting a new row into table" . PHP_EOL); $tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (. );"; $params = array('Jake','United States'); $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) inserted: " . PHP_EOL); sqlsrv_free_stmt($getResults); //Update Query $userToUpdate = 'Nikita'; $tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?"; $params = array('Sweden', $userToUpdate); echo("Updating Location 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); //Delete Query $userToDelete = 'Jared'; $tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?"; $params = array($userToDelete); $getResults= sqlsrv_query($conn, $tsql, $params); echo("Deleting 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); //Read Query $tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;"; $getResults= sqlsrv_query($conn, $tsql); echo ("Reading data from table" . PHP_EOL); if ($getResults == FALSE) die(FormatErrors(sqlsrv_errors())); while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC))  echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL); > sqlsrv_free_stmt($getResults); function FormatErrors( $errors )  /* Display errors. */ echo "Error information: "; foreach ( $errors as $error )  echo "SQLSTATE: ".$error['SQLSTATE'].""; echo "Code: ".$error['code'].""; echo "Message: ".$error['message'].""; > > ?> 

Run your PHP script from the terminal.

Inserting a new row into table 1 row(s) inserted: Updating Location for user Nikita 1 row(s) updated: Deleting user Jared 1 row(s) deleted: Reading data from table 2 Nikita Sweden 3 Tom Germany 4 Jake United States 

Congratulations! You have created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Have Questions?

Happy to help! You can find us on GitHub, MSDN Forums, and StackOverflow. We also monitor the #SQLServerDev hashtag on Twitter.

Источник

Шаг 3. Подтверждение концепции: подключение к SQL с помощью PHP

Эта функция OpenConnection вызывается перед выполнением всех последующих функций.

 function OpenConnection() < $serverName = "tcp:myserver.database.windows.net,1433"; $connectionOptions = array("Database"=>"AdventureWorks", "Uid"=>"MyUser", "PWD"=>"MyPassword"); $conn = sqlsrv_connect($serverName, $connectionOptions); if($conn == false) die(FormatErrors(sqlsrv_errors())); return $conn; > 

Шаг 2. Выполнение запроса

Функция sqlsrv_query может использоваться для извлечения результирующего набора из запроса к базе данных SQL. Эта функция фактически принимает любой запрос и объект подключения, а затем возвращает результирующий набор для итеративного перебора с помощью sqlsrv_fetch_array().

 function ReadData() < try < $conn = OpenConnection(); $tsql = "SELECT [CompanyName] FROM SalesLT.Customer"; $getProducts = sqlsrv_query($conn, $tsql); if ($getProducts == FALSE) die(FormatErrors(sqlsrv_errors())); $productCount = 0; while($row = sqlsrv_fetch_array($getProducts, SQLSRV_FETCH_ASSOC)) < echo($row['CompanyName']); echo("
"); $productCount++; > sqlsrv_free_stmt($getProducts); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Шаг 3. Вставка строки

В этом примере показано, как безопасно выполнить инструкцию INSERT и передать параметры. Значения параметров защищают приложение от внедрения кода SQL.

 function InsertData() < try < $conn = OpenConnection(); $tsql = "INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT" . " INSERTED.ProductID VALUES ('SQL Server 1', 'SQL Server 2', 0, 0, getdate())"; //Insert query $insertReview = sqlsrv_query($conn, $tsql); if($insertReview == FALSE) die(FormatErrors( sqlsrv_errors())); echo "Product Key inserted is :"; while($row = sqlsrv_fetch_array($insertReview, SQLSRV_FETCH_ASSOC)) < echo($row['ProductID']); >sqlsrv_free_stmt($insertReview); sqlsrv_close($conn); > catch(Exception $e) < echo("Error!"); >> 

Шаг 4. Откат транзакции

Этот пример кода демонстрирует использование транзакций, в которых можно:

• начать транзакцию;
• вставить строку данных, обновить другую строку данных;
• зафиксировать транзакцию, если запросы на вставку и обновление выполнены успешно, или откатить транзакцию, если один из запросов вызвал ошибку.

 function Transactions() < try < $conn = OpenConnection(); if (sqlsrv_begin_transaction($conn) == FALSE) die(FormatErrors(sqlsrv_errors())); $tsql1 = "INSERT INTO SalesLT.SalesOrderDetail (SalesOrderID,OrderQty,ProductID,UnitPrice) VALUES (71774, 22, 709, 33)"; $stmt1 = sqlsrv_query($conn, $tsql1); /* Set up and execute the second query. */ $tsql2 = "UPDATE SalesLT.SalesOrderDetail SET OrderQty = (OrderQty + 1) WHERE ProductID = 709"; $stmt2 = sqlsrv_query( $conn, $tsql2); /* If both queries were successful, commit the transaction. */ /* Otherwise, rollback the transaction. */ if($stmt1 && $stmt2) < sqlsrv_commit($conn); echo("Transaction was commited"); >else < sqlsrv_rollback($conn); echo "Transaction was rolled back.\n"; >/* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt1); sqlsrv_free_stmt( $stmt2); > catch(Exception $e) < echo("Error!"); >> 

Источник

Connecting to the Server

The topics in this section describe the options and procedures for connecting to SQL Server with the Microsoft Drivers for PHP for SQL Server.

The Microsoft Drivers for PHP for SQL Server can connect to SQL Server by using Windows Authentication or by using SQL Server Authentication. By default, the Microsoft Drivers for PHP for SQL Server try to connect to the server by using Windows Authentication.

In This Section

Topic	Description
How to: Connect Using Windows Authentication	Describes how to establish a connection by using Windows Authentication.
How to: Connect Using SQL Server Authentication	Describes how to establish a connection by using SQL Server Authentication.
How to: Connect Using Azure Active Directory Authentication	Describes how to set the authentication mode and connect using Azure Active Directory identities.
How to: Connect on a Specified Port	Describes how to connect to the server on a specific port.
Connection Pooling	Provides information about connection pooling in the driver.
How to: Disable Multiple Active Resultsets (MARS)	Describes how to disable the MARS feature when making a connection.
Connection Options	Lists the options that are permitted in the associative array that contains connection attributes.
Support for LocalDB	Describes Microsoft Drivers for PHP for SQL Server support for the LocalDB feature, which was added in SQL Server 2012 (11.x).
Support for High Availability, Disaster Recovery	Discusses how your application can be configured to take advantage of the high-availability, disaster recovery features added in SQL Server 2012 (11.x).
Connecting to Microsoft Azure SQL Database	Discusses how to connect to an Azure SQL Database.
Connection Resiliency	Discusses the connection resiliency feature that reestablishes broken connections.

Источник

Create PHP apps using SQL Server on SLES

Note: To ensure optimal performance of SQL Server, your machine should have at least 4 GB of memory.

Register the Microsoft Linux repository and add they keys.

 sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/mssql-server-2017.repo sudo zypper --gpg-auto-import-keys refresh 

 sudo zypper update sudo zypper install mssql-server 

 sudo /opt/mssql/bin/mssql-conf setup 

Microsoft(R) SQL Server(R) Setup To abort setup at anytime, press Ctrl-C. The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746388 and found in /usr/share/doc/mssql-server/LICENSE.TXT. Do you accept the license terms? If so, please type YES: Please enter a password for the system administrator (SA) account: Please confirm the password for the system administrator (SA) account: 

You now have SQL Server running locally on your SLES machine! Check out the next section to continue installing prerequisites.

Step 1.2 Install PHP and other required packages

In the following instructions, replace with your version of Suse — if you are using Suse Enterprise Linux 15, it will be SLE_15 or SLE_15_SP1. For Suse 12, use SLE_12_SP4 (or above if applicable). Not all versions of PHP are available for all versions of Suse Linux — please refer to http://download.opensuse.org/repositories/devel:/languages:/php to see which versions of Suse have the default version PHP available, or to http://download.opensuse.org/repositories/devel:/languages:/php:/ to see which other versions of PHP are available for which versions of Suse.

Packages for PHP 7.4 are not available for Suse 12. To install PHP 7.2, replace the repository URL below with the following URL: https://download.opensuse.org/repositories/devel:/languages:/php:/php72//devel:languages:php:php72.repo . To install PHP 7.3, replace the repository URL below with the following URL: https://download.opensuse.org/repositories/devel:/languages:/php:/php73//devel:languages:php:php73.repo .

sudo su zypper -n ar -f https://download.opensuse.org/repositories/devel:languages:php//devel:languages:php.repo zypper --gpg-auto-import-keys refresh zypper -n install php7 php7-devel php7-openssl 

You have successfully installed PHP on your SLES machine!

Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

sudo su #Download appropriate package for the OS version #Choose only ONE of the following, corresponding to your OS version #SUSE Linux Enterprise Server 11 SP4 #Ensure SUSE Linux Enterprise 11 Security Module has been installed zypper ar https://packages.microsoft.com/config/sles/11/prod.repo #SUSE Linux Enterprise Server 12 zypper ar https://packages.microsoft.com/config/sles/12/prod.repo #SUSE Linux Enterprise Server 15 zypper ar https://packages.microsoft.com/config/sles/15/prod.repo #(Only for driver 17.3 and below) SUSEConnect -p sle-module-legacy/15/x86_64 exit sudo ACCEPT_EULA=Y zypper install msodbcsql17 # optional: for bcp and sqlcmd sudo ACCEPT_EULA=Y zypper install mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc # optional: for unixODBC development headers sudo zypper install unixODBC-devel 

After installing SQLCMD, you can connect to SQL Server using the following command:

sqlcmd -S localhost -U sa -P yourpassword 1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements. 

This how to run a basic inline query. The results will be printed to the STDOUT.

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION" 

-------------------------------------------------------- Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64) Apr 13 2017 11:44:40 Copyright (c) Microsoft Corporation on Linux (SUSE Linux Enterprise Server 12 SP2) 1 rows(s) returned Executed in 1 ns 

You have successfully installed SQL Server Command Line Utilities on your SLES machine!

You have successfully installed the PHP Driver for SQL Server on your SLES machine. You now have everything you need to start writing PHP apps with SQL Server!

Have Questions?

Happy to help! You can find us on GitHub, MSDN Forums, and StackOverflow. We also monitor the #SQLServerDev hashtag on Twitter.

Источник