Detailed explanation of the difference between php mysqli and mysql

This article introduces some differences between mysqli and mysql classes in PHP. Friends in need can use them as a reference.

First, PHP-MySQL is the most original Extension for PHP to operate the MySQL database. The i of PHP-MySQLi stands for Improvement, which provides relatively advanced functions. PDO (PHP Data Object) provides an Abstraction Layer to operate the database. Example:

<?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); 
?> 

This method cannot Bind Column. According to the previous SQL description, $location is prone to SQL Injection. Later, mysql_escape_string() was developed (note: deprecated after 5.3.0) and mysql_real_escape_string() solves this problem.

Example:

<?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); 
?> 

There has been a lot of progress in PHP-MySQLi. In addition to solving the above problems through Bind Column, it also supports Transaction, Multi Query, and also provides Object oriented style (the following PHP-MySQLi sample The writing method of the example) and the Procedural style (the writing method of the PHP-MySQL example above) are written in two ways, etc.

Example:

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

Disadvantages, such as Bind Result, which is a bit redundant, but it actually doesn’t matter, because the biggest problem is that this is not an abstract method. So PDO appeared (Note: Currently, for Ubuntu and Debian, PDO does not have a direct package to install, but must be installed through PECL).

Example:

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

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

<?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; 
?> 

Benefits of pdo: 1. When PDO connects to a database, it uses Connection String to determine which database to connect to. 2. PDO can use PDO::setAttribute to determine the connection settings, such as Persistent Connection, and the way to return errors (Exception, E_WARNING, NULL). Even the case of the returned field name...etc. 2. PDO supports the Bind Column function. In addition to the basic Prepare and Execute, you can also Bind a single column and specify the column type. 4. PDO is an Abstraction Layer, so even if you change the storage medium, the effort required is minimal in comparison.

Personally, I prefer to use DBI to connect to databases, such as ActiveRecord and Propel ORM (Object-Relational Mapping). For example, taking ActiveRecord as an example, if you want to implement a SQL statement like this... INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe') How to operate pdo:

<?php 
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES(?, ?, ?, ?)"; 
$sth = $dbh->prepare($sql); 
$sth->execute(array(1, 'roga', 'male', 'tpe')); 
?> 

In terms of ActiveRecord, it is:

<?php 
$user = new User(); 
$user->id = 1; 
$user->name = 'roga'; 
$user->gender = 'male'; 
$user->location = 'tpe'; 
$user->save(); 
?> 

mysql is a non-persistent connection function and mysqli is a permanent connection function. That is to say Mysql will open a connection process for each connection, and running mysqli multiple times will use the same connection process, thereby reducing server overhead. When some friends are programming, they always report when using new mysqli('localhost', usenamer', 'password', 'databasename'); Wrong, Fatal error: Class 'mysqli' not found in d:... Isn’t the mysqli class built into PHP? It is not enabled by default. Under win, you need to change php.ini and remove the ";" in front of php_mysqli.dll. Under Linux, you need to compile mysqli into it.

1: Mysqli.dll allows the database to be operated in an object or process, and its use is also very easy.

Compare several common operations with mysql.dll. 1,

<?php
mysql.dll(可以理解为函数式的方式)： 
$conn = mysql_connect('localhost', 'user', 'password');　//连接mysql数据库 
mysql_select_db('data_base'); //选择数据库 
　　 
$result = mysql_query('select * from data_base');//这里有第二个可选参数，指定打开的连接
$row = mysql_fetch_row( $result ) ) //为了简单，这里只取一行数据 
echo $row[0]; //输出第一个字段的值 

　 Mysqli also has a procedural method, but it starts with the prefix of mysqli, and everything else is almost the same. If mysqli operates in a procedural manner, some functions must specify resources, such as mysqli_query (resource identifier, SQL statement), And the parameter of resource identification is placed in the front, while the resource identification of mysql_query (SQL statement, 'optional') is placed in the back, and does not need to be specified. It defaults to the previous opened connection or resource.

2, mysqli.dll (object mode):

<?php
$conn = new mysqli('localhost', 'user', 'password','data_base'); 
//这里的连接是new出来的，最后一个参数是直接指定数据库，不用mysql_select_db()了 
//也可以构造时候不指定，然后 $conn -> select_db('data_base') 
$result = $conn -> query( 'select * from data_base' ); 
$row = $result -> fetch_row(); //取一行数据 
echo row[0]; //输出第一个字段的值 

Two, mysql_fetch_row(), mysql_fetch_array() Both functions return an array. The difference is that the array returned by the first function only contains values, which can only be $row[0], $row[1]. In this way, the data can be read using the array subscript. The array returned by mysql_fetch_array() contains both the first and key-value pairs. The data can be read like this (if the database fields are username, passwd): $row['username'], $row['passwd'] Moreover, if you use ($row as $kay => $value) to operate, you can also directly obtain the field name of the database. More importantly, mysqli is a new function library provided by php5. (i) represents an improvement and its execution speed is faster.

The above is the difference between mysql and mysqli in PHP programming. I hope it will be helpful to everyone.