1. Introduction
PHP is a powerful server-side scripting language that is widely used by developers. One of the common applications of PHP is database queries. In this article, we will discuss how to query MSSQL databases using PHP. MSSQL is a relational database management system developed by Microsoft, and it is commonly used in enterprise applications.
2. Setting up the Environment
2.1 Installing PHP Extension for MSSQL
In order to connect to MSSQL databases with PHP, we need to install the Microsoft PHP extension for MSSQL. This extension provides a set of functions that allow PHP scripts to communicate with an MSSQL database server.
To install the Microsoft PHP extension for MSSQL, we need to follow these steps:
Download the extension from the official website of Microsoft.
Extract the downloaded file on your computer.
Copy the extracted DLL file to the PHP extension directory.
Add the extension line in the PHP configuration file (php.ini).
Restart the Apache server.
Here is the extension line we need to add in the PHP configuration file:
extension=php_sqlsrv_7_nts_x64.dllAfter installing the extension, we can test its installation by running the following PHP code:
<?php
phpinfo();
?>
If the extension is installed correctly, we should see the MSSQL section in the output of the phpinfo() function.
2.2 Connecting to the MSSQL Database
Once we have installed the PHP extension for MSSQL, we can connect to an MSSQL database using PHP. Here is an example code to connect to a database:
<?php
$serverName = "localhost";
$connectionInfo = array("Database"=>"myDatabase", "UID"=>"myUsername", "PWD"=>"myPassword");
$conn = sqlsrv_connect($serverName, $connectionInfo);
if(!$conn) {
die( print_r( sqlsrv_errors(), true));
}
echo "Connected successfully";
?>
We need to provide the server name, database name, username, and password to connect to the database. After connecting to the database, we can execute queries on it.
3. Executing Queries
Now that we have connected to the MSSQL database, let's execute some queries. Here is an example code to retrieve data from a table:
<?php
$sql = "SELECT * FROM myTable";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false) {
die( print_r( sqlsrv_errors(), true));
}
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['columnName'];
}
?>
In the above code, we have executed a SELECT query on the "myTable" table and fetched the result set using the sqlsrv_fetch_array() function. This function retrieves the result set row by row as an associative array. We can access the values of columns using the column name.
3.1 Query Parameters
Query parameters are used to pass values to a query at runtime. By using parameters, we can write more secure and efficient queries. Here is an example code to execute a parameterized query:
<?php
$sql = "SELECT * FROM myTable WHERE column1 = ?";
$params = array("value1");
$stmt = sqlsrv_query($conn, $sql, $params);
if($stmt === false) {
die( print_r( sqlsrv_errors(), true));
}
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['columnName'];
}
?>
In the above code, we have used a question mark as a placeholder for the parameter value in the SQL query. We have passed the parameter value in an array to the sqlsrv_query() function. This function replaces the question mark with the actual value at runtime.
3.2 Stored Procedures
Stored procedures are precompiled SQL statements that are stored in a database. They can be executed repeatedly with different parameter values. Here is an example code to execute a stored procedure:
<?php
$procedure = "{CALL myProcedure(?, ?)}";
$params = array("value1", "value2");
$stmt = sqlsrv_query($conn, $procedure, $params);
if($stmt === false) {
die( print_r( sqlsrv_errors(), true));
}
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['columnName'];
}
?>
In the above code, we have executed the "myProcedure" stored procedure with two parameter values using the sqlsrv_query() function. The stored procedure returns a result set that we have fetched using the sqlsrv_fetch_array() function.
4. Conclusion
In this article, we have discussed how to query MSSQL databases using PHP. We have explored different methods of connecting to the database, executing queries, and handling result sets. By using the Microsoft PHP extension for MSSQL, we can write powerful and efficient database applications with PHP.