How To Retrieve Data From a SQL Server Table Into An HTML Page Using PHP

In this article, we will be discussing how to retrieve data from a Microsoft SQL Server database and display it on an HTML page using the PHP sqlsrv extension. We will start by setting up the necessary configurations on the server and download the latest version of the Microsoft SQL Server Driver for PHP. Then, we will proceed to creating a connection to the database, querying the data, and displaying it in an HTML table format. This guide is perfect for those who are new to working with Microsoft SQL Server and PHP and want to learn how to connect and retrieve data from the database.

Start SQL Server Management Studio

To start SQL Server Management Studio (SSMS), you will first need to have the software installed on your computer. You can download the latest version of SSMS from the Microsoft website. – https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16&tabs=command-line

Once you have the software installed, you can start it by following these steps:

1. Click on the Start button and type “SQL Server Management Studio” in the search box.

2. Select “SQL Server Management Studio” from the list of results.
3. The SSMS application will launch and you will be prompted to connect to a SQL Server.
4. Enter the server name and your authentication details then click on the “Connect” button.

If you have multiple versions of SQL Server installed on your computer, you may need to select the appropriate version of SSMS to launch.

Alternatively you can also launch SSMS from your windows Start menu and look for Microsoft SQL Server version installed.

Please make sure that your SQL Server is running before starting SSMS.

How to create new query in sql server

There are several ways to create new queries in SQL Server:

1. Using SQL Server Management Studio (SSMS): This is a graphical user interface (GUI) that allows you to create and manage SQL Server databases. To create a new query, you can open SSMS, connect to a SQL Server instance, and then open a new query window by clicking on the “New Query” button or by pressing (Ctrl + N).

2. Once the new query window is open, you can enter your SQL code and execute it by clicking on the “Execute” button or by pressing (F5).
3. Using the command line: You can use the sqlcmd command-line utility to create and execute SQL queries. To create a new query, you can open the command prompt, navigate to the directory where sqlcmd is installed, and then type sqlcmd to start the utility. Once the sqlcmd prompt is open, you can enter your SQL code and execute it by pressing (Enter)
4. Using SQL Server stored procedure: You can create a stored procedure, which is a pre-compiled collection of SQL statements that can be executed with a single call. To create a new stored procedure, you can use the “CREATE PROCEDURE” statement and then enter your SQL code. Once the stored procedure is created, you can execute it by calling it by its name.

Creating a Database and Table in SQL Server – A Step-by-Step Guide

Here is an example of a SQL query that can be used to create a new database called “mydatabase” and a table called “mytable” with columns “id”, “name”, “email”, and “phone” in SQL Server Management Studio:

CREATE DATABASE mydatabase;
GO

USE mydatabase;
GO

CREATE TABLE mytable (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL
);
GO

This query creates a new database named “mydatabase” and a table named “mytable” with four columns: “id”, “name”, “email”, and “phone”. The “id” column is set as the primary key and will be auto-incrementing using the IDENTITY property. The “name”, “email”, and “phone” columns are set as not null, which means that they cannot contain a null value. You can run this query in SQL Server Management Studio, it will create the database and table for you.

Insert 10 rows to sql server database

Here is an example of a SQL query that can be used to insert 10 rows of data into a table called “mytable” in a SQL Server database:

INSERT INTO mytable (name, email, phone)
VALUES ('John Doe', 'john.doe@example.com', '555-555-5555'),
       ('Jane Smith', 'jane.smith@example.com', '555-555-5556'),
       ('Bob Johnson', 'bob.johnson@example.com', '555-555-5557'),
       ('Sara Lee', 'sara.lee@example.com', '555-555-5558'),
       ('Mike Brown', 'mike.brown@example.com', '555-555-5559'),
       ('Amy Taylor', 'amy.taylor@example.com', '555-555-5560'),
       ('Chris Evans', 'chris.evans@example.com', '555-555-5561'),
       ('Emily Davis', 'emily.davis@example.com', '555-555-5562'),
       ('Jacob Miller', 'jacob.miller@example.com', '555-555-5563'),
       ('Nicholas Garcia', 'nicholas.garcia@example.com', '555-555-5564')

You can run this query in SQL Server Management Studio, it will insert 10 rows of data into the “mytable” table in the database. You should replace the column name and values with your own data. It’s also important to sanitize your data to prevent SQL injection attacks.

How To Run Select Query To Confirm If Data Has Been Inserted

Here is an example of a SQL query that can be used to select all the rows from a table called “mytable” in a SQL Server database and confirm if the data has been inserted:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [id]
      ,[name]
      ,[email]
      ,[phone]
  FROM [mydatabase].[dbo].[mytable]

You can run this query in SQL Server Management Studio to retrieve all the rows from the “mytable” table, including the data you have just inserted. You will be able to see the inserted data and confirm that it has been successfully inserted into the table.

Microsoft SQL Server Driver for PHP

The Microsoft SQL Server Driver for PHP plays a crucial role in allowing the PHP code to connect and interact with the Microsoft SQL Server database. Without the driver, the PHP code would not be able to connect to the database and retrieve the necessary data to display on the HTML page. The driver essentially acts as a bridge between the PHP code and the SQL Server, allowing for seamless communication between the two. Additionally, the driver also provides functions such as sqlsrv_connect() and sqlsrv_query() that can be used in the PHP code to perform various database operations, further simplifying the process of interacting with the SQL Server. Overall, the Microsoft SQL Server Driver for PHP is an essential component for this project as it enables the PHP code to successfully connect to and retrieve data from the SQL Server database.

Installing the Microsoft SQL Server Driver for PHP

1. Download the Microsoft SQL Server Driver for PHP. You can find the latest version here: https://learn.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver16
2. Extract the downloaded file and copy the php_sqlsrv.dll and php_pdo_sqlsrv.dll files.
3. Copy the PHP extension files (php_sqlsrv_.dll and php_pdo_sqlsrv_.dll) to the PHP extension folder (e.g., C:\xampp\php\ext)

4. Paste the files in the PHP extension directory. The location of the directory can be found in the php.ini file, by searching for “extension_dir”.

5. In the php.ini file, add the following lines to enable the sqlsrv extension:

extension=php_pdo_sqlsrv_81_ts_x64.dll
extension=php_sqlsrv_81_ts_x64.dll

6. Restart the web server for the changes to take effect.

7. Verify that the sqlsrv extension is enabled by running the phpinfo() function in a PHP script or by checking the output of php -m command in the command line.

8. You can now use sqlsrv functions like sqlsrv_connect() and sqlsrv_query() in your PHP code to connect and query a Microsoft SQL Server database.

Retrieving Data From a SQL Server Table Using Microsoft SQL Server Driver for PHP

The Microsoft SQL Server Driver for PHP provides a set of functions for connecting and querying a Microsoft SQL Server database using PHP. One of the key features of this driver is the ability to retrieve data from a SQL Server table. The sqlsrv_query() function is used to execute a SELECT statement on the database and retrieve the results. This function takes two parameters, the connection to the database and the SQL query.

Once the query has been executed, the sqlsrv_fetch_array() function can be used to retrieve the rows of data returned by the query. This function takes two parameters, the statement resource returned by sqlsrv_query() and the type of array to return the data as. The SQLSRV_FETCH_ASSOC constant can be used to return the data as an associative array, with the column names of the table used as the keys.

It’s very important to close the connection after the data is retrieved from the SQL Server, this can be done by using sqlsrv_close() function. This function takes the connection variable as a parameter and closes the connection to the database.

It’s important to note that it’s good practice to always check for errors when working with the SQL Server Driver for PHP, and the sqlsrv_errors() function can be used to retrieve any errors that may have occurred during the query or connection process.

Creating an index.php File within a Folder in htdocs using XAMPP

To create an index.php file in a folder within the htdocs directory when using XAMPP, follow these steps:

1. Open the XAMPP control panel.
2. Start the Apache and MySQL modules.
3. Open your preferred web browser and go to http://localhost/
4. From the XAMPP home page, click on the “Explorer” button to open the file explorer.
5. Navigate to the htdocs folder.
6. Create a new folder with the desired name.
7. Inside the new folder, right-click and select “New” then “File”.
8. Name the file “index.php”
9. Open the newly created index.php file using any text editor, and then you can start adding your PHP code.
10. Once you’ve finished editing, save the file.
11. Finally, you can access your index.php file by going to http://localhost/foldername/index.php in your browser, where “foldername” is the name of the folder you created in step 6.

Index.php

<?php
	// Connect to the server and select the database
	$serverName = "DESKTOP-5AC0PRQ"; //serverName\instanceName
	// Since UID and PWD are not specified in the $connectionInfo array,
	// The connection will be attempted using Windows Authentication.
	$connectionInfo = array( "Database"=>"mydatabase");
	$conn = sqlsrv_connect( $serverName, $connectionInfo);
	// Check if the connection was successful
	if( $conn ) {
		echo "Connection established.<br />";
		}else{
		echo "Connection could not be established.<br />";
		die( print_r( sqlsrv_errors(), true));
	}
	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	// SQL query to select data from mytable
	$sql = "SELECT id, name, email, phone FROM mytable";
	$stmt = sqlsrv_query( $conn, $sql );
	// Check if query is successful
	if( $stmt === false) {
		die( print_r( sqlsrv_errors(), true) );
	}
	// Create table to display the data
?>
<table>
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Phone</th>
    </tr>
    <?php
    // Loop through the data and add it to the table
    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) )
    { ?>
    <tr>
        <td><?php echo $row['id']; ?></td>
        <td><?php echo $row['name']; ?></td>
        <td><?php echo $row['email']; ?></td>
        <td><?php echo $row['phone']; ?></td>
    </tr>
    <?php } ?>
</table>

The code is using a combination of PHP and HTML to display the data from a SQL Server database on a table.

First, the code connects to the SQL Server database using the server name and connection information provided. If the connection is successful, it will print “Connection established.” If it fails, it will print “Connection could not be established” and display the error message.

Next, the code creates a SQL query to select the data from the “mytable” table, and uses the sqlsrv_query() function to execute the query and store the results in the $stmt variable.

The code then creates an HTML table with table headers for “ID”, “Name”, “Email”, and “Phone”. Using a while loop, the code then iterates through each row of data returned from the query and displays the values in a new table row.

The PHP code within the while loop uses the sqlsrv_fetch_array() function to fetch each row of data, and retrieves the values of the “id”, “name”, “email”, and “phone” columns. These values are then displayed in the corresponding table cells within the HTML table.

Once the while loop ends, the table is closed with the ending tag.

Ensuring Compatibility between PHP and Microsoft SQL Server Driver for PHP

It is important to ensure that you are using the correct version of the Microsoft SQL Server Driver for PHP that corresponds to the version of PHP that you are running on your server. Failing to do so may result in errors or unexpected behavior. Be sure to double-check the version of PHP and the driver before proceeding with the installation and configuration.

Enhancing the Output with Bootstrap

A. Adding Bootstrap CSS to the HTML page: In order to enhance the look and feel of the HTML table output, we can use Bootstrap, a popular CSS framework that provides a collection of CSS and JavaScript components for creating responsive, mobile-first web pages. To add Bootstrap CSS to the HTML page, we can either download the Bootstrap CSS file from the official website and link to it in the head of the HTML page, or use a Content Delivery Network (CDN) to link to the Bootstrap CSS file.

B. Converting the HTML table to a Bootstrap table: Once we have added the Bootstrap CSS to the HTML page, we can use Bootstrap classes to convert the HTML table to a Bootstrap table. This can be done by adding the class “table” to the table element, and “table-striped” to add zebra-striping to the table. Additionally, we can add the class “table-hover” to enable hover effect on the table rows.

C. Styling the table with Bootstrap classes: In addition to converting the HTML table to a Bootstrap table, we can also use Bootstrap classes to style the table. This can be done by adding classes such as “table-primary” to give the table a blue background color, “table-success” for green background color, “table-danger” for red background color and so on. We can also use classes such as “text-muted”, “text-primary”, “text-success” etc to style the text within the table cells.

To create and add Bootstrap CSS to the header of your HTML file, you will need to do the following steps:

1. Download the Bootstrap CSS library from the official website (https://getbootstrap.com/) or include it from a CDN (Content Delivery Network) by adding the following link in the head section of your HTML file:

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">

2. Create a container element within the body of your HTML file, where you want to display the table. For example:

<div class="container">
  ...
</div>

HTML 5 & Bootstrap Source Code

<?php
	// Connect to the server and select the database
	$serverName = "DESKTOP-5AC0PRQ"; //serverName\instanceName
	// Since UID and PWD are not specified in the $connectionInfo array,
	// The connection will be attempted using Windows Authentication.
	$connectionInfo = array( "Database"=>"mydatabase");
	$conn = sqlsrv_connect( $serverName, $connectionInfo);
	// Check if the connection was successful
	if( $conn ) {
		echo '<div class="p-3 mb-2 bg-success text-white"><p>Connection established.</p></div><br />';
		}else{
		echo "Connection could not be established.<br />";
		die( print_r( sqlsrv_errors(), true));
	}
	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	// SQL query to select data from mytable
	$sql = "SELECT id, name, email, phone FROM mytable";
	$stmt = sqlsrv_query( $conn, $sql );
	// Check if query is successful
	if( $stmt === false) {
		die( print_r( sqlsrv_errors(), true) );
	}
	// Create table to display the data
?>

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title></title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"
        integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
</head>

<body>
    <div class="container">
        <table class="table">
            <tr class="table-success">
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Phone</th>
            </tr>
            <?php
    // Loop through the data and add it to the table
    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) )
    { ?>
            <tr>
                <td><?php echo $row['id']; ?></td>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['phone']; ?></td>
            </tr>
            <?php } 
    sqlsrv_close($conn); // close the connection
    ?>
        </table>
    </div>
</body>

</html>

Conclusion

In conclusion, the process of retrieving data from a SQL Server table and displaying it on an HTML page using PHP requires a few key components. One of the most important is the Microsoft SQL Server Driver for PHP, which allows PHP to communicate with the SQL Server. It is important to ensure that the correct version of the driver is used, corresponding to the version of PHP running on the server.

Additionally, the use of a local server environment, such as XAMPP, can be helpful in testing and developing the PHP code. The use of tools such as SQL Server Management Studio (SSMS) and the phpinfo() function can also aid in the process.

Furthermore, the use of Bootstrap can enhance the output by adding a responsive, mobile-first design to the HTML table. By adding the Bootstrap CSS and converting the HTML table to a Bootstrap table, we can also style the table with Bootstrap classes.

In this article, we have discussed the steps necessary to connect to a SQL Server database using PHP, retrieve data from a table, and display it on an HTML page. By following the steps outlined above, you should now be able to successfully retrieve data from a SQL Server table and display it on an HTML page using PHP and the Microsoft SQL Server Driver for PHP.

FAQ (Frequently Asked Questions)