A brief discussion on three ways to connect PHP to MySQL database

This article will introduce you to the three methods of PHP connecting to the MySQL database (mysql, mysqli, pdo), and analyze the related operating skills and precautions of PHP based on the three methods of connecting to the MySQL database based on mysql, mysqli, and pdo in the form of examples. matter. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

There are three API interfaces for connecting PHP to MySQL, namely: PHP's MySQL extension, PHP's mysqli extension, and PHP Data Object (PDO). The following is for the above three Let’s summarize the connection methods to prepare for choosing the best solution in different scenarios.

The MySQL extension for PHP is an early extension designed and developed to allow PHP applications to interact with the MySQL database. The MySQL extension provides a procedure-oriented interface and is designed for MySQL 4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or newer database servers, it does not support some features provided by later MySQL servers. Because it is too old and unsafe, it has been completely replaced by the later mysqli;

PHP’s mysqli extension, which we sometimes call the MySQL enhanced extension, can be used to use the new MySQL 4.1.3 or newer version. Advanced features. Its features are: object-oriented interface, prepared statement support, multi-statement execution support, transaction support, enhanced debugging capabilities, embedded service support, and preprocessing methods that completely solve the problem of SQL injection. However, it also has a disadvantage, that is, it only supports mysql database. If you don't operate other databases, this is undoubtedly the best choice.

PDO is the abbreviation of PHP Data Objects, which is a database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the specific database server system type to be connected. In other words, if you use PDO's API, you can seamlessly switch database servers whenever needed, such as From Oracle to MySQL, only a small amount of PHP code needs to be modified. Its functions are similar to interfaces such as JDBC, ODBC, and DBI. Similarly, it also solves the SQL injection problem and has good security. However, it also has disadvantages. Some multi-statement execution queries are not supported (but this situation is rare).

The official article also makes a list comparison between the three:

	PHP’s mysqli extension	PDO	MySQL extension for PHP
Introduced PHP version	5.0	5.0	3.0 Before
Whether PHP5.x contains	Yes	Yes	Yes
MySQL Development Status	Active	Active in PHP5.3	Maintained only
Suggestions in new MySQL projects Usage level	Recommendation-Preferred	Recommendation	Not recommended
API character set support	Yes	Yes	No
Support of server-side prepare statement	Yes	Yes	No
Support of client prepare statement	No	Yes	No
Stored procedure support	Yes	Yes	No
Multiple statement execution support	Yes	Most	No
Does all MySQL4.1 or above functions be supported?	Yes	Most	No

Judging from the official results, msqli is recommended first, followed by PDO. Many 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 extension (This extension has been abandoned since PHP 5.5.0 and will be removed in the future). PHP’s native way to connect to the database is process-oriented. ’s

<?php
$mysql_conf = array(
  &#39;host&#39;  => &#39;127.0.0.1:3306&#39;, 
  &#39;db&#39;   => &#39;test&#39;, 
  &#39;db_user&#39; => &#39;root&#39;, 
  &#39;db_pwd&#39; => &#39;root&#39;, 
  );
$mysql_conn = @mysql_connect($mysql_conf[&#39;host&#39;], $mysql_conf[&#39;db_user&#39;], $mysql_conf[&#39;db_pwd&#39;]);
if (!$mysql_conn) {
  die("could not connect to the database:\n" . mysql_error());//诊断连接错误
}
mysql_query("set names &#39;utf8&#39;");//编码转化
$select_db = mysql_select_db($mysql_conf[&#39;db&#39;]);
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 extensions, process-oriented and object-oriented

<?php
$mysql_conf = array(
  &#39;host&#39;  => &#39;127.0.0.1:3306&#39;, 
  &#39;db&#39;   => &#39;test&#39;, 
  &#39;db_user&#39; => &#39;root&#39;, 
  &#39;db_pwd&#39; => &#39;joshua317&#39;, 
  );
$mysqli = @new mysqli($mysql_conf[&#39;host&#39;], $mysql_conf[&#39;db_user&#39;], $mysql_conf[&#39;db_pwd&#39;]);
if ($mysqli->connect_errno) {
  die("could not connect to the database:\n" . $mysqli->connect_error);//诊断连接错误
}
$mysqli->query("set names &#39;utf8&#39;;");//编码转化
$select_db = $mysqli->select_db($mysql_conf[&#39;db&#39;]);
if (!$select_db) {
  die("could not connect to the db:\n" . $mysqli->error);
}$sql = "select uid from user where name = &#39;joshua&#39;;";
$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 extensions, process-oriented and object-oriented

<?php
$mysql_conf = array(
  &#39;host&#39;  => &#39;127.0.0.1:3306&#39;, 
  &#39;db&#39;   => &#39;test&#39;, 
  &#39;db_user&#39; => &#39;root&#39;, 
  &#39;db_pwd&#39; => &#39;joshua317&#39;, 
  );
$pdo = new PDO("mysql:host=" . $mysql_conf[&#39;host&#39;] . ";dbname=" . $mysql_conf[&#39;db&#39;], $mysql_conf[&#39;db_user&#39;], $mysql_conf[&#39;db_pwd&#39;]);//创建一个pdo对象
$pdo->exec("set names &#39;utf8&#39;");
$sql = "select * from user where name = ?";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(1, &#39;joshua&#39;, PDO::PARAM_STR);
$rs = $stmt->execute();
if ($rs) {
  // PDO::FETCH_ASSOC 关联数组形式
  // PDO::FETCH_NUM 数字索引数组形式
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    var_dump($row);
  }
}
$pdo = null;//关闭连接
?>

For more related knowledge, please pay attention to PHP Chinese website! !

The above is the detailed content of A brief discussion on three ways to connect PHP to MySQL database. For more information, please follow other related articles on the PHP Chinese website!