How to use PDO to connect to the database in php7

In php, you can use pdo to connect to the database, but not everyone knows the method that is compatible with windows and centos at the same time. Let’s introduce the method of using PDO to connect and be compatible with windows and centos at the same time. If you need it, you can read it look.

1. First, the windows installation extension

7.0.x extension download address:

Microsoft Drivers for PHP for SQL Server https ://www.microsoft.com/en-us/download/details.aspx?id=20098

ODBC Driver:

Microsoft® ODBC Driver 11 for SQL Server® - Windows (Supported Sql Server® 2005) https://www.microsoft.com/zh-CN/download/details.aspx?id=36434

Microsoft® ODBC Driver 13 for SQL Server® - Windows Linux (supports the latest SQL Server® 2016) https://www.microsoft.com/zh-CN/download/details.aspx?id=50420

Installation configuration:

Download the PHP extension of SQL Server ( Microsoft Drivers for PHP for SQL Server), there are multiple files in the connection, you only need to download SQLSRV40.EXE.

After decompression, select 32-bit or 64-bit extensions according to your PHP version, and pay attention to distinguish (Thread safe?) nts and ts.

Copy the files required for the extension to the PHP extension directory. (For example, what I use here is: php_pdo_sqlsrv_7_ts_x64.dll and php_sqlsrv_7_ts_x64.dll)

Modify php.ini and add the following two lines to enable the extension:

extension=php_sqlsrv_7_ts_x64.dll
extension=php_pdo_sqlsrv_7_ts_x64.dll

Install the ODBC Driver and choose according to your needs Download and install from the address above.

You can now use phpinfo() to check whether the pdo_sqlsrv module was loaded successfully.

2. Centos installation extension

The premise seems that the document says that it must be PHP7 or later. In the past, many of them were third-party FreeTDS. No matter how you try it, it is not as stable as Microsoft’s official version!

1. Join Microsoft's source

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssqlrelease.repo

2. To prevent conflicts, uninstall the original version first (optional)

yum remove unixODBC

3. Install the driver (all three must be installed , both are indispensable)

yum install msodbcsql mssql-tools unixODBC-devel

4. Download the pdo_sqlsrv expansion package

http://pecl.php.net/package/pdo_sqlsrv

5. Take 4.3.8 as an example

tar -zxvf pdo_sqlsrv-4.3.8.tgz

6. Enter the decompression directory

cd pdo_sqlsrv-4.0.8

7. Execute an extension command of PHP

/usr/local/php/bin/phpize

8. Compile

./configure --with-php-config=/usr/local/php/bin/php-config

9. Install

make && make install

10. Modify /usr/local/php/etc/php .ini Add extension

extension = "pdo_sqlsrv.so"

11. Restart php-fpm and nginx, you're done

Now I will focus on my pitfall record:

After windows is installed, 2 types Method call, the sqlsrv_connect method I used at the beginning can be called successfully. For the specific API, please refer here.

https://docs.microsoft.com/en-us/sql/connect/php/sqlsrv-driver-api-reference

But with the above installation method, the Centos server ultimately only installs pdo_sqlsrv.so. Therefore, it can only be called using another pdo method, which is universal.

https://github.com/Microsoft/msphpsql/edit/master/sample/pdo_sqlsrv_sample.php

<?php
    echo "\n";
    $serverName = "tcp:yourserver.database.windows.net,1433";
	$database = "yourdatabase";
	$uid = "yourusername";
	$pwd = "yourpassword";
	
	 //Establishes the connection
	 $conn = new PDO( "sqlsrv:server=$serverName ; Database = $database", $uid, $pwd);
	 
	 //Select Query
	 $tsql = "SELECT [CompanyName] FROM SalesLT.Customer";
	 
	 //Executes the query
	 $getProducts = $conn->query( $tsql );
	 
	 //Error handling
	 FormatErrors ($conn->errorInfo());
	 
	 $productCount = 0;
	 $ctr = 0;
	 ?> 
	 
	 <h1> First 10 results are : </h1>
	 
	 <?php
	 while($row = $getProducts->fetch(PDO::FETCH_ASSOC))
	 {
		 if($ctr>9)
			 break; 
		 $ctr++;
		 echo($row[&#39;CompanyName&#39;]);
		 echo("<br/>");
		 $productCount++;
	 }
	 $getProducts = NULL;
	 
	 $tsql = "INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT INSERTED.* VALUES (&#39;SQL New 1&#39;, &#39;SQL New 2&#39;, 0, 0, getdate())";
	 
	 //Insert query
	 $insertReview = $conn->query( $tsql );
	 FormatErrors ($conn->errorInfo());
	 ?> 
	 
	 <h1> Product Key inserted is :</h1> 
	 
	 <?php
	 while($row = $insertReview->fetch(PDO::FETCH_ASSOC))
	 {
		 echo($row[&#39;ProductID&#39;]."<br/>");
	 }
	 $insertReview = NULL;
	 
	 //Delete Query
	 //We are deleting the same record
	 $tsql = "DELETE FROM [SalesLT].[Product] WHERE Name=?";
	 $param = "SQL New 1";
	 
	 $deleteReview = $conn->prepare($tsql);
	 $deleteReview->bindParam(1, $param);
	 
	 $deleteReview->execute();
	 FormatErrors ($deleteReview->errorInfo());
	 
	 function FormatErrors( $error )
	 {
	    /* Display error. */
	    echo "Error information: <br/>";
	 
	    echo "SQLSTATE: ".$error[0]."<br/>";
	    echo "Code: ".$error[1]."<br/>";
	    echo "Message: ".$error[2]."<br/>";
	 }
?>

Recommended learning: php video tutorial

The above is the detailed content of How to use PDO to connect to the database in php7. For more information, please follow other related articles on the PHP Chinese website!