3 Ways to Connect MySQL Database in PHP(Mysql, Mysqli, Pdo)

There are three API interfaces for the connection between PHP and MySQL, namely: MySQL extension of PHP, mysqli extension of PHP, and PHP Data Objects (PDO). The following is a summary of the above three connection methods, in order to select the best in different scenarios. Excellent plan.
Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program

$300 coupon package for all new SMEs and a $500 coupon for paying customers.

PHP's MySQL extension is an early extension designed to allow PHP applications to interact with MySQL databases. The 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 newer database server, it does not support some of the features provided by the later MySQL server. Because it is too old and unsafe, it has been completely replaced by later mysqli;

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

PDO is an abbreviation of PHP Data Objects and is a database abstraction layer specification in PHP applications. PDO provides a unified API interface so that your PHP application does not care about the specific type of database server system to be connected. That is to say, if you use the PDO API, you can seamlessly switch the database server at any time, such as From Oracle to MySQL, only a few PHP codes need to be modified. Its function is similar to JDBC, ODBC, DBI and other interfaces. Similarly, it also solves the sql injection problem and has good security. But he also has shortcomings, some multi-statement execution queries are not supported (but this situation is rare).

The official text also made a tabular comparison between the three:

	PHP mysqli extension	PDO
Introduced PHP version	5.0	5.0
Does PHP5.x include	Yse	Yse
MySQL development status	Active	PHP5.3 Only
Recommended use level in new MySQL projects	Recommended-First	Recommended
API's character set support	Yes	Yes
Support of server-side prepare statements	Yes	Yes
Support status of client prepare statement	No	Yes
Stored procedure support status	Yes	Yes
Multi-statement execution support	Yes	Most
Does it support all MySQL 4.1 and above functions?	Yes	Most

From the official results, msqli is recommended first, followed by PDO. Many of the results given by "civilian" tend to use PDO, because it does not bear the advantages of cross-database, and has the characteristics of fast reading and writing speed.

1. PHP and Mysql extensions (this extension has been deprecated 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(
     '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("could not connect to the database:\n". mysql_error());//Diagnose connection errors
}
mysql_query("set names'utf8'");//coding 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

<?php
$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("could not connect to the database:\n". $mysqli->connect_error);//Diagnose connection errors
}
$mysqli->query("set names'utf8';");//coding 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, object-oriented

<?php
$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("could not connect to the database:\n". $mysqli->connect_error);//Diagnose connection errors
}
$mysqli->query("set names'utf8';");//coding 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();
?>...