Summary of methods for connecting PHP to MySQL

This article brings you relevant knowledge about PHP video tutorial, which mainly introduces the relevant content of the connection between PHP and MySQL. There are three API interfaces for the connection between PHP and MySQL, namely: PHP's MySQL extension, PHP's mysqli extension, and PHP Data Object (PDO), let's take a look at them separately. I hope it will be helpful to everyone.

Recommended study: "PHP"

We can use PHP's mysql_connect statement, MySQLi statement or PDO is used to connect to MySQL. Before we can access the data in the MySQL database, we need to be able to connect to the MySQL server. Let's take a look at how to connect to MySQL.

There are three API interfaces for the connection between PHP and MySQL, namely: PHP's MySQL extension, PHP's mysqli extension, and PHP Data Object (PDO). The following is a summary of the above three connection methods for future reference. Choose the best solution under different scenarios.

1. MySQL extension

PHP’s MySQL extension is an early extension designed and developed to allow PHP applications to interact with the MySQL database.

MySQL extension provides a procedure-oriented interface and is designed for MySQL4.1.3 or earlier.

Therefore, although this extension can interact with MySQL4.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 5.5.0 起已废弃。
    $mysql_conf = array(
        'host'    => '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("无法连接到数据库:\n" . mysql_error());//诊断连接错误
    }
    mysql_query("set names 'utf8'");//编码转化为UTF-8
    //判断连接的数据库是否存在
    $select_db = mysql_select_db($mysql_conf['db']);
    if (!$select_db) {
        die("无法连接到数据库:\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. Mysqli extension

PHP’s mysqli extension, we sometimes call it MySQL enhanced extensions can be used to use the new advanced features in MySQL 4.1.3 or later versions.

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 do not operate other databases, this is undoubtedly the best choice.

//PHP与Mysqli扩展,面向过程、对象
 $mysql_conf = array(
     'host'    => '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("无法连接到数据库:\n" . $mysqli->connect_error);//诊断连接错误
 }
 $mysqli->query("set names 'utf8';");//编码转化utf8
 $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. PDO extension

PDO is the abbreviation of PHP Data Objects and 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 use it whenever needed. Seamlessly switch database servers, such as from Oracle to MySQL, with only a small amount of PHP code modifications.

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 shortcomings. Some multi-statement execution queries are not supported (but this situation is rare).

//PHP与PDO扩展,面向过程、对象
  $mysql_conf = array(
      'host'    => '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']);//创建一个pdo对象
  $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 关联数组形式
      // PDO::FETCH_NUM 数字索引数组形式
      while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
          var_dump($row);
      }
  }
  
  $pdo = null;//关闭连接

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of Summary of methods for connecting PHP to MySQL. For more information, please follow other related articles on the PHP Chinese website!