Mya Dopkova

Mya Dopkova

1566787542

Building PHP apps using SQL Server on Windows

Step 1: Set up your environment

In this section, you will get SQL Server on Windows. After that you will install the necessary dependencies to create PHP apps with SQL Server

1. Install SQL Server

  1. If you don’t have SQL Server Developer (or above) installed, click here to download the SQL Server exe.
  2. Run it to start the SQL installer.
  3. Click Basic in Select an installation type.
  4. Click Accept after you have read the license terms.
  5. (Optional) if you need to, you can choose a custom installation location for SQL Server.
  6. Click Install to proceed with the installation.

You now have SQL Server installed and running locally on your Windows computer! Check out the next section to continue installing prerequisites.

2. Install PHP and Chocolatey

You can download PHP using the Web Platform Installer. Once you download Web PI, open it up and download the entry which says ‘PHP 7.2.7 (x64) for IIS Express’.

Next, install Chocolatey. Chocolatey is a package manager like apt-get and yum for Windows. We will use Chocolatey later in the tutorial. Use an elevated Command-line session (run as administrator):

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

For Chocolatey to work, you now need to restart the terminal session by closing and opening the terminal.

You have succesfully installed PHP and Chocolatey on your machine!

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.

  1. Install the ODBC Driver.
  2. Install the SQL Server Command Line Utilities.

After installing SQLCMD, you can connect to SQL Server using the following command from a CMD session:

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

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

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



Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright © Microsoft Corporation
Developer Edition (64-bit)

1 rows(s) returned

Executed in 1 ns.

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

Step 2: Create PHP application with SQL server

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

1. Install the PHP Drivers for SQL Server

Download the Microsoft PHP Drivers for SQL Server from the download center.

Pick the appropriate dll - for example php_pdo_sqlsrv_72_nts.dll for the PDO Driver and php_sqlsrv_72_nts.dll for the SQLSRV driver.

Copy the dll’s to the C:\Program Files\iis express\PHP\v7.2\ext folder.

Register the dll’s in the php.ini file.

cd C:\Program^ Files\iis^ express\PHP\v7.2\ext
echo extension=php_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini
echo extension=php_pdo_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini

2. Create a database for your application

Create the database using sqlcmd.

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

3. Create a PHP app that connects to SQL Server and executes queries

mkdir SqlServerSample
cd SqlServerSample

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
$serverName = “localhost”;
$connectionOptions = array(
“Database” => “SampleDB”,
“Uid” => “sa”,
“PWD” => “your_password”
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);
if($conn)
echo “Connected!”
?>

Run your PHP script from the terminal.

php connect.php


Connected!

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

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;”

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
$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']."";
}

}
?>

Run your PHP script from the terminal.

php crud.php


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.

Step 3: 3 Make your PHP app up to 100x faster

In this section we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

1. Create a new table with 5 million rows using sqlcmd

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;”

2. Create a PHP app that queries this table and measures the time taken

cd ~/
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

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

<?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’;

?>

3. Measure how long it takes to run the query

Run your PHP script from the terminal.

php columnstore.php


Sum: 50000000
QueryTime: 363ms

4. Add a columnstore index to your table

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

5. Measure how long it takes to run the query with a columnstore index

php columnstore.php


Sum: 50000000
QueryTime: 5ms

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

Check out the PHP Driver on GitHub

Thanks For Visiting, Keep Visiting.

Originally published on sqlchoice

#php #sql #sql-server #mysql #web-development

What is GEEK

Buddha Community

Building PHP apps using SQL Server on Windows
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel

1625843760

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

Brain  Crist

Brain Crist

1600347600

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master

Introduction

This is part 3 of “MS SQL Server- Zero to Hero” and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below,

A glimpse of previous articles
Part 1

In part one, we learned the basics of data, database, database management system, and types of DBMS and SQL.

Part 2
  • We learned to create a database and maintain it using SQL statements.
  • Best practice methods were also mentioned.

#sql server #benefits of schemas #create schema in sql #database schemas #how to create schema in sql server #schemas #schemas in sql server #sql server schemas #what is schema in sql server

Karlee  Will

Karlee Will

1620516240

Key Differences between SQL Server 2019 for Windows and Linux

Since the release of SQL Server 2017 for Linux, Microsoft has pretty much changed the entire game. It enabled a whole new world of possibilities for their famous relational database, offering what was only available in the Windows space until then.

I know that a purist DBA would tell me right away that the out of the box SQL Server 2019 Linux version has several differences, in terms of features, in regards to its Windows counterpart, such as:

  • No SQL Server Agent
  • No FileStream
  • No System Extended Stored Procedures (e.g. xp_cmdshell)

However, I got curious enough to think “what if they can be compared, at least to some extent, against things that both can do?” So, I pulled the trigger on a couple of VMs, prepared some simple tests, and collected data to present to you. Let’s see how things turn out!

#sql server #sql server 2019 #sql server linux #sql server windows #sql

Michael  Hamill

Michael Hamill

1618233360

Get Started with SQL Server Machine Learning Services

We know Humans learn from their past experiences. Mean while Machines follow Instructions given by Humans. But what if Human can train Machines to learn from the past data?. In simple, this is what Machine learning is !!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss about the capabilities of Machine Learning in SQL Server.

#machine learning #sql server #machine learning with sql server #ml in sql server using python #python in sql server ml #sql server machine learning services