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

Composer Centos 7

Install Composer using cURL

# sudo curl -sS https://getcomposer.org/installer | php

Want to make Composer globally accessible?

# mv composer.phar /usr/local/bin/composer

preg_match

Simple regex

Regex quick reference
[abc]     A single character: a, b or c
[^abc]     Any single character but a, b, or c
[a-z]     Any single character in the range a-z
[a-zA-Z]     Any single character in the range a-z or A-Z
^     Start of line
$     End of line
\A     Start of string
\z     End of string
.     Any single character
\s     Any whitespace character
\S     Any non-whitespace character
\d     Any digit
\D     Any non-digit
\w     Any word character (letter, number, underscore)
\W     Any non-word character
\b     Any word boundary character
(...)     Capture everything enclosed
(a|b)     a or b
a?     Zero or one of a
a*     Zero or more of a
a+     One or more of a
a{3}     Exactly 3 of a
a{3,}     3 or more of a
a{3,6}     Between 3 and 6 of a

options: i case insensitive m make dot match newlines x ignore whitespace in regex o perform #{...} substitutions only onc

http://php.net/manual/en/function.preg-match.php

form template site

Form template

https://w3layouts.com/free-web-elements/

PHP: How to send HTTP response code?

378

I just found this question and thought it needs a more comprehensive answer:

As of PHP 5.4 there are three methods to accomplish this:

Assembling the response code on your own (PHP >= 4.0)

The header() function has a special use-case that detects a HTTP response line and lets you replace that with a custom one

header("HTTP/1.1 200 OK");

However, this requires special treatment for (Fast)CGI PHP:

$sapi_type = php_sapi_name();
if (substr($sapi_type, 0, 3) == 'cgi')
    header("Status: 404 Not Found");
else
    header("HTTP/1.1 404 Not Found");

Note: According to the HTTP RFC, the reason phrase can be any custom string (that conforms to the standard), but for the sake of client compatibility I do not recommend putting a random string there.

Note: php_sapi_name() requires PHP 4.0.1

3rd argument to header function (PHP >= 4.3)

There are obviously a few problems when using that first variant. The biggest of which I think is that it is partly parsed by PHP or the web server and poorly documented.

Since 4.3, the header function has a 3rd argument that lets you set the response code somewhat comfortably, but using it requires the first argument to be a non-empty string. Here are two options:

header(':', true, 404);
header('X-PHP-Response-Code: 404', true, 404);

I recommend the 2nd one. The first does work on all browsers I have tested, but some minor browsers or web crawlers may have a problem with a header line that only contains a colon. The header field name in the 2nd. variant is of course not standardized in any way and could be modified, I just chose a hopefully descriptive name.

http_response_code function (PHP >= 5.4)

The http_response_code() function was introduced in PHP 5.4, and it made things a lot easier.

http_response_code(404);

That's all.

reading a file and printing specific values

<?php
// get contents of a file into a string
$filename = "./areacode.log";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize($filename));
fclose($handle);

$elements=explode("\n",$contents) ;
foreach($elements as $value) {
if (stristr($value,"California ")) {   // If  Number  California  is found it will print all values co Calif
echo "$value<br>";
}

}
?>

Original File
Alabama(1)251

Alabama(1)256

Alabama(1)334

Alaska(1)907

Arizona (1)480

Arizona (1)520

Arizona (1)602

Arizona (1)623

Arizona (1)928

Arkansas(1)479

Arkansas(1)501

Arkansas(1)870

California (1)209

California (1)213

California (1)310

California (1)323

California (1)408

California (1)415

json encode y decode

<?php

$arr=array(6000=>"blocked",6001=>40000);

$info=json_encode($arr);

print_r($info);

$info=json_decode($info,true);

foreach($info as $key=>$value){
echo "<br><br>";
echo  "$key=>$value <br>";
}


?>