Three ways to connect php to mysql database

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 for the above three connection methods. Below is a summary to prepare for choosing the best solution in different scenarios.

(Related tutorial recommendations: mysql video tutorial)

PHP’s MySQL extension is an early extension designed and developed to allow PHP applications to interact with MySQL databases. 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:

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 obsolete since PHP 5.5.0 and will be removed in the future). PHP’s native way to connect to the database is process-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;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 extension, process 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 extension, process 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;//关闭连接
?>

Related recommendations: php training

The above is the detailed content of Three ways to connect php to mysql database. For more information, please follow other related articles on the PHP Chinese website!