Enhancing PHP Projects With SQL Server

In the world of web development, PHP stands as one of the most popular scripting languages that empowers developers to create dynamic and interactive web applications with ease. Central to the functionality of many PHP projects is the management of data, which often necessitates the integration of powerful database solutions. Let us look at how to connect a PHP project to SQL Server, which holds a prominent position among other RDBMSs for its reliability, scalability, and comprehensive feature set.

According to the latest statistics from W3Techs in 2024, PHP continues to dominate website development, showing an impressive share of 76.5%. The majority of websites, in their turn, depend on database interaction in their backend to efficiently store, retrieve, and manage data. This interaction between PHP and a database management system is essential for dynamic content delivery, user authentication, and various other functionality crucial to modern web applications. According to the latest metrics from Statista, SQL Server ranks third among the most widely used relational database management systems (RDBMSs).

ODBC (Open Database Connectivity) drivers play an important role in facilitating the connection between SQL Server and PHP projects. These drivers serve as intermediaries, enabling PHP applications to communicate with SQL Server databases by translating PHP requests into commands that the SQL Server database can understand. Through ODBC drivers, PHP developers can execute SQL queries, retrieve data, and perform various database operations.

Additionally, tools like dbForge Studio for SQL Server provide a comprehensive environment for managing SQL Server databases within PHP projects. dbForge Studio offers features such as visual query building, database design and modeling, data synchronization, and debugging capabilities, empowering developers to streamline database development tasks. With its intuitive interface and robust functionality, dbForge Studio enhances productivity and simplifies the process of integrating SQL Server databases into PHP projects.

In this article, we’ll explore the steps to integrate a SQL Server database with a PHP project. Additionally, we’ll demonstrate how to use the capabilities of SQL Server effectively using a powerful GUI tool – dbForge Studio.

Contents

• Setting up the environment
  • Using Docker for SQL Server and PHP integration
  • Selecting the right tools: dbForge Studio and ODBC Drivers
  • Establish connection to the SQL Server instance running in Docker
• Integrating ODBC drivers in PHP projects
  • Benefits of using ODBC Drivers in PHP projects
  • Step-by-step guide to connecting PHP to SQL Server using ODBC Driver
• Database development with dbForge Studio for SQL Server
• Conclusion

Setting up the environment

First things first, let’s prepare our environment. In this article, we’ll demonstrate how to use Docker to connect a PHP project with SQL Server. We’ll walk you through setting up Docker and running SQL Server within it. Ready? Then let’s get started!

Using Docker for SQL Server and PHP integration

Using Docker containers to integrate SQL Server with PHP offers a streamlined and portable solution for development environments. By containerizing both SQL Server and PHP applications, developers can easily replicate the setup across different machines, ensuring consistency and eliminating compatibility issues. Docker enables the isolation of environments, allowing developers to run SQL Server and PHP instances side by side without interference.

SQL Server can be installed natively on both Windows and Linux systems, and it can also be run from Docker images. This provides users with flexibility in how they choose to deploy and manage SQL Server instances. Running SQL Server from a Docker image offers numerous benefits. Firstly, it ensures portability, allowing easy deployment across various environments, thereby saving time and ensuring consistency. Secondly, Docker containers provide isolation, ensuring reliable performance by preventing interference from other applications. And finally, Docker containers consume fewer resources compared to traditional virtual machines, optimizing resource utilization and reducing infrastructure costs.

How to install Docker Engine on Ubuntu using the apt repository

Before installing Docker Engine on a new host machine for the first time, it’s necessary to set up a Docker repository. This involves configuring the system to access the Docker repository where Docker packages are stored. Once the repository is set up, you can proceed to install and update Docker directly from the repository.

Step 1: Set up Docker’s apt repository

# Add Docker's official GPG key:
sudo apt-get update
sudo apt-get install ca-certificates curl
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc
sudo chmod a+r /etc/apt/keyrings/docker.asc

# Add the repository to Apt sources:
echo \
  "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.asc] https://download.docker.com/linux/ubuntu \
  $(. /etc/os-release && echo "$VERSION_CODENAME") stable" | \
  sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update

Note
If you are using an Ubuntu derivative distribution, such as Linux Mint, you may need to use UBUNTU_CODENAME instead of VERSION_CODENAME when configuring repositories or installing packages.

Step 2: Install the Docker packages

To install the latest version of Docker Engine, run the following command:

sudo apt-get install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Afterwards, verify the successful installation of Docker Engine by running the hello-world image:

sudo docker run hello-world

This command downloads a test image and executes it within a container. Upon successful execution, it prints a confirmation message and exits.

You have now successfully installed and started Docker Engine on your system.

How to run SQL Server from Docker

Now that our Docker is ready, we can proceed to the next stage of preparing our environment: running SQL Server from Docker.

Step 1: Pull the SQL Server Docker image

Use the following command to pull the official SQL Server Docker image from the Docker Hub repository:

docker pull mcr.microsoft.com/mssql/server:2022-latest

Step 2: Run the SQL Server container

Once the image is pulled, you can create and start a new SQL Server container using the following command:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=PASSWORD" -p 1433:1433 --name SQLSEVER_2022 --hostname SQLSEVER_2022 --mount type=bind,source=/home/test/database/sqlserver_2022/data,target=/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2022-latest

Step 3: Verify that SQL Server is running

To check if SQL Server is running in a container, you can use the following command:

docker ps

This command lists all running containers on your system. Look for the container running SQL Server. If SQL Server is running successfully, you should see a container with the name you specified (or a randomly generated name if you didn’t specify one), and the status should be “Up” in the output of the docker ps command.

Additionally, you can use the docker container ls command as an alternative to docker ps, which provides similar information about running containers.

Selecting the right tools: dbForge Studio and ODBC Drivers

Now that our setup is complete, the next step is to choose the appropriate tool to establish a connection between our SQL Server instance running in Docker and our PHP project. For this, we need a robust and efficient ODBC driver. Devart ODBC connectors seem to be an optimal solution as they enable direct access to databases using native protocols without the need for additional client software. These connectors also support accessing cloud data sources via HTTPS, which adds to their versatility.

Devart ODBC Drivers are extensively tested and proven to be compatible with leading analytics and reporting tools, as well as various IDEs and programming languages. With the Devart ODBC Driver for SQL Server, you can connect to SQL Server databases from various applications and development environments. This integration allows you to establish a direct connection to SQL Server via TCP/IP to eliminate the need for SQL Server Client.

Once everything is connected, having a robust tool to manage your SQL Server database becomes essential. That’s where dbForge Studio for SQL Server steps in. It offers a comprehensive environment for database development and management, featuring, among other things, visual query building, schema comparison, and data synchronization capabilities.

Establish connection to the SQL Server instance running in Docker

You can easily connect to the SQL Server instance hosted on your Docker from dbForge Studio for SQL Server. To do this, navigate Database > New Connection. Then, in the Database Connection Properties dialog that will open, enter the necessary connection details, such as the server name (which would typically be the IP address or hostname of your Docker container running SQL Server), select the authentication type, and enter authentication credentials (such as username and password).

Once all the connection details are entered, click Test Connection to ensure that dbForge Studio can successfully connect to the SQL Server instance running in Docker.

Integrating ODBC Drivers in PHP Projects

ODBC (Open Database Connectivity) drivers for SQL Server are software components that facilitate connectivity between SQL Server databases and applications or tools that support the ODBC standard. ODBC drivers translate application requests into commands that SQL Server can understand and vice versa. These drivers enable applications to communicate with SQL Server databases by providing a standardized interface for accessing and manipulating data.

Devart ODBC Driver for SQL Server stands as an industry-standard solution known for its reliability and robust features. Distinguishing itself from other ODBC drivers, it fully supports standard ODBC API functions and data types. This versatility enables seamless compatibility with any ODBC-compatible tool, ensuring secure connections to SQL Server. Furthermore, with just one ODBC Driver for SQL Server, users can effortlessly connect to multiple databases, streamlining database management tasks.

Benefits of using ODBC Drivers in PHP projects

1. Platform independence: Devart ODBC Drivers provide a standardized interface for accessing databases, making it easier to switch between different database systems without changing the application code. This enhances portability and reduces dependency on specific database platforms.
2. Wide compatibility: These Drivers support a wide range of database management systems, including SQL Server, MySQL, PostgreSQL, Oracle, and others. This allows PHP applications to interact with various databases using a single set of functions and commands.
3. Performance optimization: All ODBC Drivers are optimized for performance, providing efficient data access and retrieval capabilities. This ensures that PHP applications can handle large volumes of data quickly and effectively.
4. Security: Users can make the most out of multiple security features, such as encrypted connections and authentication mechanisms, ensuring that sensitive data remains secure during transmission between the PHP application and the database server.
5. Ease of use: Each Driver offers a straightforward and consistent API for database access, making it easier for developers to write and maintain PHP code that interacts with databases. This reduces development time and effort.
6. Support for advanced features: Devart ODBC Drivers support advanced database features such as stored procedures, transactions, and data manipulation language (DML) statements. This allows PHP applications to take advantage of the full capabilities of the underlying database system.

Step-by-step guide to connecting PHP to SQL Server using ODBC Driver

Step 1: Install PHP on Ubuntu

1.1 Update the package index: First, update the package index on your Ubuntu system to ensure you have the latest available versions of software packages:

sudo apt update

1.2 Install PHP: Run the following command to install PHP:

sudo apt install php

1.3 Verify the installation: After the installation, you can verify that PHP is installed correctly by checking its version:

php -v

1.4 Test PHP: Create a test PHP file to ensure that PHP is working correctly. For example, create a file named info.php in your web server’s document root directory with the following content:

<?php
phpinfo();
?>

Save the file and access it through a web browser (e.g., http://localhost/info.php). You should see a page displaying PHP configuration details.

Step 2: Install Devart ODBC Driver for SQL Server

The installation process is simple: all you need to do is download the latest version from the developer’s website. After downloading, run the setup program to install the software. The installer will automatically detect the SQL Server version number. Devart ODBC Driver for Windows/Linux/macOS will then begin the installation process. You can also install this driver from the command line. For more details, please refer to Installing on Linux (DEB) – ODBC Driver for SQL Server.

To install Devart ODBC Driver for SQL Server, we will run the following commands:

sudo apt-get install odbcinst1debian2 libodbc1 odbcinst unixodbc
sudo apt install php-pear
sudo pecl install pthreads
sudo apt-get install php8.1-odbc
sudo dpkg -i devartodbcsqlserver_amd64.deb

The first command installs the ODBC driver manager (unixodbc) and related libraries (odbcinst1debian2 and libodbc1), which are necessary for managing ODBC drivers on Debian-based Linux systems. Then, PHP PEAR, a framework for reusable PHP components, is installed. The third command installs the Pthreads extension for PHP, enabling multi-threading support. Then, the ODBC extension for PHP version 8.1, which allows PHP scripts to connect to ODBC data sources, gets installed. And finally, the Devart ODBC Driver for SQL Server from a Debian package, enabling connectivity to SQL Server databases, is installed.

Step 3: Connect to a SQL Server instance hosted on Docker from PHP using ODBC Driver for SQL Server

Use the following script to establish a connection with a SQL Server database and execute a SQL query.

<?php

$user = "myusername";

$password = "mypassword";

$ODBCConnection = odbc_connect("DRIVER={Devart ODBC Driver for SQL Server};Server=myserver;Database=AdventureWorks2022; Port=myport;String Types=Unicode", $user, $password);

$SQLQuery = "SELECT top(5) * FROM Sales.Customer";

$RecordSet = odbc_exec($ODBCConnection, $SQLQuery);
$Result = odbc_result_all($RecordSet, "border=1");

odbc_close($ODBCConnection);

?>

In this query, the odbc_connect() function is used to connect to an ODBC data source. It takes three mandatory parameters: the data source name, username, and password. If your database is not password-protected or doesn’t require a username, leave these parameters empty. If the connection is successful, the odbc_exec() function is used to execute a SELECT statement. Then, the odbc_result_set() function prints results as an HTML table. After all rows from the result set have been printed, the odbc_close() function closes the connection.

Let us save the script and run it from the command line using the command:

php Documents/connect.php -r

And if we save the result in an HTML file and open it in a browser, we will get it as follows.

Congratulations! We have successfully integrated SQL Server into our PHP project.

Let us also try to run the query in dbForge Studio for SQL Server. The result is the same.

Database development with dbForge Studio for SQL Server

How dbForge Studio enhances PHP development with SQL Server

In typical PHP development scenarios, the focus lies predominantly on working with data rather than with the structure of the database. For developers, it often proves more convenient to use specialized software dedicated to database management. Here, dbForge Studio hits the stage. With its impressive suite of features, dbForge Studio offers a convenient environment for handling database structures and data efficiently. By providing intuitive tools for tasks such as schema design, query building, and data synchronization, dbForge Studio significantly facilitates the database management process, enabling developers to focus more on data manipulation and application logic within their PHP projects.

Key features of dbForge Studio for developers and DBAs

• Advanced SQL editing and execution: The SQL editor in dbForge Studio provides advanced features such as syntax highlighting, code completion, and code formatting, enhancing the productivity of developers and DBAs when writing and executing SQL queries.
• Database management tools: The Studio offers a comprehensive suite of utilities for managing databases, including creating, editing, and deleting database objects such as tables, views, stored procedures, and functions.
• Visual Query Builder: The integrated query builder simplifies the process of creating SQL queries by providing a graphical interface, allowing users to drag and drop tables, define relationships, and visually construct complex queries without writing code manually.
• Schema comparison and synchronization: dbForge Studio helps users compare and synchronize database schemas across different environments, identifying and resolving any differences efficiently. This feature is invaluable for maintaining consistency between development, staging, and production databases.
• Data comparison and synchronization: With the Studio, users can compare and synchronize database contents between databases, backups, and scripts folders. This functionality is particularly useful for synchronizing test data with production data or updating databases with new data.
• Data Generator: dbForge Studio includes a powerful data generation tool that allows users to create realistic test data quickly and easily. With customizable data generation rules and support for various data types, users can get large volumes of sample data for testing and development purposes.
• Database backup and restore: dbForge Studio facilitates database backup and restoration processes, allowing users to create and schedule backups, as well as restore databases to previous states in the event of data loss or corruption.
• Database performance monitoring: dbForge Studio allows for monitoring database performance metrics such as CPU usage, memory usage, and query execution times in real time, helping users quickly identify performance bottlenecks and optimize database performance effectively.
• Integration with version control systems: dbForge Studio integrates seamlessly with popular version control systems such as Git and Subversion, allowing users to manage database schema changes and track revisions collaboratively.

Conclusion

PHP with SQL Server opens up a world of possibilities for web developers. By using the right tools and resources such as ODBC drivers and specialized software like dbForge Studio for SQL Server, you can streamline your development process and build robust, high-performance web applications with ease. Try dbForge Studio for SQL Server today and take your PHP development to the next level.

If your daily tasks involve connecting PHP with a MySQL database, see the How to Connect PHP With MySQL blog article.