PHP connects to MySQL

PHP 5 and above are recommended to use the following method to connect to MySQL:

MySQLi extension ("i" means improved)

PDO (PHP Data Objects)

In early versions of PHP we used the MySQL extension. However, this extension was deprecated in 2012.

For a complete introduction to PDO, please refer to: PHP PDO in the PHP Reference Manual

Should I use MySQLi or PDO?

If you need a short answer, "use whichever you are used to".

MySQLi and PDO have their own advantages:                                                                                               Support MySQL

API OOP

Connection                                                                                                                                             

#Object mapping support Supported Supported

Prepared statements (client) Supported Not supported

Performance                                                                              Support

Therefore, the various methods and properties of PDO are more versatile and must take into account the operations of various databases and are applicable Wider scope; If your project needs to switch between multiple databases, it is recommended to use PDO; in this way, you only need to modify the connection string and department query statement. MySQLi's series of classes and methods can only operate MySQL database; using MySQLi, if it is a different database, you need to rewrite all codes, including queries.

Both are object-oriented, but MySQLi also provides an API interface.

Both support prepared statements. Prepared statements can prevent SQL injection and are very important for the security of web projects.

MySQLi and PDO connect to MySQL instance

In this chapter and the following chapters, we will use the following three methods to demonstrate PHP Operating MySQL:

MySQLi (object-oriented)

MySQLi (procedure-oriented)

PDO

MySQLi installation

Linux and Windows: Installation in php5 mysql package The MySQLi extension is installed automatically in most cases.

For installation details, please check: http://php.net/manual/en/mysqli.installation.php

You can use the .php file with the suffix name of PHP software (PhpStorm) Write phpinfo() and click to open the browser to see if the installation is successful:

##PDO Installation

For installation details, please check: http://php.net/manual/en/pdo.installation.php

You can check whether the installation is successful through phpinfo() ：

Connect to MySQL

Before we access the MySQL database, we need to connect to the database server first :

Example (MySQLi - Object-oriented)

<?php
// 三个参数
$servername = "localhost";   // 服务器地址（域名、IP）
$username = "username";   // 数据库用户名 
$password ="password";   // 数据库密码(可能为空)
// 创建连接
$conn =new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
} 
echo "连接成功";
?>

Note that $connect_error in the above object-oriented example is in PHP 5.2.9 and 5.3.0 added in. If you need compatibility with earlier versions, please use the following code replacement:

// Detect connection if (mysqli_connect_error()) {
die("Database connection Failure: " . mysqli_connect_error());
}

Instance (MySQLi - Procedure-oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// 创建连接
$conn = mysqli_connect($servername,$username, $password);
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "连接成功";
?>

Note that in the above process-oriented example, if you want to directly select the name of the database you want to connect to when connecting to the database, there will be a fourth parameter: $db_name = "db_name"; // Database name

// Create connection

$conn = mysqli_connect($servername, $username, $password, $db_name);

Instance (PDO)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB",$username, $password);
    echo "连接成功";
}
catch(PDOException $e)  // PDOException 错误处理 
{
   echo $e->getMessage();  // getMessage()取得文本化的错误信息 
}
?>

Note that we have specified the database (myDB) in the above PDO instance. PDO needs to set the database name during the connection process. If not specified, an exception will be thrown.        

Close the connection

The connection will be automatically closed after the script is executed. You can also use the following code to close the connection:

Instance (MySQLi - Object Oriented)

$coon->close();

Example (MySQLi - Procedure-oriented)

mysql_close($coon);

Example (PDO)

mysql_close($coon);

##Appendix

Attachment The previous complete code to connect to the database and obtain the query results (for reference):

Example

<?php
$conn = mysqli_connect($servername,$username,$password);  // 获取连接
if($conn){  //判断是否连接
   mysqli_select_db($conn,"db_name"); // 选择一个数据库
   mysqli_query($conn,"set names utf8");   // 查询输出要使用utf8的编码格式，避免乱码
   $sql = "select * from product;";  // 查找到数据库的product表（）
   $result = mysqli_query($conn,$sql);  // 从键连获取到数据库的字表 
   while($row = mysqli_fetch_array($result,MYSQL_ASSOC)){   // MYSQLI_ASSOC返回键值形式数组
      echo $row["需要输出的表中列的名称"]; 
   } 
   mysqli_free_result($result);   // 释放内存在 
   mysqli_close($conn);   // 关闭连接 
   echo "成功"; 
   }else{ 
   echo "失败"; 
}; 
?>