Three ways of PHP connecting MySQL database

Time:2021-4-6

This article introduces three ways of PHP connecting to MySQL database (mysql, mysqli, PDO), and analyzes the related operation skills and precautions of PHP connecting to MySQL database based on MySQL, mysqli, PDO with examples. There is a certain reference value, friends in need can refer to it, I hope to help you.

There are three API interfaces between PHP and mysqlMySQL extension of PHPMysqli extension of PHPPHP data object (PDO)The following is a summary of the above three connection modes, in order to select the optimal scheme in different scenarios.

  • MySQL extension of PHPIt is an early extension of design and development to allow PHP applications to interact with MySQL database. MySQL extension provides a process oriented interface and is designed for MySQL 4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or the updated database server, it does not support some features provided by the later MySQL server. Because it is too old and unsafe, it has been completely replaced by mysqli;

  • Mysqli extension of PHP, which we sometimes call MySQL enhanced extension, can be used to use new advanced features in MySQL 4.1.3 or later. It is characterized by object-oriented interface, prepared statement support, multi statement execution support, transaction support, enhanced debugging ability, embedded service support and preprocessing mode, which completely solves the problem of SQL injection. However, it only supports MySQL database. If you do not operate other databases, this is undoubtedly the best choice.

  • PDOIs the abbreviation of PHP data objects, is a database abstraction layer specification in PHP applications. PDO provides a unified API interface, which enables your PHP application not to care about the specific type of database server system to be connected. In other words, if you use PDO API, you can seamlessly switch the database server whenever you need, such as from Oracle to MySQL. You only need to modify very little PHP code. Its functions are similar to interfaces such as JDBC, ODBC and DBI. It also solves the problem of SQL injection and has good security. However, it also has the disadvantage that some multi statement execution queries are not supported (though this situation is rare).

The official article also makes a tabular comparison among the three

Mysqli extension of PHP PDO MySQL extension of PHP
PHP version introduced 5.0 5.0 Before 3.0
Does PHP 5. X contain yes yes yes
MySQL development status active Active in php5.3 Maintenance only
Recommended usage in new MySQL projects Recommendations – preferred proposal Not recommended
Character set support of API yes yes no
Server side prepare statement support yes yes no
Client side support for prepare statements no yes no
Stored procedure support yes yes no
Multi statement execution support yes majority no
Do you support all functions above MySQL 4.1 yes majority no

From the official results, msqli is recommended first, followed by PDO. However, most of the results given by “folk” tend to use PDO, because it does not have the advantages of cross database, and has the characteristics of fast reading and writing speed.

1. PHP and MySQL extensions (this extension has been abandoned since PHP 5.5.0 and will be removed in the future). PHP’s native way to connect to databases is process oriented

'127.0.0.1:3306',  
  
  'db'   => 'test',  
  
  'db\_user' => 'root',  
  
  'db\_pwd' => 'root',  
  
  );  
  
$mysql\_conn = @mysql\_connect($mysql\_conf\['host'\], $mysql\_conf\['db\_user'\], $mysql\_conf\['db\_pwd'\]);  
  
if (!$mysql\_conn) {  
  
  die("could not connect to the database:\n" . mysql\_ Error (); // diagnose connection errors  
  
}  
  
mysql\_ Query ("set names' utf8 '"); // code conversion  
  
$select\_db = mysql\_select\_db($mysql\_conf\['db'\]);  
  
if (!$select\_db) {  
  
  die("could not connect to the db:\n" . mysql\_error());  
  
}  
  
$sql = "select \* from user;";  
  
$res = mysql\_query($sql);  
  
if (!$res) {  
  
  die("could get the res:\n" . mysql\_error());  
  
}  
  
while ($row = mysql\_fetch\_assoc($res)) {  
  
  print\_r($row);  
  
}  
  
mysql\_close($mysql\_conn);  
  
?>

2. PHP and mysqli extension, process oriented, object-oriented

'127.0.0.1:3306',  
  
  'db'   => 'test',  
  
  'db\_user' => 'root',  
  
  'db\_pwd' => 'joshua317',  
  
  );  
  
$mysqli = @new mysqli($mysql\_conf\['host'\], $mysql\_conf\['db\_user'\], $mysql\_conf\['db\_pwd'\]);  
  
if ($mysqli->connect\_errno) {  
  
  die("could not connect to the database:\n" . $mysqli->connect\_ Error); // diagnose connection error  
  
}  
  
$mysqli - > query ("set names' utf8 ';"); // code conversion  
  
$select\_db = $mysqli->select\_db($mysql\_conf\['db'\]);  
  
if (!$select\_db) {  
  
  die("could not connect to the db:\n" . $mysqli->error);  
  
}$sql = "select uid from user where name = 'joshua';";  
  
$res = $mysqli->query($sql);  
  
if (!$res) {  
  
  die("sql error:\n" . $mysqli->error);  
  
}  
  
 while ($row = $res->fetch\_assoc()) {  
  
    var\_dump($row);  
  
  }  
  
$res->free();  
$mysqli->close();  
?>

3. PHP and PDO extension, process oriented and object-oriented

'127.0.0.1:3306',  
  
  'db'   => 'test',  
  
  'db\_user' => 'root',  
  
  'db\_pwd' => 'joshua317',  
  
  );  
  
$pdo = new PDO(" mysql:host= " . $mysql\_ conf\['host'\] . ";dbname=" . $mysql\_ conf\['db'\], $mysql\_ conf\['db\_ user'\], $mysql\_ conf\['db\_ PWD '\]; // create a PDO object  
  
$pdo->exec("set names 'utf8'");  
  
$sql = "select \* from user where name = ?";  
  
$stmt = $pdo->prepare($sql);  
  
$stmt->bindValue(1, 'joshua', PDO::PARAM\_STR);  
  
$rs = $stmt->execute();  
  
if ($rs) {  
  
  // PDO::FETCH\_ Assoc associative array form  
  
  // PDO::FETCH\_ Num numeric index array form  
  
  while ($row = $stmt->fetch(PDO::FETCH\_ASSOC)) {  
  
    var\_dump($row);  
  
  }  
  
}  
  
$PDO = null; // close connection  
  
?>

More learning content can be accessedAs long as you can read it, your salary will go up a step

The above contents hope to help youA lot of PHPer always encounter some problems and bottlenecks when they are upgrading. They write too much business code and have no sense of direction. They don’t know where to start to improve. For this, I have sorted out some materials, including but not limited to:Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, tp6, laravel, yii2, redis, spool, swoft, Kafka, MySQL optimization, shell script, docker, microservice, nginxAnd so on many knowledge points advanced dry goods need can be free to share with you, need can click the link to getAdvanced PHP monthly salary 30K > > > architect growth path [free access to videos and interview documents]

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]