MSSQL PDO

Step 1.1 Install SQL Server

Note: To ensure optimal performance of SQL Server, your machine should have at least 4 GB of memory.

1. Register the Microsoft Linux repository.
   Terminal

   curl https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo | sudo tee /etc/yum.repos.d/mssql-server-2017.repo
   
    Copy
   

2. Install SQL Server.
   Terminal

   sudo yum update
   sudo yum install mssql-server
   
    Copy
   

3. Setup your SQL Server.
   Terminal

   sudo /opt/mssql/bin/mssql-conf setup
   
    Copy
   

   Results

   Microsoft(R) SQL Server(R) Setup
   
   To abort setup at anytime, press Ctrl-C.
   
   The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746388 and
   found in /usr/share/doc/mssql-server/LICENSE.TXT.
   
   Do you accept the license terms? If so, please type YES:
   Please enter a password for the system administrator (SA) account:
   Please confirm the password for the system administrator (SA) account:

You now have SQL Server running locally on your RHEL machine! Check out the next section to continue installing prerequisites.

Step 1.2 Install PHP and other required packages

To install PHP 7.1 or 7.2, replace remi-php73 with remi-php71 or remi-php72 respectively in the following commands.

Terminal

sudo su
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm epel-release-latest-7.noarch.rpm
subscription-manager repos --enable=rhel-7-server-optional-rpms
yum install yum-utils
yum-config-manager --enable remi-php73
yum update
yum install php php-pdo php-xml php-pear php-devel re2c gcc-c++ gcc

 Copy

Compiling the PHP drivers with PECL with PHP 7.2 requires a more recent GCC than the default:

Terminal

sudo yum-config-manager --enable rhel-server-rhscl-7-rpms
sudo yum install devtoolset-7
scl enable devtoolset-7 bash

 Copy

You have successfully installed PHP on your RHEL machine!

SELinux is installed by default and runs in Enforcing mode. To allow Apache to connect to a database through SELinux, run the following command:

Terminal

sudo setsebool -P httpd_can_network_connect_db 1

 Copy

Step 1.3 Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

Terminal

sudo su
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-tools.repo
sudo ACCEPT_EULA=Y yum install msodbcsql17 mssql-tools
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql17 mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo yum install unixODBC-devel

 Copy

After installing SQLCMD, you can connect to SQL Server using the following command:

Terminal

sqlcmd -S localhost -U sa -P yourpassword
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

 Copy

This how to run a basic inline query. The results will be printed to the STDOUT.

Terminal

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

 Copy

Results

--------------------------------------------------------
Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)
 Apr 13 2017 11:44:40
 Copyright (c) Microsoft Corporation
    on Linux (Red Hat Enterprise Linux)

1 rows(s) returned

Executed in 1 ns

You have successfully installed SQL Server Command Line Utilities on your Red Hat machine!

Go to step 2

Step 2.1 Install the PHP Driver for SQL Server

Terminal

sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv
sudo su
echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini
echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini
exit

 Copy

An issue in PECL may prevent correct installation of the latest version of the drivers even if you have upgraded GCC. To install, download the packages and compile manually (similar steps for pdo_sqlsrv):

Terminal

pecl download sqlsrv
tar xvzf sqlsrv-5.6.0.tgz
cd sqlsrv-5.6.0/
phpize
./configure --with-php-config=/usr/bin/php-config
make
sudo make install

 Copy

Step 2.2 Create a database for your application

Create the database using sqlcmd.

Terminal

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

 Copy

Step 2.3 Create a PHP app that connects to SQL Server and executes queries

Terminal

mkdir SqlServerSample
cd SqlServerSample

 Copy

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

PHP

<?php
    $serverName = "localhost";
    $connectionOptions = array(
        "Database" => "SampleDB",
        "Uid" => "sa",
        "PWD" => "your_password"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn)
        echo "Connected!"
?>

 Copy

Run your PHP script from the terminal.

Terminal

php connect.php

 Copy

Results

Connected!

Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

 Copy

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

PHP

<?php
$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);

//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);


//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
?>

 Copy

Run your PHP script from the terminal.

Terminal

php crud.php

 Copy

Results

Inserting a new row into table
1 row(s) inserted:
Updating Location for user Nikita
1 row(s) updated:
Deleting user Jared
1 row(s) deleted:
Reading data from table
2 Nikita Sweden
3 Tom Germany
4 Jake United States

Congratulations! You have created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Go to step 3

Step 3.1 Create a new table with 5 million rows using sqlcmd

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

 Copy

Step 3.2 Create a PHP app that queries this tables and measures the time taken

Terminal

cd ~/
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

 Copy

Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.

PHP

<?php
$time_start = microtime(true);

$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Read Query
$tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Sum: ");
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['sum'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
$time_end = microtime(true);
$execution_time = round((($time_end - $time_start)*1000),2);
echo 'QueryTime: '.$execution_time.' ms';

?>

 Copy

Step 3.3 Measure how long it takes to run the query

Run your PHP script from the terminal.

Terminal

php columnstore.php

 Copy

Results

Sum: 50000000
QueryTime: 363ms

Step 3.4 Add a columnstore index to your table

Terminal

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

 Copy

Step 3.5 Measure how long it takes to run the query with a columnstore index

Terminal

php columnstore.php

 Copy

Results

Sum: 50000000
QueryTime: 5ms

Congratulations! You just made your PHP app faster using Columnstore Indexes!

Check out the PHP Driver on GitHub
# yum install php php-mcrypt php-cli php-gd php-curl php-mysql php-ldap php-zip php-fileinfo


# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
# yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

# yum install yum-utils
# yum-config-manager --enable remi-php72   [Install PHP 7.2]


yum install php php-cli php-pdo php-xml php-pear php-devel re2c gcc-c++ gcc

sudo yum install php-sqlsrv php-pdo_sqlsrv




# yum install php php-mcrypt php-cli php-gd php-curl php-mysql php-ldap php-zip php-fileinfo


https://www.microsoft.com/en-us/sql-server/developer-get-started/php/rhel



<?php

    $serverName = "148.103.164.69";
    $connectionOptions = array(
        "Database" => "master",
        "Uid" => "sa",
        "PWD" => "123"
    );
    //Establishes the connection
  $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn) {
        $sql = "SELECT * FROM TestSchema.Employees;";
       //$sql="SELECT @@VERSION  as ver";
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      //echo $row['optname']."<br />";
       echo $row['Id']."\n";
      echo $row['Name']."\n";
            echo $row['Location']."\n";

}

sqlsrv_free_stmt( $stmt);
}

if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

?>

sqlcmd -S 148.103.164.69 -U sa -P 123 -d master -Q "CREATE SCHEMA TestSchema;"

sqlcmd -S 148.103.164.69 -U sa -P 123  -d master -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"

sqlcmd -S 148.103.164.69 -U sa -P 123  -d master -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared',N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"

sqlcmd -S 148.103.164.69 -U sa -P 123  -d master -Q "SELECT * FROM TestSchema.Employees;"