Differences and connections between the three main APIs commonly used in link-MySQL database servers in php: mysql, mysqli, pdo

Differences and connections between the three main APIs when php connects to the mysql database server: mysql, mysqli, and pdo
are also commonly used extensions. Which of them has better performance?

In fact, they are all good, but in comparison some are better (I like pdo)

/**
After reading a lot of related database connections and operations, here is a summary of database connections in
php
--------------------- I hope it will be helpful

*/




//1 Connection type
/**

Differences and connections between the three main APIs commonly used in link-MySQL database servers in php: mysql, mysqli, pdo

/********Basic related information*********/
 a.API---------- ------Application Programming Interface
Application Programming Interface (abbreviation of Application Programming Interface) defines classes, methods, functions,
variables, and so on, everything that needs to be called in your application to complete specific tasks. When a PHP application needs to interact with a database, the API required is usually exposed through a PHP extension (called by the terminal PHP programmer).

API can be process-oriented or object-oriented. For procedure-oriented APIs, we complete tasks by calling functions, while for object-oriented APIs, we instantiate classes and call methods on the objects obtained after instantiation.
Of the two interfaces, the latter is usually preferred as it is more modern and gives us a good
code structure.


 b.connector ----- "A piece of software code that allows your application to connect to the MySQL database server".
 When your PHP application needs to interact with a database server, you need to write PHP code to complete a series of activities such as "connecting to the data
 library server", "querying the database" and other database-related functions. Your PHP application will use software that provides these APIs, or use some intermediate libraries when needed, to handle the interaction between your application and the database server.

 c. Driver
 Software code used to interact with a specific type of database server. The driver may call some libraries,
 such as MySQL client library or MySQL Native driver library. These libraries implement the low-level protocols for interacting with the MySQL database server.

 Maybe people use the terms connector and driver without distinction.
 The term "driver" in MySQL related documentation is used as a connector package
 that provides software code for a specific database part.

 d. What are extensions?

 You will also find many other extensions in the PHP documentation. PHP code is composed of a core and some optional extensions that make up the core functionality.
 MySQL-related extensions of PHP, such as mysqli and mysql, are all implemented based on the PHP extension framework.
 A typical function of extension is to expose an API to PHP programmers, allowing extended functions to be used by programmers.
 Of course, there are also some extensions developed based on the PHP extension framework that do not expose API interfaces to PHP programmers.

For example, the PDO MySQL driver extension does not expose the API interface to PHP programmers, but provides an interface to the PDO layer above it.

The terms API and extension do not describe the same kind of thing, because extensions may not need to expose an API interface to programmers.


/*********Key points*******/
Main API for MySQL provided in PHP:

■MySQL extension for PHP
 (pros and cons)

 [

 Designed and developed to allow PHP applications to interact with MySQL databases Early expansion. The mysql extension provides a procedure-oriented interface,
 and is designed for MySQL4.1.3 or earlier. Therefore, although this extension can interact with MySQL 4.1.3 or newer database servers, it does not support some features provided by later MySQL servers.

】


■Mysqli extension for PHP
 The mysqli extension, which we sometimes call the MySQL enhancement extension, can be used to use the new advanced features in MySQL 4.1.3 or newer versions.
 The mysqli extension is included in PHP 5 and later versions.

 The mysqli extension has a series of advantages. Compared with the mysql extension, the main improvements are:

 ■Object-oriented interface

 ■Prepared statement support (Annotation: Please refer to mysql related documents for prepare)

 ■Multiple statement execution support

 ■Transaction support

 ■Enhanced debugging capabilities

 ■Embedded service support

■PHP Data Object (PDO)

PHP Data Object is a database abstraction layer specification in PHP applications. PDO provides a unified API interface that allows your PHP application to not care about the specific database server system type to be connected. In other words,
If you use PDO's API, you can seamlessly switch database servers whenever needed


/*******Compared***********/

PHP-MySQL is the most original Extension for PHP to operate the MySQL database. The i in PHP-MySQLi stands for Improvement, which provides relatively advanced functions. As far as the Extension is concerned, it also increases security.
And PDO (PHP Data Object) provides an Abstraction Layer to operate the database


Detailed source reference: http://www.jb51.net/article/28103.htm

1.mysql and mysqli
mysqli It is a new function library provided by php5. (i) means improvement, and its execution speed is faster. Of course, it is also safer. Detailed source reference: http://www.jb51.net/article/28103.htm

mysql is 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 mysqli will use the same connection process when running mysqli multiple times,
thus reducing the 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 ";" before 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 are a few common
 detailed sources for reference: http://www.jb51.net/article/28103.htm

 Do more and talk less:

*/
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);


/**
In fact, there is some knowledge behind it...
This method cannot Bind Column, speaking from 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, if this is done, the entire narrative will become complicated and ugly, and if there are too many columns, You can imagine what the situation will be like...

Detailed source reference: http://www.jb51.net/article/28103.htm

*/




$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 and Multi Query.
It also provides Object oriented style (the following PHP-MySQLi example is written) and Procedural style
 (the way of writing the PHP-MySQL example above) are two ways of writing...and so on.

Detailed source reference: http://www.jb51.net/article/28103.htm
*/



$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. For example, Bind Result is a bit redundant, but it doesn’t really matter.
Because the biggest problem is that this is not an abstraction method, so when the backend When changing the database, the pain begins...
So PDO appeared
Detailed source reference: http://www.jb51.net/article/28103.htm

*/


// 2.PDO and mysql
/*
PDO is PHP5. Supported only after 1, it uses a consistent interface for accessing the database. However, many domestic open source programs use the functions provided by MySQL extension to connect to the database and perform queries. PDO is so powerful, why don’t mature domestic PHP systems use it?
I asked a few friends why they use PDO, and the answer is "fast". Will PDO connect to the database faster? Why use PDO?

What are the differences between the two methods? First of all, I am more concerned about performance issues. I wrote a script to test inserting 1 million pieces of data into MySQL.
*/

$link = mysql_connect("localhost", "root", "root") or die('mysql connect error');
$num = 100000;
$dsn = "mysql:host=127.0.0.1 ;dbname=performace_test";
$db = new PDO($dsn, 'root', 'root', array(PDO::ATTR_PERSISTENT => true));
mysql_query('TRUNCATE TABLE `performace_test`.`myquery` ',$link); //Truncate Table
$query = "INSERT INTO `performace_test`.`myquery`(`goods_id`,`cat_id`,`click_count`,`goods_number`,`goods_weight`,`goods_sn`,` goods_name`,`goods_reason`,`brand_name`,`goods_thumb`,`brand_id`,`is_on_sale`,`wap_cod`,`wap_title`,`wap_detail`,`wap_flag`,`wap_onsale`,`shop_price`,`cost_price` ,`channel_rate`,`channel_onsale`,`add_time`,`is_main`,`last_update`,`brand_logo`) VALUES ( '80′,'298′,'65′,'100′,'0.125′,'SMT000080′ ,'Health',",'Health120','images/201004/thumb_img/80_thumb_G_1272071721054.jpg','1′,'0′,'0′,NULL,NULL,NULL,'0′,'2980.00′, '0.00′,'1.250000′,'1′,'1271612064′,'0′,'1297624384′,'1293649512083026412.jpg')";
$start_time = microtime(true);
for($i=0;$ i<$num;$i++)
{
mysql_query($query,$link);
}
echo "USE MySQL extension: ". (microtime(true)-$start_time);
mysql_query('TRUNCATE TABLE `performace_test` .`myquery`',$link); //Truncate Table
$start_time = microtime(true);
for($i=0;$i<$num;$i++)
{
$db->exec( $query);
}
echo "rnUSE PDO: ". (microtime(true)-$start_time);

/**
USE MySQL extension: 95.233189106s

USE PDO: 99.1193888187s

Almost on linked MySQL no difference. The performance penalty of PDO is completely negligible.

But there are a lot of operations that the MySQL extension library does not have:

1: PDO is truly a unified interface database operation interface implemented at the bottom layer
2: PDO supports more advanced DB feature operations, such as: Scheduling of stored procedures, etc., is not supported by the mysql native library.
3: PDO is PHP’s official PECL library, and its compatibility and stability must be higher than MySQL Extension. You can directly use the pecl upgrade pdo command to upgrade

PHP6 is also used by default PDO performs database linking, and MySQL Extension will assist.

So in our daily projects, if the environment permits, we try to use PDO to perform MySQL database operations as much as possible.





?>