Query sql server using php

Step 3: Proof of concept connecting to SQL using PHP

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("
"); $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!"); >> 

Источник

Шаг 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!"); >> 

Источник

Шаг 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!"); >> 

Источник

sqlsrv_query

$conn: The connection resource associated with the prepared statement.

$tsql: The Transact-SQL expression that corresponds to the prepared statement.

$params [OPTIONAL]: An array of values that correspond to parameters in a parameterized query. Each element of the array can be one of the following:

• A literal value.
• A PHP variable.
• An array with the following structure:

array($value [, $direction [, $phpType [, $sqlType]]]) 

The description for each element of the array is in the following table:

Element	Description
$value	A literal value, a PHP variable, or a PHP by-reference variable.
$direction[OPTIONAL]	One of the following SQLSRV_PARAM_* constants used to indicate the parameter direction: SQLSRV_PARAM_IN, SQLSRV_PARAM_OUT, SQLSRV_PARAM_INOUT. The default value is SQLSRV_PARAM_IN.

$options [OPTIONAL]: An associative array that sets query properties. It is the same list of keys also supported by sqlsrv_prepare.

Return Value

A statement resource. If the statement cannot be created and/or executed, false is returned.

Remarks

The sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply. This function provides a streamlined method to execute a query with a minimum amount of code. The sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

Example 1

In the following example, a single row is inserted into the Sales.SalesOrderDetail table of the AdventureWorks database. The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

Although the following example uses an INSERT statement to demonstrate the use of sqlsrv_query for a one-time statement execution, the concept applies to any Transact-SQL statement.

"AdventureWorks"); $conn = sqlsrv_connect($serverName, $connectionInfo); if ($conn === false) < echo "Could not connect.\n"; die(print_r(sqlsrv_errors(), true)); >/* Set up the parameterized query. */ $tsql = "INSERT INTO Sales.SalesOrderDetail (SalesOrderID, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount) VALUES (?, ?, ?, ?, ?, ?)"; /* Set parameter values. */ $params = array(75123, 5, 741, 1, 818.70, 0.00); /* Prepare and execute the query. */ $stmt = sqlsrv_query($conn, $tsql, $params); if ($stmt) < echo "Row successfully inserted.\n"; >else < echo "Row insertion failed.\n"; die(print_r(sqlsrv_errors(), true)); >/* Free statement and connection resources. */ sqlsrv_free_stmt($stmt); sqlsrv_close($conn); ?> 

Example 2

The following example updates a field in the Sales.SalesOrderDetail table of the AdventureWorks database. The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.

"AdventureWorks"); $conn = sqlsrv_connect($serverName, $connectionInfo); if ($conn === false) < echo "Could not connect.\n"; die(print_r(sqlsrv_errors(), true)); >/* Set up the parameterized query. */ $tsql = "UPDATE Sales.SalesOrderDetail SET OrderQty = (?) WHERE SalesOrderDetailID = (?)"; /* Assign literal parameter values. */ $params = array(5, 10); /* Execute the query. */ if (sqlsrv_query($conn, $tsql, $params)) < echo "Statement executed.\n"; >else < echo "Error in statement execution.\n"; die(print_r(sqlsrv_errors(), true)); >/* Free connection resources. */ sqlsrv_close($conn); ?> 

It is recommended to use strings as inputs when binding values to a decimal or numeric column to ensure precision and accuracy as PHP has limited precision for floating point numbers. The same applies to bigint columns, especially when the values are outside the range of an integer.

Example 3

This code sample shows how to bind a decimal value as an input parameter.

"YourTestDB"); $conn = sqlsrv_connect($serverName, $connectionInfo); if ($conn === false) < echo "Could not connect.\n"; die(print_r(sqlsrv_errors(), true)); >// Assume TestTable exists with a decimal field $input = "9223372036854.80000"; $params = array($input); $stmt = sqlsrv_query($conn, "INSERT INTO TestTable (DecimalCol) VALUES (?)", $params); sqlsrv_free_stmt($stmt); sqlsrv_close($conn); ?> 

Example 4

This code sample shows how to create a table of sql_variant types and fetch the inserted data.

$dbName, "UID"=>$uid, "PWD"=>$pwd); $conn = sqlsrv_connect($server, $options); if($conn === false) < die(print_r(sqlsrv_errors(), true)); >$tableName = 'testTable'; $query = "CREATE TABLE $tableName ([c1_int] sql_variant, [c2_varchar] sql_variant)"; $stmt = sqlsrv_query($conn, $query); if($stmt === false) < die(print_r(sqlsrv_errors(), true)); >sqlsrv_free_stmt($stmt); $query = "INSERT INTO [$tableName] (c1_int, c2_varchar) VALUES (1, 'test_data')"; $stmt = sqlsrv_query($conn, $query); if($stmt === false) < die(print_r(sqlsrv_errors(), true)); >sqlsrv_free_stmt($stmt); $query = "SELECT * FROM $tableName"; $stmt = sqlsrv_query($conn, $query); if(sqlsrv_fetch($stmt) === false) < die(print_r(sqlsrv_errors(), true)); >$col1 = sqlsrv_get_field($stmt, 0); echo "First field: $col1 \n"; $col2 = sqlsrv_get_field($stmt, 1); echo "Second field: $col2 \n"; sqlsrv_free_stmt($stmt); sqlsrv_close($conn); ?> 

The expected output would be:

First field: 1 Second field: test_data 

Источник