Installing SQL Server and connecting with PHP

The SQL Server is one of the most famous and well-known databases in the world, being widely used in the corporate environment. In this article, we will learn how to install SQL Server on our computer and connect it with PHP. We will see what challenges may arise in making this connection and what the best ways to solve them are.

Installing SQL Server

To install SQL Server 2019, we will access the page with the download options on Microsoft’s website.

SQL Server is usually a paid product. However, we can download SQL Server Express, a free version of this Database. We will choose the following option:

After downloading, we will start the installation. The first window we will see will display three installation options. We will select the basic installation, which will install the default settings for SQL Server:

Next, we will see a window with the SQL Server Terms of Use. Just click on “Accept” (as we always do in any installation ?).

Next, we will see the window with the installation location for SQL Server, and we can select the folder we want. Then, just click on “Install” and wait for the process to finish:

After the installation, we will see the following success message:

Installing SSMS

Now that we have installed the SQL Server service on the computer, we need to install the graphical interface where we can manage our connections and our Databases. In SQL Server, this interface is called “SQL Server Management Studio,” or SSMS. Note that in the success window of the installation, we can see a button to install this interface:

By clicking on this option, we will be redirected to this site:

We will click on the blue link to download SSMS.

After the download is complete, we will start the installation. The first installation window will show us where SSMS will be installed, and we can change the folder if we wish. Then, just click on “Install”:

The installation process for this interface's packages on our computer will start. Once SSMS is installed, we will be asked to restart the computer:

After the computer is restarted, we will see the SSMS option in our Start Menu. In the image below, it is the fourth option:

When starting this interface, we will be asked to authenticate. In our case, we will choose the “Windows Authentication” option:

This will log us into SQL Server. However, to connect it with PHP, it is more common to have a username and password. We will configure this now. To do this, we will right-click on Security > New > Login.

In the next window, we will select the SQL Server authentication option and then provide a name for our user and a password for it. In our case, our user will be named “hcode” and the password will be “root”:

The next configuration we will make is regarding the privileges that this user will have. To add them, we will click on “Server Roles,” which will be in our left menu. In this window, we will give this user the “public” and “sysadmin” privileges:

Remembering that we are only granting these privileges to the user because we are in a development machine. In everyday use, these privileges are managed by the company's DBA and are delegated with great attention and caution.

After this configuration, just click on “Ok” at the bottom of the page, and we will have a new authenticated user in our interface.

However, this will not be enough to authenticate our user when connecting to PHP. One last adjustment is necessary. Go to the name of our main SQL Server user, right-click on it, and then click on Properties:

In the window that opens, click on Security, which will be in the left menu, and then select the option “SQL Server and Windows Authentication mode”:

So many settings, right? But now we have reached the end and we are ready to create our first Database.

Creating our first Database in SQL Server

Now that the installation and configuration of SQL Server has been completed, let’s create our first Database. To do this, we will right-click on Databases and select the New Database option:

In the window that opens, we will see the option to create the Database name, and we will set it as “hcodedb”. Then we will click on “Ok” and have a new Database created:

Our Database will appear in the left menu of our interface. To create our first project, we will right-click on it and then select “New Query”:

A blank window will open where we can execute our codes. Let’s create a users table with the following script:

CREATE TABLE users (
	id INT PRIMARY KEY NOT NULL IDENTITY,
	name VARCHAR(64) NOT NULL,
	age INT NOT NULL
);

Just a reminder that to execute a command in SQL Server, we need to select the lines we want to execute and press F5 or Ctrl + R.

After creating the table, we will populate it with some records, using the following script:

INSERT INTO users (name, age) VALUES ('João Rangel', 25), ('Glaucio Daniel', 26), ('Djalma Sindeaux', 27);

When selecting the data from the users table, we will see the following result:

Our Database is already functioning, which is excellent. The next step is to connect PHP to SQL Server. We will use version 7.4.6 of PHP, installed via XAMPP.

To connect to SQL Server, we first need to download some DLLs. But what are DLLs?

What are DLLs

DLL stands for “Dynamic Link Library.” It is a file that contains code that can be executed by different programs at the same time. The various functionalities of PHP consist of using DLLs. Most of the features of this language come with the DLLs defined in its structure, such as MySQL, for example. However, the SQL Server DLLs are not defined by default. We need to download them.

For this download, we will access the official Microsoft page on GitHub.

This page lists several versions of SQL Server drivers for PHP. We will select version 5.8.0:

When we open this page, we will see a list of several files that contain the desired DLLs. We will select the .zip file that contains the name of our Operating System along with the version of our PHP. In my case, it will be Windows, with version 7.4:

After downloading, we will extract the folder containing our DLLs. We will notice that this folder has two options: x64 (64-bit computers) and x86 (32-bit computers). We can select the folder that corresponds to our computer's architecture.

We will paste these DLLs into the xampp/php/ext folder. Then, we need to reference these DLLs in our php.ini file, which is located in the xampp/php folder. We will add the following configurations to this file:

extension=php_pdo_sqlsrv_74_nts.dll
extension=php_pdo_sqlsrv_74_ts.dll
extension=php_sqlsrv_74_nts.dll
extension=php_sqlsrv_74_ts.dll

They will be set around line 910 of the php.ini file.

We need to restart our XAMPP server. Finally, we will make the request to SQL Server using PHP with PDO. We will create a file called index.php and it will have the following content:

<?php

$connection = new PDO("sqlsrv:Database=hcodedb;server=localhost\SQLEXPRESS", "hcode", "root");

$statement = $connection->prepare("SELECT * FROM users;");

$statement->execute();

$users = $statement->fetchAll(PDO::FETCH_ASSOC);

?>

In this code, we create a connection to SQL Server and inform a query to be executed on the Database that will select all registered users. We store the results of the query in a variable called $users.

Remembering that we explain in detail the function of each of the methods above in our Complete PHP 7 Course, where we learn all the concepts of PHP and develop an amazing real project, with over 130 classes and 33 hours of content.

To display this data on our page, we will use HTML code that will have a table positioned through Flexbox. The content of the file will be as follows:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>User List</title>
    <style>
        body {
            display: flex;
            justify-content: center;
            align-items: center;
        }
        table, th, tr, td {
            padding: 10px;
            font-size: 25px;
            border-collapse: collapse;
        }
        table, th {
            border: 2px solid #FF760C;
        }
        td {
            border-bottom: 1px solid #8395a7;
            border-right: 1px solid #8395a7;
        }
    </style>
</head>
<body>

    <table>
        <thead>
            <tr>
                <th>User Name</th>
                <th>Age</th>
            </tr>
        </thead>

        <tbody>

            <?php foreach ($users as $user): ?>
                <tr>
                    <td><?php echo $user['name'] ?></td>
                    <td><?php echo $user['age'] ?></td>
                </tr>
            <?php endforeach; ?>

        </tbody>
</table>

</body>
</html>

The result in the browser will be as follows:

Excellent, our code worked!!! Our connection to SQL Server was successful, and now our data is being loaded dynamically ?.

You can find the complete code developed in this article on our GitHub: https://github.com/hcodebr/blog-php

SQL Server is a very powerful Database, and in this article we learned how to get started with creating great projects with this incredible tool for our Development.

Thank you for reading this far and see you in the next article :)