PHP connection MySQL database in several ways (MySQL, mysqli, PDO)

Source: Internet
Author: User
Tags character set dsn ini prepare sql injection
I. Characteristics and comparison
    • The MySQL extension of PHP is an early extension of design and development that allows PHP applications to interact with MySQL databases. The MySQL extension provides a process-oriented interface and is designed for MySQL4.1.3 or earlier versions. As a result, this extension can interact with the MySQL4.1.3 or updated database server, but does not support some of the features provided by the late MySQL server. Because it is too old and unsafe, it has been completely replaced by the later mysqli.
    • PHP's mysqli extensions, which we sometimes call MySQL enhanced extensions, can be used to use new advanced features in MySQL4.1.3 or newer versions. Its features are: Object-oriented interface, prepared statement support, multiple statement execution support, transaction support, enhanced debugging capability, embedded service support, and preprocessing method completely solve the problem of SQL injection. But it also has the drawback, is only supports the MySQL database. If you do not operate other databases, this is certainly the best choice.
    • PDO is the abbreviation for 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 type of database server system you want to connect to. That is, if you use the PDO API, you can seamlessly switch database servers whenever you need them, such as from Oracle to MySQL, and you'll just need to modify a few PHP code. It features similar interfaces as JDBC, ODBC, DBI, and so on. In the same way, it solves the problem of SQL injection and has good security. However, he also has shortcomings, some multiple statement execution query does not support (but this situation is very small).
Guanven also makes a list comparison between the three:

mysqli Extension of PHP PDO (using PDO mysql driver and MySQL native drive) mysql extensions for PHP
Introduction of PHP Version 5.0 5.0 Before 3.0
Whether the php5.x contains Is Is Is
MySQL Development status Active Active in the PHP5.3 Maintenance only
Recommended usage in new MySQL project Recommendations-Preferred Suggestions Not recommended
Character set support for APIs Is Is Whether
Support for service-side prepare statements Is Is Whether
Support for client Prepare statements Whether Is Whether
Stored Procedure Support Scenarios Is Is Whether
Multi-statement execution support scenarios Is Most Whether
Whether to support all MySQL4.1 or above features Is Most Whether

From the official point of view, the first recommendation Msqli, followed by PDO. and "Folk" gives a lot of results are inclined to use PDO, because it does not have the advantages of cross-Library, more read and write faster characteristics.

Second, the module installs and calls

Take Ubuntu and its derivative version as an example, through sudo apt-get install mysqlnd can increase the PHP PDO and mysqli support (source installation can choose Phpize Program to dynamically expand). You can open the view through the Phpinfo page, where under MYSQLND, you can see the following:

API Extensions Mysql,mysqli,pdo_mysql

Mysqli and PDO connection methods

The code is as follows Copy Code

$pdo = new PDO ("Mysql:host=localhost;dbname=database", ' username ', ' password ');
MYSQLI, process-oriented approach
$mysqli = Mysqli_connect (' localhost ', ' username ', ' password ', ' database ');
Mysqli, Object oriented
$mysqli = new mysqli (' localhost ', ' username ', ' password ', ' database ');

mysqli example of a query through a configuration file:

Configuration file

The code is as follows Copy Code

/var/www/t$ Cat config.ini.php
$dbhost = "localhost";
$dbuser = "root";
$dbpwd = "123456";
$dbname = "Wish";
$charName = "UTF8";

Query code

The code is as follows Copy Code

/var/www/t$ Cat mysqlquery.php
Require_once ("config.ini.php");
$MYSQLIOBJ = new Mysqli ($dbhost, $dbuser, $dbpwd, $dbname);
if (Mysqli_connect_errno ()) {
echo "Connection Failed". Mysqli_connect_error ();
printf ("Connect failed:%sn", Mysqli_connect_error ());
Exit ();
$sql = "SELECT * from Wp_wish";
$MYSQLIOBJ->query ("Set names $charName");
3, processing results
$res = $MYSQLIOBJ->query ($sql);
Var_dump ($res);
FETCH_ASSOC Fetch_array Fetch_object
while ($row = $res->fetch_row ()) {
Print_r ($row);
foreach ($row as $val) {
Echo '--'. $val;
Echo ' <br/> ';
4. Close Resources
$res->free ();
$MYSQLIOBJ->close ();

Queries made in PDO mode

The code is as follows Copy Code

$dsn = ' mysql:dbname=test;host= ';
$user = ' root ';
$password = ' 1 ';
$DBH = new PDO ($DSN, $user, $password);
$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_exception);
$r = $dbh->query (' SELECT * from user ');
Var_dump ($R);
foreach ($r as $v) {
Var_dump ($v);


Like Discuz, Phpcms, AKCMS and other programs will generally provide two ways to connect mysqli or Pdo-mysql (if your deployment environment to support), specifically in the use of classes in the above PHP program, can be based on their own circumstances

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.