Step 3: Proof of concept connecting to SQL using PHP
Step 1: Connect
This OpenConnection function is called near the top in all of the functions that follow.
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;
}
Step 2: Execute query
The sqlsrv_query() function can be used to retrieve a result set from a query against SQL Database. This function essentially accepts any query and the connection object and returns a result set, which can be iterated over with the use of 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("<br/>");
$productCount++;
}
sqlsrv_free_stmt($getProducts);
sqlsrv_close($conn);
}
catch(Exception $e)
{
echo("Error!");
}
}
Step 3: Insert a row
In this example, you'll see how to execute an INSERT statement safely and pass parameters. Parameter values protect your application from SQL injection.
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!");
}
}
Step 4: Roll back a transaction
This code example demonstrates the use of transactions in which you:
Begin a transaction
Insert a row of data, Update another row of data
Commit your transaction if the insert and update were successful and roll back the transaction if one of them wasn't
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!");
}
}
More examples
Example Application (SQLSRV Driver)
Example Application (PDO_SQLSRV Driver)
Phản hồi
https://aka.ms/ContentUserFeedback.
Sắp ra mắt: Trong năm 2024, chúng tôi sẽ dần gỡ bỏ Sự cố với GitHub dưới dạng cơ chế phản hồi cho nội dung và thay thế bằng hệ thống phản hồi mới. Để biết thêm thông tin, hãy xem:Gửi và xem ý kiến phản hồi dành cho