Basic Usage of SQL Server in a PHP Application

Microsoft SQL Server is a powerful relational database management system (RDBMS) used in various enterprise applications. In this article, we’ll explore the fundamental steps to use SQL Server in a PHP application. We’ll cover database connection, data manipulation, and error handling to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

1. Microsoft SQL Server: SQL Server should be installed and running on your server or local development environment. You can download SQL Server from the official Microsoft website.

2. PHP: Ensure you have PHP installed. You can download PHP from the official PHP website.

3. SQL Server Driver for PHP: Install the SQL Server driver for PHP. You can download it from the Microsoft PHP Drivers for SQL Server page.

Step 1: Connecting to SQL Server

To connect to a SQL Server database from a PHP application, you’ll use the SQLSRV extension. Create a connection to the SQL Server database:

<?php
$serverName = "your_server_name";
$connectionOptions = array(
    "Database" => "your_database_name",
    "Uid" => "your_username",
    "PWD" => "your_password"
);

// Create a connection
$connection = sqlsrv_connect($serverName, $connectionOptions);

// Check the connection
if (!$connection) {
    die("Connection failed: " . sqlsrv_errors());
}

echo "Connected successfully";
?>

Replace "your_server_name", "your_database_name", "your_username", and "your_password" with your SQL Server connection details.

Step 2: Executing SQL Queries

Once connected, you can execute SQL queries to interact with the database. Let’s start with a simple example: inserting data into a table.

<?php
// SQL query to insert data into a "users" table
$query = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";

$insertResult = sqlsrv_query($connection, $query);

if ($insertResult === false) {
    die("Error: " . sqlsrv_errors());
}

echo "Data inserted successfully";
?>

Step 3: Querying Data

You can retrieve data from SQL Server tables using SQL queries. Here’s an example of selecting data from a table:

<?php
// SQL query to retrieve data from the "users" table
$query = "SELECT id, username, email FROM users";

$queryResult = sqlsrv_query($connection, $query);

if ($queryResult === false) {
    die("Error: " . sqlsrv_errors());
}

while ($row = sqlsrv_fetch_array($queryResult, SQLSRV_FETCH_ASSOC)) {
    echo "ID: " . $row['id'] . ", Username: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}
?>

Step 4: Updating and Deleting Data

Updating and deleting data in SQL Server is straightforward. Here’s an example of updating a user’s email address:

<?php
// SQL query to update a user's email address
$query = "UPDATE users SET email='[email protected]' WHERE username='john_doe'";

$updateResult = sqlsrv_query($connection, $query);

if ($updateResult === false) {
    die("Error: " . sqlsrv_errors());
}

echo "Data updated successfully";
?>

And here’s an example of deleting a user:

<?php
// SQL query to delete a user
$query = "DELETE FROM users WHERE username='john_doe'";

$deleteResult = sqlsrv_query($connection, $query);

if ($deleteResult === false) {
    die("Error: " . sqlsrv_errors());
}

echo "User deleted successfully";
?>

Step 5: Error Handling and Closing the Connection

Always handle errors gracefully and close the SQL Server connection when done.

<?php
// Closing the SQL Server connection
sqlsrv_close($connection);
?>

Conclusion

In this article, we’ve covered the basic usage of SQL Server in a PHP application. You’ve learned how to connect to a SQL Server database, execute SQL queries for data manipulation, retrieve data, update records, and delete records. SQL Server is a robust RDBMS commonly used in enterprise applications, and these fundamental skills are essential for building database-driven PHP web applications. As you continue your journey in web development, you’ll explore more complex database operations and best practices for secure and efficient data management.