Analysis on the differences between mysql and mysqli in php

I:
PHP-MySQL is the most primitive Extension for PHP to operate MySQL databases. I of PHP-MySQLi stands for Improvement and provides more advanced functions. Extension also increases security. The PDO (PHP Data Object) provides an action Layer to operate the database. The difference is not obvious, so let's look at the program directly...
First, let's look at a piece of code written in PHP-MySQL. Such examples are often 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 '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 actually some knowledge behind it...
This method cannot Bind Column. In the previous SQL statement, $ location is prone to SQL Injection. Later, I developed mysql_escape_string () (Remarks: abandon after 5.3.0) and mysql_real_escape_string () to solve this problem. However, in this case, the entire description will become complex and ugly, and if there are too many columns, you can see what the situation will be...
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 );
?>

PHP-MySQLi has made a lot of progress. In addition to solving the above problems through Bind Column, it also provides multiple support for Transaction, Multi Query, the Object oriented style and Procedural style are provided at the same time... And so on.
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 ('dss', $ 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 ();
?>

However, some shortcomings have been found here, such as the Bind Result. This is a bit redundant, but it does not matter, because the biggest problem is that this is not an abstract action) so when the back-end database is changed, it is a painful start...
As a result, PDO appears (NOTE: For Ubuntu and Debian, PDO does not have a direct suite to install, but must be installed through PECL ).

Copy codeThe Code is as follows:
Roga @ carlisten-lx :~ $ 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

After the installation is completed through PECL, 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' =? ";
$ Something = $ dbh-> prepare ($ SQL );
$ Something-> execute (array ($ location, $ name ));
$ Result = $ something-> fetch (PDO: FETCH_OBJ );
Echo $ result-> name. $ result-> location;
$ Dbh = NULL;
?>

At first glance, the PDO Code does not seem to be too short. What are the benefits?
1. When the PDO connects to the database, the Connection String is used to determine the database to be connected.
2. PDO can determine the Connection settings through PDO: setAttribute, such as Persistent Connection, and Return Error Methods (Exception, E_WARNING, NULL ). Even the case of the return field name... And so on.
2. PDO supports the Bind Column function. In addition to the basic Prepare and Execute functions, PDO can also specify a single Column of Bind and a Column type.
4. PDO is a physical action Layer. Therefore, it takes the least effort to replace the storage media.
Unfortunately, render manager has been around for a long time, but it is still not popular enough. I think it may be because you are used to reading books from other places, but those books often only introduce the simplest and most traditional methods. As a result, many people are still using MySQL databases directly.
However, at present, I personally prefer to connect to databases through DBI, such as ActiveRecord and Propel ORM (Object-Relational Mapping ).
For example, taking ActiveRecord as an example, if you want to implement such an SQL statement...
Insert into 'users' (id, name, gender, location) VALUES (1, 'roga ', 'male', 'tpe ')
Written as PDO:
Copy codeThe Code is as follows:
<? Php
$ SQL = "INSERT INTO 'users' (id, name, gender, location) VALUES (?, ?, ?, ?) ";
$ Something = $ dbh-> prepare ($ SQL );
$ Something-> execute (array (1, 'roga ', 'male', 'tpe '));
?>

But for 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 ();
?>

Is the latter much more concise in syntax, and it also greatly reduces the dependency on the SQL language! (For more information about SQL implementation in different databases, see Comparison of different SQL implementations)
The above are some simple introductions. You are welcome to add any omissions.



Mysql is a non-persistent connection function, while mysqli is a permanent connection function. That is to say
Mysql opens a connection process at each link, and mysqli runs mysqli multiple times to use the same connection process, thus reducing the server overhead.
Some friends always report new mysqli ('localhost', usenamer ', 'Password', 'databasename') during programming.
Error, Fatal error: Class 'mysqli' not found in d :\...
Isn't the mysqli class included in php?
It is not enabled by default. In win, you need to change php. ini and remove the ones before php_mysqli.dll. in linux, You need to compile mysqli.
I. Mysqli. dll is an object-based or process-based database that is easy to use. Here we will compare several common operations with mysql. dll.
1: mysql. dll (which can be understood as a functional method ):
Copy codeThe Code is as follows:
$ Conn = mysql_connect ('localhost', 'user', 'Password'); // connect to the mysql database
Mysql_select_db ('data _ base'); // select a database
　　
$ Result = mysql_query ('select * from data_base '); // there is a second optional parameter, specifying the opened connection.
$ Row = mysql_fetch_row ($ result) // For simplicity, only one row of data is taken.
Echo $ row [0]; // output the value of the first field

Mysqli also has a program method, but starts to use the mysqli prefix, and the others are similar. If mysqli operates in a procedural manner, some functions must specify resources, for example, mysqli_query (resource ID, SQL statement), and the parameters of the resource ID are placed in the front, the resource ID of mysql_query (SQL statement, 'option') is placed behind and can be left unspecified. It is the last opened connection or resource by default.
2mysqli. dll (Object method ):
Copy codeThe Code is as follows:
$ Conn = new mysqli ('localhost', 'user', 'Password', 'Data _ base ');
// The connection here is new. The last parameter is to specify the database directly without mysql_select_db ().
// You can also choose not to specify this parameter during construction, and then $ conn-> select_db ('data _ base ')
$ Result = $ conn-> query ('select * from data_base ');
$ Row = $ result-> fetch_row (); // retrieves a row of data
Echo row [0]; // output the value of the first field

Ii. mysql_fetch_row (), mysql_fetch_array ()
The two functions return an array. The difference is that the array returned by the first function only contains values. We can only $ row [0].
$ Row [1], which reads data by the array subscript. The array returned by mysql_fetch_array () contains both the first and key values.
In the correct format, we can read data like this (if the database field is username, passwd ):
$ Row ['username'], $ row ['passwd']
In addition, if you use ($ row as $ kay => $ value) for operations, you can directly obtain the database field name.
What's more, mysqli is a new function library provided by php5. (I) indicates improvement and its execution speed is faster.