Some knowledge points about the differences between MySQL and MySQL in PHP

Brief Introduction: This is a detailed page on the differences between MySQL and mysqli in PHP. It introduces PHP, related knowledge, skills, experience, and some PHP source code.

Class = 'pingjiaf' frameborder = '0' src = 'HTTP: // biancheng.dnbc?info/pingjia.php? Id = 346697 'rolling = 'no'>

I have never understood the difference between MySQL and MySQL when reading books or watching videos. So let's make a look at Google tonight.

I:

Php-MySQL is the most primitive extension for PHP to operate mysql. I of PHP-mysqli represents improvement, which improves the functions of extension, security is also increased. The PDO (PHP Data Object) provides an action layer to perform operations. However, the difference does not exist, so let's look at the program directly...

First, let's take a look at a PHP-mysql program. Examples of this program are commonly used around the world:

View Source

   <?

　　 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 something behind it...

�� Method cannot bind column. In the previous example, SQL statements are described. $ location is prone to SQL injection. Subsequently, mysql_escape_string () (later than 5.3.0) and mysql_real_escape_string () are displayed, the whole story is ugly, and if there are too many places, you can think about what the situation is...

1

<?php

2

3	$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",

4	mysql_real_escape_string($user),

5	mysql_real_escape_string($password));

6

7	mysql_query($query);

8

9

?>

There are many steps in PHP-mysqli. Apart from the transparent Bind Column, the above steps are also supported by transaction, multi query, and provides the object oriented style (the PHP-mysqli method in the following section) and procedural style (the method in the above PHP-mysql example) method... And so on.

01

<?php

02

03	$mysqli = new mysqli($db_host, $db_user, $db_password, $db_name);

04

05	$sql = "INSERT INTO `users` (id, name, gender, location) VALUES (?, ?, ?, ?)";

06	$stmt = $mysqli->prepare($sql);

07

08	$stmt->bind_param('dsss', $source_id, $source_name, $source_gender, $source_location);

09

10	$stmt->execute();

11

12	$stmt->bind_result($id, $name, $gender, $location);

13

14	while ($stmt->fetch())

15

{

16	echo $id . $name . $gender . $location;

17

}

18

19	$stmt->close();

20	$mysqli->close();

21

22

?>

However, when we see some missing items, such as bind result, there will be many items, because the largest is not an abstract action method, it is the beginning of pain...

So PDO is coming out (��: currently Ubuntu and Debian, PDO has a direct suite that can be installed, but it must be passed through PECL ).

roga@carlisten-lx:~$ pecl search pdo=======================================Package      Stable/(Latest) LocalPDO          1.0.3 (stable)        PHP Data Objects Interface.PDO_4D       0.3 (beta)            PDO driver for 4D-SQL databasePDO_DBLIB    1.0 (stable)          FreeTDS/Sybase/MSSQL driver for PDOPDO_FIREBIRD 0.2 (beta)            Firebird/InterBase 6 driver for PDOPDO_IBM      1.3.2 (stable)        PDO driver for IBM databasesPDO_INFORMIX 1.2.6 (stable)        PDO driver for IBM Informix INFORMIX databasesPDO_MYSQL    1.0.2 (stable)        MySQL driver for PDOPDO_OCI      1.0 (stable)          Oracle Call Interface driver for PDOPDO_ODBC     1.0.1 (stable)        ODBC v3 Interface driver for PDOPDO_PGSQL    1.0.2 (stable)        PostgreSQL driver for PDOPDO_SQLITE   1.0.1 (stable)        SQLite v3 Interface driver for PDOpdo_user     0.3.0 (beta)          Userspace driver for PDO

When PECL is installed, you can operate the raw material in the following ways:

01

<?php

02

03	$dsn = "mysql:host=$db_host;dbname=$db_name";

04	$dbh = new PDO($dsn, $db_user, $db_password);

05

06	$sql = "SELECT `name`, `location` FROM `users` WHERE `location` = ? , `name` = ?";

07	$sth = $dbh->prepare($sql);

08

09	$sth->execute(array($location, $name));

10

11	$result = $sth->fetch(PDO::FETCH_OBJ);

12	echo $result->name . $result->location;

13

14	$dbh = NULL;

15

16

?>

At first glance, the PDO program seems to be relatively short. What is better?

1. PDO depends on the connection string.
2. PDO can be set through PDO: setattribute, such as persistent connection, return method (exception, e_warning, null ). Even the size of the callback bit... And so on.
2. PDO supports the Bind Column function. In addition to basic prepare and execute, you can also bind a bit and specify a bit type.
4. PDO is an action layer, so it takes the least effort to store media.

It is a pity that some of them have been around for a long time, but they are not big. I think it may be because of the reading of the square, but these are often the only best practices. Many people are using MySQL directly.

No. Currently, we are the most popular passthrough DBI connectors, such as activerecord and propel ORM (object-relational mapping ).

For example, in activerecord format...

INSERT INTO `users` (id, name, gender, location) VALUES(1, 'roga', 'male', 'tpe')

Take PDO as follows:

1

<?php

2

3	$sql = "INSERT INTO `users` (id, name, gender, location) VALUES(?, ?, ?, ?)";

4	$sth = $dbh->prepare($sql);

5

6	$sth->execute(array(1, 'roga', 'male', 'tpe'));

7

8

?>

However, the format of activerecord is:

01

<?php

02

03	$user = new User();

04

05	$user->id = 1;

06	$user->name = 'roga';

07	$user->gender = 'male';

08	$user->location = 'tpe';

09

10	$user->save();

11

12

?>

Does the latter greatly reduce SQL statement compliance! (Comparison of different SQL implementations can be used for different SQL statements)

The above are some introductions. If you have any omissions, you will be welcomed.

Address: http://blog.roga.tw/2010/06/%E6%B7%BA%E8% AB %87-php-mysql-php-mysqli-pdo-%E7%9A%84%E5%B7% AE %E7%95%B0/

II:

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 ):

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

$ 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.

Address: http://www.csharpwin.com/dotnetspace/7661r9994.shtml

Love J2EE follow Java Michael Jackson video station JSON online tools

Http://biancheng.dnbcw.info/php/346697.html pageno: 4.