The difference between MySQL and mysqli

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, mention the relative advanced function, Extension, itself also increased security. and PDO (PHP Data Object) is to provide a abstraction Layer to manipulate the database, through the text narrative actually do not see what the difference, rather than directly look at the code.
First, let's look at a piece of code written in Php-mysql, which is a common example:

<?PHPmysql_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 are some routines behind it.

This method can not Bind Column, the previous example of SQL narration, $location Place is easy to be SQL injection. Then it developed the mysql_escape_string (note: 5.3.0) and mysql_real_escape_string () to solve the problem, but the whole narrative would be complicated and ugly, and if there were more records, You can imagine what it would be like.

<?  $querysprintfmysql_real_escape_string($usermysql_real_escape_ String($passwordmysql_query($query

There has been a lot of progress in php-mysqli, in addition to solving the above problems through Bind Column, but also supporting Transaction, Multi Query, and providing an object-oriented style (the following example of Php-mysqli) and the process-oriented wind Lattice (php-mysql example above) two ways to write ... Wait a minute.

<?PHP$mysqli=NewMysqli ($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 ();?>

There are some drawbacks, such as Bind Result, which is somewhat superfluous, but it doesn't matter because the biggest problem is that this is not an abstract (abstraction) approach, so when the backend changes the database, it's the beginning of the nightmare.
So PDO appeared (note: Currently, for Ubuntu and Debian, PDO does not have a direct kit to install, but must be installed via PECL).

[Email protected]:~$ pecl Search PDO=======================================Package Stable/(Latest) Local PDO1.0.3 (Stable) PHP Data ObjectsInterface.pdo_4d0.3 (Beta) PDO driver for4d-SQL Database Pdo_dblib1.0 (Stable) Freetds/sybase/mssql driver forPDO Pdo_firebird0.2 (Beta) firebird/interbase 6 driver forPDO PDO_IBM1.3.2 (Stable) PDO driver forIBM Databases Pdo_informix1.2.6 (Stable) PDO driver forIBM informix Informix Databases Pdo_mysql1.0.2 (Stable)MySQLDriver forPDO Pdo_oci1.0 (Stable) Oracle callInterfaceDriver forPDO Pdo_odbc1.0.1 (Stable) ODBC v3InterfaceDriver forPDO Pdo_pgsql1.0.2 (Stable) PostgreSQL driver forPDO Pdo_sqlite1.0.1 (Stable) SQLite v3InterfaceDriver forPDO Pdo_user0.3.0 (Beta) userspace driver forPdo

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

<?PHP$dsn= "mysql:host=$db _host;d bname=$db _name"; $DBH=NewPDO ($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 short, so what is the benefit?

String2. PDO can use Pdo::setattribute to determine the connection settings, such as persistent Connection, the way to return the error (Exceptione_warningNULL   2. PDO supports the function of Bind Column, in addition to the basic Prepare,

Unfortunately, although these things have been there for a long time, but still not popular. I think maybe it's because people are used to reading the book of introductory books, but those books tend to only introduce the simplest and most traditional way. Cause a lot of people still use MySQL this kind of party directly connect database.

However, for the time being, I personally prefer to connect to the database via 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 VALUES (1'roga'male'TPE' 

To write in PDO is:

<?  $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:

<?  $usernew$user->id = 1$user->name = ' Roga '   $user->gender = ' Male '$user->location = ' TPE '$user- >

The latter is not much more concise in syntax, and also greatly reduces the dependence on SQL language! (For SQL operations of different databases, refer to Comparison of different SQL implementations).

The above is a few simple introduction, if there are errors 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?
Yes, but it is not the default to open, 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 way):

 　　 $conn=mysql_connect(' localhost ', ' user ', ' password ');//connect to MySQL database 　　 mysql_select_db(' Data_base ');//Select Database 　　 　　 $result=mysql_query(' SELECT * from Data_base ');//Here's a second optional parameter that specifies the open connection 　　 $row=Mysql_fetch_row($result) )//for the sake of simplicity, only one row of data is taken 　　 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.

2, Mysqli.dll (object mode):

　　$conn 　　new mysqli (' localhost ', ' user ', ' password ', ' data_base '); 　　 //$result$conn , query (' SELECT * from Data_base ' );  $row$result//echo//　　　　

II, Mysql_fetch_row (), mysql_fetch_array ()

These two functions return an array, the difference is that the first function returns an array containing 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.

The difference between MySQL and mysqli