PHP MS SQL Server

Introduction

In this tutorial, we are going to look at how we can connect to SQL Server using PHP and create, read, update and delete data. You can connect to the database using PDO but in this example, we will use Eloquent ORM framework. Our complete work will look as follows

PHP SQL Server

PHP SQL Server

PHP SQL Server

Introduction to SQL Server

SQL Server is a powerful relational database management system developed by Microsoft. Previously SQL Server was available only on the windows platform but you can now run it on Linux and MAC OS. SQL Server is a commercial product but also comes with a community edition which you can download and use for free. Some of the cool features supported by SQL Server include;

  • Stored Procedures
  • User Defined Functions
  • Triggers
  • And many more

Downloading SQL Server (Free Edition)

SQL Server comes in many flavours but for the purpose of this tutorial, we will use the express edition which is free. You can visit https://www.microsoft.com/en-us/sql-server/sql-server-editions-express for more information and how to download it.

Installing SQL Server on Windows

We will not cover how to install SQL Server in this tutorial. Installing SQL isn’t very different from installing any other windows program. Microsoft has a tutorial How to install SQL Server that will guide you during the installation process if you need help. if you get stuck you can always use the comments section to ask.

After you have successfully installed SQL Server, run the following command to create the database, items table and add tow dummy records.

CREATE DATABASE PHP7CrudExample
GO

CREATE TABLE [items](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [item] [nvarchar](50) NULL,
    [description] [nvarchar](255) NULL,
    [status] [nvarchar](50) NULL,
    [created_at] [datetime] NULL,
    [updated_at] [datetime] NULL,
    [deleted_at] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[items] ADD  CONSTRAINT [DF_items_status]  DEFAULT ((0)) FOR [status]
GO

INSERT INTO items (item,description,status)
VALUES ('Exercising','Go to the Gym','Not Started')
GO

Microsoft SQL Server ODBC Drivers

SQL Server drivers for PHP use ODBC. You will need to download the ODBC drivers if you do not already have them. You can download Microsoft ODBC Driver 13 for SQL Server – Windows + Linux from this link ODBC Drivers Download

PHP + Web Server

This tutorial assumes you already have PHP up and running. We will be working with the latest version of PHP which is 7. * as of this writing. I am using Windows 10 and I have XAMPP.

Microsoft Drivers for PHP for SQL Server

By default, PHP does not come with Microsoft SQL Server drivers. We will not to download the drivers and install them.

The official GitHub repository for SQL Server PHP Drivers is https://github.com/Microsoft/msphpsql it contains great information on a number of topics that are covered in this post. I recommend you read it especially on sections that are specific to your platform.

For developers using windows, you can visit this link to download the binary files https://github.com/Azure/msphpsql/releases

If you are using PHP 7.0.* then you should download Windows-7.0.zip if you are using PHP 7.1.* then you should download Windows-7.1.zip

The downloaded file will contain libraries for both 32 bit and 64 bit systems. If you are using PHP 64 bit, then you will need the 64 bit libraries. If you are using the 32-bit version of PHP, then you will need the 32-bit version.

The image below shows the contents of the extracted downloaded files

PHP SQL Server Drivers

HERE,

We will use the thread safe versions of the drivers.

Copy the following two files

  • php_pdo_sqlsrv_7_ts.dll
  • php_sqlsrv_7_ts.dll

Paste them in following directory

C:\xampp\php\ext

This assumes you are using XAMPP and have installed it to drive C:\. If your path is different from the above, then copy the files to the respective directory. We will now modify php.ini to enable the new extensions that we just added

extension=php_pdo_sqlsrv_7_ts.dll
extension=php_sqlsrv_7_ts.dll

Save the changes and restart the web server

Load the following URL in your web browser to see if the drivers have been loaded properly

http://localhost:8000/dashboard/phpinfo.php 

You should be able to see the following results.

sqlsvr

PHP Database Programming with SQL Server

In this section, we will create a basic application that will use Eloquent ORM to communicate with SQL Server.

We are not going to use any framework for simplicity’s sake. But we also want a structured application so we will simulate the MVC architecture. The following image shows how our application will be structured

Simple MVC framework

HERE,

  • app – this is the directory where all of our code will be
    • Controllers – contains the application controller. Don’t worry about the fancy name. it is nothing more than a PHP class with methods that respond to create, read, update and delete functions
    • Models – this is where the class that extends Eloquent ORM Model will be
    • View – this is where we will have the master layout and child templates
  • bootstrap – this will contain a single file that we will be using to bootstrap the application
  • config – this will contain database configuration file
  • vendor – contains all packages installed via composer.
  • composer.json – contains required packages and auto loading the application.
  • index.php – provides the gateway into the application

Building the application

This section assumes you have installed composer. You can download composer for free from the official website.

Step 1 – Installing Eloquent ORM

Create a new directory php7sqlsrv in the web root of your web server i.e. C:\xampp\htdocs

Run the following command

composer require illuminate/database

HERE,

  • The above command installs Eloquent ORM and create the composer.json and composer.lock files. It will also create the vendor directory.

Step 2 – auto loading our application classes using psr-4

Open composer.json and modify it as follows

{
    "require": {
        "illuminate/database": "^5.4",
    },
    "autoload": {
        "psr-4": {
            "KodeBlog\\": "app/"
        }
    }
}

HERE,

"autoload": {…} uses psr-4 to autoload all class in the namespace KodeBlog from the directory app

Run the following command to generate auto loading files

composer dump-autoload

perfect, let’s now create the directories that we listed above.

Step 3 – creating the rest of the application directories

Open the terminal in the root directory of your project

Run the following commands to create the required directories

mkdir bootstrap
mkdir config
mkdir app
cd app
mkdir Controllers
mkdir Models
mkdir Views
cd Views
mkdir items
mkdir templates

Step 4 – bootstrapping the application and database configuration

Create a new file in /bootstrap directory autoload.php

Add the following code to autoload.php

<?php

require_once './vendor/autoload.php';

require_once './config/database.php';

HERE,

  • require_once './vendor/autoload.php'; loads the autoload file that is generated by composer
  • require_once './config/database.php'; loads our database configuration file

create a new file in /config directory database.php

add the following code

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

$capsule = new Capsule;

$capsule->addConnection(array(
    'driver' => 'sqlsrv',
    'host' => 'localhost',
    'database' => 'PHP7CrudExample',
    'username' => 'kr',
    'password' => 'aise',
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'pooling' => false,
));

$capsule->setAsGlobal();

$capsule->bootEloquent();

HERE,

  • We instantiate the Manager class and define database connection settings. sqlsrv tells Eloquent to use SQL Server. The other settings are self-explanatory. Make sure you include use the correct credentials that match the one on your development machine.

Step 5 – Application controller

In this section, we will create the ItemsController that will be responding to the various user actions.

Create a new file ItemsController.php in /app/Controllers directory

Add the following code

<?php
namespace KodeBlog\Controllers;
use KodeBlog\Models\Item;

class ItemsController {
    public static function index() {
        global $base_url, $action;
        $title = 'Items Listing';
        $view = 'items/list';
        $data = Item::all();

        require './app/Views/Templates/layout.html';
    }
    public static function create() {
        global $base_url, $action;
        $title = 'Create New Item';
        $view = 'items/create';

        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            Item::create($_POST);
            header("Location: $base_url?action=list");
        } else {
            require './app/Views/Templates/layout.html';
        }
    }
    public static function edit($id) {
        global $base_url, $action;
        $title = 'Edit Item';
        $view = 'items/edit';
        $item = Item::find($id);

        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            $item->item = $_POST['item'];
            $item->description = $_POST['description'];
            $item->status = $_POST['status'];
            $item->save();
            header("Location: $base_url?action=list");
        } else {
            require './app/Views/Templates/layout.html';
        }
    }
    public static function delete($id) {
        global $base_url, $action;
        $title = 'Delete Item';
        $view = 'items/delete';
        $item = Item::find($id);

        if ($_SERVER['REQUEST_METHOD'] == 'POST') {
            $item->delete();

            header("Location: $base_url?action=list");
        } else {
            require './app/Views/Templates/layout.html';
        }
    }
}

HERE,

  • namespace KodeBlog\Controllers; defines the controllers’ namespace
  • use KodeBlog\Models\Item; imports the Item model from the Models namespace
  • public static function index(){…} is the method that is called when the user requests the list action. What the method does is;
    • global $base_url, $action; makes the $base_url and $action variables available to the method.
    • $title = 'Items Listing'; set the application title to Items Listing
    • $view = 'items/list'; define the name of the view that should be loaded
    • $data = Item::all(); retrieve all items from the database
    • require './app/Views/Templates/layout.html'; load the layout.html which is the master layout. The master layout is responsible for loading the sub template as specified by the $view variable.
  • public static function create() {…} responsible for displaying the create item form and creating the record in the database
  • public static function edit($id) {…} responsible for displaying the editing form and updating the existing record in the database.
  • public static function delete($id) {…} responsible for displaying the confirm delete form and actually deleting the form.

Step 6 – Item Model

The model will do all of our database dirty works.

Create a new file Item.php in the directory /appModels

Add the following code

<?php

namespace KodeBlog\Models;

use Illuminate\Database\Eloquent\Model as Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Item extends Model {
    use SoftDeletes;

    protected $dates = ['deleted_at'];

    protected $fillable = [
        'item',
        'description',
        'status',
    ];
}

HERE,

  • namespace KodeBlog\Models; defines the namespace for the models
  • use Illuminate\Database\Eloquent\Model as Model; imports the eloquent model
  • use Illuminate\Database\Eloquent\SoftDeletes; imports the Soft Deletes trait. Soft delete means the record will be marked as deleted but not permanently removed from the database.
  • class Item extends Model {…} defines a class Item that extends the model class.
  • use SoftDeletes; applies the soft delete trait to our model.
  • protected $dates = ['deleted_at']; the timestamp field that will be used for soft deletes
  • protected $fillable = […] defines the field names that can be mass assigned. This is for security reasons which we will not cover in this tutorial.

Step 7 – Application Views

In a nutshell, we have a master layout that displays the title and the navigation menu which is common to all windows. This is created in the directory /app/Views/templates/layout.html

The sub templates representing each action are stored in the directory /app/Views/items namely

  • create.html
  • delete.html
  • edit.html
  • list.html

We will use Burma CSS and Font awesome icons for beautification of the application.

The code for the views is as follows

templates/layout.html

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title><?=$title?></title>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bulma/0.4.0/css/bulma.min.css">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
        <style type="text/css">
        body{padding: 40px}
        </style>
    </head>
    <body>
        <div class="container">
            <h2 class="title">PHP SQL Server CRUD Example</h2>
            <div class="columns">
                <div class="column is-one-quarter">
                    <h2 class="title is-4">Menu</h2>
                    <aside class="menu">
                        <p class="menu-label">
                            Administration
                        </p>
                        <ul class="menu-list">
                            <li>
                                <a href="<?=$base_url?>?action=list" <?=$action == 'list' ? 'class="is-active"' : ''?>>
                                    <span class="icon"><i class="fa fa-list"></i></span> Items Listing
                                </a>
                            </li>
                            <li>
                                <a href="<?=$base_url?>?action=create" <?=$action == 'create' ? 'class="is-active"' : ''?>>
                                    <span class="icon"><i class="fa fa-plus"></i></span> Create Item
                                </a>
                            </li>
                        </ul>
                    </aside>
                </div>
                <div class="column is-three-quarters">
                    <h2 class="title is-4"><?=$title?></h2>
                    <?php require "./app/Views/$view.html"; ?>
                </div>
            </div>
        </div>
    </body>
</html>

create.html

<form action="<?=$base_url?>?action=create" method="post">
    <div class="field">
        <label class="label">Item</label>
        <p class="control has-icon has-icon-left">
            <input id="item" name="item" class="input" type="text" placeholder="Enter Item Name" required="1">
            <span class="icon is-small">
                <i class="fa fa-tasks"></i>
            </span>
        </p>
    </div>
    <div class="field">
        <label class="label">Description</label>
        <p class="control has-icon has-icon-left">
            <input id="description" name="description" class="input" type="text" placeholder="Describe the Item" required="1">
            <span class="icon is-small">
                <i class="fa fa-pencil-square-o"></i>
            </span>
        </p>
    </div>
    <div class="field">
        <label class="label">Status</label>
        <p class="control has-icon has-icon-left">
            <input id="status" name="status" class="input" type="text" placeholder="Enter the status" required="1">
            <span class="icon is-small">
                <i class="fa fa-hourglass-half"></i>
            </span>
        </p>
    </div>
    <div class="field is-grouped">
        <p class="control">
            <input class="button is-primary" type="Submit" value="Create New Item">
        </p>
    </div>
</form>

delete.html

<h2>Are you sure you want to delete <strong><?=$item->item?></strong>?</h2>
<br>
<form action="<?=$base_url?>?action=delete&id=<?=$item->id?>" method="post">
    <div class="field is-grouped">
        <p class="control">
            <input class="button is-danger" type="Submit" value="Yes, Delete">
        </p>
    </div>
</form>

edit.html

<form action="<?=$base_url?>?action=edit&id=<?=$item->id?>" method="post">
    <div class="field">
        <label class="label">Item</label>
        <p class="control has-icon has-icon-left">
            <input id="item" name="item" class="input" type="text" value="<?=$item->item?>" placeholder="Enter Item Name" required="1">
            <span class="icon is-small">
                <i class="fa fa-tasks"></i>
            </span>
        </p>
    </div>
    <div class="field">
        <label class="label">Description</label>
        <p class="control has-icon has-icon-left">
            <input id="description" name="description" class="input" type="text" value="<?=$item->description?>" placeholder="Describe the Item" required="1">
            <span class="icon is-small">
                <i class="fa fa-pencil-square-o"></i>
            </span>
        </p>
    </div>
    <div class="field">
        <label class="label">Status</label>
        <p class="control has-icon has-icon-left">
            <input id="status" name="status" class="input" type="text" value="<?=$item->status?>" placeholder="Enter the status" required="1">
            <span class="icon is-small">
                <i class="fa fa-hourglass-half"></i>
            </span>
        </p>
    </div>
    <div class="field is-grouped">
        <p class="control">
            <input class="button is-primary" type="Submit" value="Save Changes">
        </p>
    </div>
</form>

list.html

<table class="table is-striped">
    <thead>
        <tr>
            <th>Item</th>
            <th>Description</th>
            <th>Status</th>
            <th>Action</th>
        </tr>
    </thead>
    <tfoot>
    <tr>
        <th>Item</th>
        <th>Description</th>
        <th>Status</th>
        <th>Action</th>
        </tfoot>
    </thead>
    <tbody>
        <?php if(count($data)): ?>
        <?php foreach($data as $row): ?>
        <tr>
            <td><?=$row->item?></td>
            <td><?=$row->description?></td>
            <td><?=$row->status?></td>
            <td>
                <a title="Edit Item" href="<?=$base_url?>?action=edit&id=<?=$row->id?>">
                    <span class="icon"><i class="fa fa-edit"></i></span>
                </a>
                <a title="Delete Item" href="<?=$base_url?>?action=delete&id=<?=$row->id?>">
                    <span class="icon"><i class="fa fa-trash"></i></span>
                </a>
            </td>
        </tr>
        <?php endforeach; ?>
        <?php endif; ?>
    </tbody>
</table>

Step 8 – index.php

The index.php is the gateway into our application.

Create a new file index.php in the root directory and add the following code

<?php

use KodeBlog\Controllers\ItemsController;

require './bootstrap/autoload.php';

$base_url = 'http://localhost:8000/php7sqlsrv/index.php';

if (!isset($_GET["action"])) {
    header("Location: $base_url?action=list");
}

$action = $_GET["action"];

switch ($action) {
case 'list':
    ItemsController::index();
    break;

case 'create':
    ItemsController::create();
    break;

case 'edit':
    $id = $_GET["id"];
    ItemsController::edit($id);
    break;

case 'delete':
    $id = $_GET["id"];
    ItemsController::delete($id);
    break;
}

HERE,

  • use KodeBlog\Controllers\ItemsController; imports the namespace
  • require './bootstrap/autoload.php'; loads the bootstrap autoload.php file
  • $base_url = 'http://localhost:8000/php7sqlsrv/index.php'; defines the base url that we will use to build links for our application. Note: I am running apache on port 8000
  • if (!isset($_GET["action"])) {…} checks if the request action has been set. It defaults to list action if nothing has been specified
  • $action = $_GET["action"]; grabs the value of action and assigns it to the variable $action.
  • switch ($action) {…} acts like a router for our application and calls the relevant methods in the ItemsController class

You can now take the application for a spin by loading the following URL into your web browser.

http://localhost:8000/php7sqlsrv/index.php?action=list

You will be able to see the cool screens that we showed you in the introduction of this tutorial.

Complete Tutorial Project Code

You can clone this the complete tutorial project code from Github repository by running the following git command

git clone https://github.com/KodeBlog/php-7-ms-sql-server.git

Support Us

Please support us by creating a free account on our site, sign up for the newsletter, follow us on twitter and share the tutorial on social media. Thank you very much for your support.