The difference between mysql and mysqli in PHP

One:
PHP-MySQL is the original Extension for PHP to operate the MySQL database. The i of PHP-MySQLi represents Improvement, which provides relatively advanced functions. As far as the Extension is concerned, it also increases security. PDO (PHP Data Object) provides an Abstraction Layer to operate the database. In fact, you can’t tell the difference in practical terms, so just look at the program...
First, let’s look at a paragraph written in PHP-MySQL Program code, such examples are commonly used around the world:
The code is as follows:

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

There is nothing wrong at first glance, but there is actually some knowledge behind it...
This method cannot Bind Column. In terms of the previous SQL description, $location is prone to SQL Injection. Later, mysql_escape_string() (note: deprecated after 5.3.0) and mysql_real_escape_string() were developed to solve this problem. However, the entire narrative will become complicated and ugly, and if there are too many fields, you can Imagine what it would be like...
The code is as follows:

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
mysql_real_escape_string($user ) ,
mysql_real_escape_string($password));
mysql_query($query);
?>

has made 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 two writing methods: Object oriented style (the writing method of the PHP-MySQLi example below) and Procedural style (the writing method of the PHP-MySQL example above)... and so on.
The code is as follows:

$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 I found some shortcomings here, such as Bind Result, this is a bit redundant, but it actually doesn't matter, because the biggest problem is that it is not an abstraction method, so when the backend changes the database, the pain begins...
So PDO It appears (Note: Currently, for Ubuntu and Debian, PDO does not have a direct package to install, but must be installed through PECL).

The 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

Once installed through PECL, you can operate the database in the following ways:
The code is as follows:

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

zar It seems that PDO's program code is not shorter, so what are the benefits?
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.
Unfortunately, even though these things have been around for a long time, they are still not popular enough. I think it may be because people are accustomed to reading books on the market, but those books often only introduce the simplest and most traditional methods. As a result, many people still use MySQL to directly connect to the database.
However, at present, I personally still like to connect to the database through DBI, such as ActiveRecord and Propel ORM (Object-Relational Mapping).
For example, taking ActiveRecord as an example, if you want to implement such a SQL statement...
INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe')
Use PDO To write:
The code is as follows:

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

But in terms of ActiveRecord, then Yes:
The code is as follows:

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

Isn't the latter much simpler in syntax, and it also greatly reduces the need for SQL Language dependency! (For questions about SQL implementations in different databases, please refer to Comparison of different SQL implementations) The above are some simple introductions. If there are any omissions and fallacies, you are welcome to add.



mysql is a non-persistent connection function and mysqli is a permanent connection function. In other words,
mysql will open a connection process every time it is connected, and running mysqli multiple times will use the same connection process, thereby reducing server overhead.
Some friends use new mysqli('localhost', usenamer' when programming , 'password', 'databasename'); always reports an error, Fatal error: Class 'mysqli' not found in d:... Isn't the mysqli class included in 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. Here is a comparison between several common operations and mysql.dll.
　　1: mysql.dll (can be understood as a functional way):
The code is as follows:

　$conn = mysql_connect('localhost', 'user', 'password'); //Connect to the mysql database
　mysql_select_db('data_base '); //Select the database
　
　$result = mysql_query('select * from data_base');//There is a second optional parameter here to specify the open connection
　$row = mysql_fetch_row( $result ) ) //For Simple, here we only take one row of data
　 Echo $row[0]; // Output the value of the first field

Mysqli also has a procedural method, but it just starts with the mysqli prefix, 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 the resource identifier is placed in front, while the parameter of mysql_query (SQL statement, 'optional') The resource identifier is placed at the end and does not need to be specified. It defaults to the last opened connection or resource.
　　2mysqli.dll (object mode):
The code is as follows:

　$conn = new mysqli('localhost', 'user', 'password','data_base');
　 //The connection here is new, and finally One parameter is to directly specify the database, no need for mysql_select_db()
　　 //You can also not specify it when constructing, then $conn -> select_db('data_base')
　 $result = $conn -> query( 'select * from data_base ' );
　$row = $result -> fetch_row(); //Get a row of data
　echo row[0]; //Output the value of the first field

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