Some knowledge points about the differences between Mysqli and MySQL in PHP

Reading, watching the video has not been understanding mysqli and MySQL exactly what the difference. So tonight, "Google" a bit, tidy up. You need a friend to refer to below.

One:
Php-mysql is the PHP operation MySQL Database The most primitive Extension, php-mysqli I for improvement, to mention the relative advanced function, in Extension, itself also increased security. and PDO (PHP data Object) is to provide a abstraction Layer to manipulate the database, with the fact that there is no difference in speaking, so just read the program directly ...
First, let's take a look at a code written in Php-mysql, which is commonly used around the world:

Copy CodeThe code is as follows:
<?php
Mysql_connect ($db _host, $db _user, $db _password);
mysql_select_db ($dn _name);
$result = mysql_query ("Select ' Name ' from ' The Users ' WHERE ' location ' = ' $location '");
while ($row = Mysql_fetch_array ($result, MYSQL_ASSOC))
{
echo $row [' name '];
}
Mysql_free_result ($result);
?>

At first glance there is no problem, but there is some knowledge behind it ...
This method can not Bind Column, the previous example of SQL narration, $location Place is easy to be SQL injection. Then the mysql_escape_string (note: 5.3.0) and mysql_real_escape_string () were developed to solve the problem, but the whole narrative would become complex and ugly, and if there were more columns, You can see what the situation is ...

Copy CodeThe code is as follows:
<?php
$query = sprintf ("SELECT * from Users WHERE user= '%s ' and password= '%s '",
Mysql_real_escape_string ($user),
Mysql_real_escape_string ($password));
mysql_query ($query);
?>

A lot of progress has been made in php-mysqli, except through Bind Column to solve the above problem, but also Transaction, Multi Query, and also provides an Object oriented style (below this paragraph php-mysq Li example) and procedural style (the above Php-mysql example) two ways to write ... Wait a minute.

Copy CodeThe code is as follows:
<?php
$mysqli = new Mysqli ($db _host, $db _user, $db _password, $db _name);
$sql = "INSERT into ' users ' (ID, name, gender, location) VALUES (?,?,?,?)";
$stmt = $mysqli->prepare ($sql);
$stmt->bind_param (' dsss ', $source _id, $source _name, $source _gender, $source _location);
$stmt->execute ();
$stmt->bind_result ($id, $name, $gender, $location);
while ($stmt->fetch ())
{
Echo $id. $name. $gender. $location;
}
$stmt->close ();
$mysqli->close ();
?>

But see here and found some shortcomings, such as Bind Result, this is a little bit more, but it does not matter, because the biggest problem is that this is not an abstract (abstraction) method, so when the backend to change the database, it is the beginning of pain ...
So PDO appeared (note: Currently, for Ubuntu and Debian, PDO does not have a direct kit to install, but must be installed via PECL).

Copy CodeThe code is as follows:
[Email protected]:~$ pecl Search PDO
=======================================
Package stable/(Latest) Local
PDO 1.0.3 (Stable) PHP Data Objects Interface.
PDO_4D 0.3 (Beta) PDO driver for 4d-sql database
Pdo_dblib 1.0 (Stable) Freetds/sybase/mssql driver for PDO
Pdo_firebird 0.2 (Beta) firebird/interbase 6 driver for PDO
PDO_IBM 1.3.2 (Stable) PDO driver for IBM databases
Pdo_informix 1.2.6 (Stable) PDO driver for IBM informix Informix databases
Pdo_mysql 1.0.2 (Stable) MYSQL driver for PDO
PDO_OCI 1.0 (Stable) Oracle call Interface driver for PDO
Pdo_odbc 1.0.1 (Stable) ODBC v3 Interface driver for PDO
Pdo_pgsql 1.0.2 (Stable) PostgreSQL driver for PDO
Pdo_sqlite 1.0.1 (Stable) SQLITE v3 Interface driver for PDO
Pdo_user 0.3.0 (Beta) userspace driver for PDO

When installed through the PECL installation, you can operate the database in the following ways:

Copy CodeThe code is as follows:
<?php
$DSN = "mysql:host= $db _host;dbname= $db _name";
$DBH = new PDO ($DSN, $db _user, $db _password);
$sql = "Select ' Name ', ' Location ' from ' Users ' WHERE ' location ' =?, ' name ' =? ';
$sth = $dbh->prepare ($sql);
$sth->execute (Array ($location, $name));
$result = $sth->fetch (pdo::fetch_obj);
Echo $result->name. $result->location;
$DBH = NULL;
?>

At first glance, the PDO code does not seem to be relatively short, what is the benefit?
1. PDO connects to the database by Connection String to determine which database to connect to.
2. PDO can use Pdo::setattribute to determine the connection settings, such as persistent Connection, the way to return the error (Exception, e_warning, NULL). Even the case of the callback field name ... Wait a minute.
2. PDO supports the function of BIND column, in addition to the basic Prepare, Execute, bind a single field, and specify the field type.
4. PDO is a abstraction Layer so it takes less effort to replace the storage medium than it does.
Unfortunately, although these things have been there for a long time, but still not popular. I think maybe it's because everyone is used to reading books, but those books tend to only introduce the simplest and most traditional ways. Cause a lot of people still use MySQL this kind of party directly connects the database.
However, for the time being, I personally prefer to connect to the database through DBI, such as ActiveRecord and Propel ORM (object-relational Mapping).
For example, take ActiveRecord as an example, if you want to implement such a SQL narrative ...
INSERT into ' users ' (ID, name, gender, location) VALUES (1, ' Roga ', ' Male ', ' TPE ')
To write in PDO is:

Copy CodeThe code is as follows:
<?php
$sql = "INSERT into ' users ' (ID, name, gender, location) VALUES (?,?,?,?)";
$sth = $dbh->prepare ($sql);
$sth->execute (Array (1, ' Roga ', ' Male ', ' TPE '));
?>

But in the case of ActiveRecord, it is:

Copy CodeThe code is as follows:
<?php
$user = new User ();
$user->id = 1;
$user->name = ' Roga ';
$user->gender = ' male ';
$user->location = ' TPE ';
$user->save ();
?>

The latter is not much more concise in syntax, and also greatly reduces the dependence on SQL language! (Refer to Comparison of different SQL implementations for problems with SQL implementation for different repositories)
The above is some simple introduction, if there are omissions fallacy also welcome to add.



MySQL is a non-holding connection function and Mysqli is a permanent connection function. Other words
MySQL each link will open a connected process and mysqli multiple runs mysqli will use the same connection process, thus reducing the server overhead
Some friends use new mysqli (' localhost ', usenamer ', ' Password ', ' DatabaseName ') when programming, and always quote
Wrong, Fatal Error:class ' mysqli ' not found in d:\ ...
Mysqli class is not PHP comes with it?
Not by default, win under to change php.ini, remove Php_mysqli.dll before, Linux to put Mysqli in.
One: Mysqli.dll is a database that allows the operation of objects in a way or process, and it is easy to use. Here is a comparison of a few common operations and mysql.dll.
1:mysql.dll (can be understood as a functional approach):

Copy CodeThe code is as follows:
$conn = mysql_connect (' localhost ', ' user ', ' password '); Connect to MySQL Database
mysql_select_db (' data_base '); Select Database

$result = mysql_query (' select * from Data_base ');//There is a second optional parameter that specifies the open connection
$row = Mysql_fetch_row ($result))//For simplicity, only one row of data is taken here
echo $row [0]; Output the value of the first field

Mysqli also have a procedural approach, but start with the mysqli prefix, the others are similar. If Mysqli is manipulated in a procedural way, some functions must specify a resource, such as mysqli_query (resource ID, SQL statement), and the resource identification parameter is placed in front, while the mysql_query (SQL statement, ' optional ') resource identifier is placed in the back , and can not be specified, it is the last open connection or resource by default.
2mysqli.dll (object mode):

Copy CodeThe code is as follows:
$conn = new mysqli (' localhost ', ' user ', ' password ', ' data_base ');
The connection here is new, and the last parameter is to specify the database directly, without mysql_select_db ().
can also be constructed when not specified, then $conn-select_db (' data_base ')
$result = Query (' SELECT * from Data_base '), $conn
$row = $result-Fetch_row (); Fetch a row of data
Echo Row[0]; Output the value of the first field

II: Mysql_fetch_row (), mysql_fetch_array ()
These two functions return an array, the difference is that the first function returns an array that contains only the values, we can only $row[0],
$row [1], which reads the data as an array subscript, and the array returned by Mysql_fetch_array () contains both the first and key values
In the right form, we can read the data in this way (if the database field is USERNAME,PASSWD):
$row [' username '], $row [' passwd ']
Furthermore, if you use ($row as $kay + $value), you also get the field name of the database directly.
More important is that mysqli is a new library of functions provided by PHP5, (i) for improvement, and for faster execution.