Use ext/mysqli: Part I - the Review and the prepared expressions
1. A prospective audience
Clause{Article} is intended for the readers having some experience of use PHP and MySQL. She assumes, that the reader understands main principles of job with databases and programming and can use script PHP for sending search to MySQL server.
Pay attention to that at the end of clause{article} there are footnotes for an explanation of some statements and the dictionary of terms.
Instructions on installation PHP and MySQL are beyond given clause{article};
* For reception of the information on installation PHP 5, visit http://www.php.net/installation < =http%3A%2F%2Fwww.php.net%2Finstallation>
* The information on compilation PHP 5 c support ext/mysqli is accessible to the address http://www.php.net/mysqli < =http%3A%2F%2Fwww.php.net%2Fmysqli>
* Behind the information on installation MySQL 4.1.2 or is higher, address on http://www.mysql.com/doc/en/Installing.html < =http%3A%2F%2Fwww.mysql.com%2Fdoc%2Fen%2FInstalling.html>
2. Introduction
Since the middle 90kh, ext/mysql served as the basic bridge between PHP and MySQL. Though in him there were lacks and problems grew in the course of time, in general, ext/mysql did{made} the business not bad and went in a leg{foot} with changes both in PHP, and in MySQL.
However with occurrence PHP 5 and MySQL 4.1 all has changed - extensive cracks have started to be formed a little bit enough.
In ext/mysql there were " the advantages which have appeared lacks ": first of all it mysql_pconnect () [1], connection by default and automatic connection [2]. Besides were showed incompatibility between functions ext/mysql and that were supported by client library MySQL on which are based both ext/mysql, and ext/mysqli.
In attempt to correct these divergences, George Richter has created next expansion PHP 5 which supports new opportunities MySQL 4.1 +. This expansion has received the name ext/mysqli where 'i' replaces one of words: improved (improved), interface (interface), ingenious (inventive), incompatible (incompatible) or incomplete (incomplete). [3]
2.1 The basic purposes
Some from the basic purposes of creation of new expansion were:
* Simplicity of use. The code ext/mysql became very complex{difficult} and chaotic. Significant upgrade of functionality MySQL has demanded an opportunity of connection and disconnect of those or other parts depending on the version of client library. Other problems demanded change of functionality depending on operational system.
* The best compatibility. Expansion should is accurater to use client library MySQL that the future improvements of library were easier supported in PHP.
* Return compatibility. Though compatibility between ext/mysql and ext/mysqli is not ideal, significant efforts for simplification portirovanija applications with ext/mysql on ext/mysqli have been enclosed.
2.2 The basic opportunities
Ext/mysqli Supports the new opportunities which have appeared in last versions MySQL, and offers new functions.
The basic opportunities of expansion:
* The procedural interface very similar to the interface ext/mysql.
* The object-oriented interface which allows to use style, more simple and expanded, rather than the procedural interface.
* Support of the new binary report MySQL entered into versions 4.1. (the New report is more effective, than old, and supports wider set of the opportunities, for example prepared expressions).
* Support of a full set of opportunities of client library MySQL C, including installation of complex{difficult} parameters of connection with the help mysqli_init () and other functions. Besides expansion has support of additional functions of monitoring, catching of mistakes, managements of loading and replikacii.
2.3 What for to pass?
Except for reception of access to additional funkcionalu MySQL 4.1 +, what for it is necessary to pass to use ext/mysqli?
In addition to mentioned funkcionalu, ext/mysqli has some essential advantages:
* Appreciablly big speed. Improvements, both in expansion, and in MySQL, have sped up the majority of operations, sometimes reaching{achieving} 40-fold increase in productivity in comparison with ext/mysql.
* Amplified{Strengthened} safety. In early versions MySQL RDBMS (see. The dictionary of terms at the end of clause{article} - a comment of the translator), there was an opportunity to catch khehsh the weak password in a network and then to recreate the password of the user. New procedure autentifikacii much more strongly also repeats steady against attacks mechanisms of authorization of such tools as SSH.
2.4 Preventions{Warnings} and unexpectedness
Some aspects ext/mysqli strongly differ from old expansion. With the purpose of correction of the certain defects in design and the behaviour inclined to mistakes, some opportunities have been removed{cleaned}:
* Connection to a database by default. If you obviously are not connected to her, ext/mysqli will not make it for you.
* Connection by default (link). It is necessary to address obviously to connection with the server of a database which you want to use if you work with ext/mysqli through the procedural interface, for example mysqli_query ($link, $query);
3. Show me a code!
Now, when you know, that has changed, we shall start to analyze a code which shows as looks and new expansion works. All independent code resulted in this clause{article}, uses a database "world" which is free-of-charge accessible on a site http://www.mysql.com/documentation/index.html. < =http%3A%2F%2Fwww.mysql.com%2Fdocumentation%2Findex.html.>
3.1 Base use
The simple script which incorporates to MySQL server, sends search to the server with the help of this connection, deduces results of search and then releases{exempts} resulting set of search and closes connection.
<? php
/* Connection to MySQL */server
$link = mysqli_connect (
'localhost',/* the Host to which we are connected */
'user',/* the Login name */
'password',/* the Used password */
'world');/* the Database for searches by default */
if (! $link) {
printf (" It is impossible to be connected to a database. An error code: %sn ", mysqli_connect_error ());
exit;
}
/* We send search to */server
if ($result = mysqli_query ($link, ' SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5 ')) {
print (" very large goroda:n ");
/* Sample of results of search */
while ($row = mysqli_fetch_assoc ($result)) {
printf (" %s (%s) n ", $row ['Name'], $row ['Population']);
}
/* We release{exempt} used memory */
mysqli_free_result ($result);
}
/* We close connection */
mysqli_close ($link);
?>
The resulted script should deduce{remove} something like:
Very large cities:
Mumbai (Bombay) (10500000)
Seoul (9981619)
Sao Paulo (9968485)
Shanghai (9696300)
Jakarta (9604900)
Apparently from a code, ext/mysqli and ext/mysql can be very similar. Unique essential distinction is that procedural style ext/mysqli "is a little more verbose".
Notice, that without check on mistakes the resulted script could give failure in any place and deduce{remove} to the user the vile message on a mistake.
3.2 Use of the object-oriented interface
The object-oriented interface gives method of use a little more laconic and less susceptible to mistakes ext/mysqli. The code resulted below, makes the same actions, as previous, however, there are some key differences to which it is necessary to pay attention:
* We do not need to set obviously the connection used in our commands. The information on connection contains in our objects $mysqli and $result and is accessible by a call of corresponding methods.
* When sample of a resulting data set of search with use fetch_assoc is made (), it is not necessary to set the identifier of a used resulting set obviously. As well as the information on connection, he contains in object $result.
<? php
/* Connection to MySQL */server
$mysqli = new mysqli ('localhost', 'user', 'password', 'world');
if (mysqli_connect_errno ()) {
printf (" Connection to MySQL server is impossible. An error code: %sn ", mysqli_connect_error ());
exit;
}
/* We send search to */server
if ($result = $mysqli-> query (' SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5 ')) {
print (" very large goroda:n ");
/* We choose results of search: */
while ($row = $result-> fetch_assoc ()) {
printf (" %s (%s) n ", $row ['Name'], $row ['Population']);
}
/* We release{exempt} memory */
$result-> close ();
}
/* We close connection */
$mysqli-> close ();
?>
4. The prepared expressions
Now, when we have disassembled elements of use of expansion, we shall consider some new opportunities.
The prepared expressions give developers an opportunity to create searches which are more safe, have more high efficiency and are more convenient in a spelling.
The prepared expressions can be used in two ways: with the set parameters and with the set results.
4.1 With the set parameters
The prepared expressions with the set parameters allow to create patterns of searches and to store{keep} them on MySQL server. When it is necessary to create search, the data filling a pattern, are sent MySQL server where completely generated search and is carried out.
The basic process of creation and use of the prepared expressions with the set parameters is simple.
The pattern of search is created and sent MySQL server. The server of it{him} receives, checks his{its} correctness to be convinced, that he is meaningful, and saves it{him} in the special buffer. Then the server returns the identifier which can be used in further for the reference{manipulation} to the prepared expression.
When it is necessary to create search, the data filling a pattern, are sent MySQL server and completely generated search is carried out.
In this process very important details are made some.
The body of a pattern is sent MySQL server only once. For performance of expression the data necessary for filling of a pattern are sent only.
The most part of job on check and processing of search is made only once instead of doing{making} it each time.
Besides for searches which contain a small amount melon, charges are strongly reduced. For example, if you have search of type:
INSERT INTO City (ID, Name) VALUES (NULL, 'Calgary');
That at performance of search needs to be sent each time only about 16 bytes instead of usual 60 or more bytes. (These approached numbers include charges on all data like the identifier of the prepared expression, lengths of the data of search - for safety of the binary data-, etc., but do not include charges on a line of search.)
The data of search should not pass through functions like mysql_real_escape_string () to be convinced, that there is no threat of attack of "SQL-injection" [4] Instead of it, the client and MySQL server work so that to be convinced, that the sent data are safely processed at their combination with the prepared expression.
The pattern of search looks somehow so:
INSERT INTO City (ID, Name) VALUES (??);
Sign '?' It is possible to use in the majority of places where the symbolical data are used, for example the search can be altered from
SELECT Name FROM City WHERE Name = 'Calgary';
In
SELECT Name FROM City WHERE name =?;
More full example showing all process:
<? php
$mysqli = new mysqli ('localhost', 'user', 'password', 'world');
/* Check of connection */
if (mysqli_connect_errno ()) {
printf (" Connection is impossible: %sn ", mysqli_connect_error ());
exit ();
}
$stmt = $mysqli-> prepare (" INSERT INTO CountryLanguage VALUES (????) ");
$stmt-> bind_param ('sssd', $code, $language, $official, $percent);
$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;
/* Performance of the prepared expression */
$stmt-> execute ();
printf (" %d Row inserted.n ", $stmt-> affected_rows);
/* Closing connection and vyrazhenija*/
$stmt-> close ();
/* To clear table CountryLanguage */
$mysqli-> query (" DELETE FROM CountryLanguage WHERE Language ='Bavarian ' ");
printf (" %d Row deleted.n ", $mysqli-> affected_rows);
/* To close connection */
$mysqli-> close ();
?>
Pay attention to that the first parameter bind_param () is the short line. It is the line of a format used for definition of how the declared parameters should be interpreted.
In case of the above-stated script 'sssd' means, that values of first three parameters $code, $language and $official will be sent as a line, and the fourth parameter $percent will contain values such as double from a floating point.
For each declared variable in bind_param (), there should be a letter in a line of a format which means as the variable will be sent. For example
$stmt-> bind_param (' s', $foo);
$stmt-> bind_param ('si', $foo, $bar);
$stmt-> bind_param ('sid', $foo, $bar, $baz);
The announcement of types provides that expansion mysqli knows how to cipher the data for the greater efficiency.
Definitions of types are very simple: the data in the set variables will be processed as integer, rational numbers (double) or as lines.
Also there is the special type, allowing to send bloby (the big binary objects) portions.
The following table illustrates types and opportunities of use:
The identifier of type Type of a column
i All INT types
d DOUBLE and FLOAT
b BLOB'?
s Other types
4.2 With the set results
The prepared expressions with the declared results allow to adhere variables of a PHP-script to values of fields of the data in resulting set of search.
Process of the announcement is those:
* To create search.
* To ask MySQL server to prepare search.
* To adhere variables PHP to stolbcam in preparation of search.
* To force MySQL server to execute search.
* To request addition of new lines of the data in the adhered variables.
Simple fragment of a code, illjustrirujuhhij process:
<? php
$mysqli = new mysqli ("localhost", "user", "password", "world");
if (mysqli_connect_errno ()) {
printf (" Connection is impossible: %sn ", mysqli_connect_error ());
exit ();
}
/* The prepared expression */
if ($stmt = $mysqli-> prepare (" SELECT Code, Name FROM Country ORDER BY Name LIMIT 5 ")) {
$stmt-> execute ();
/* Attachment of variables to preparation */
$stmt-> bind_result ($col1, $col2);
/* Sample of values */
while ($stmt-> fetch ()) {
printf (" %s %sn ", $col1, $col2);
}
/* Closing the operator $stmt-> close ();
}
/* Closing connection */
$mysqli-> close ();
?>
4.3 Use of the set parameters and results together
More full example showing use both set parameters, and the set results simultaneously:
<? php
$mysqli = new mysqli ("localhost", "user", "password", "world");
if (mysqli_connect_errno ()) {
printf (" Connection is impossible: %sn ", mysqli_connect_error ());
exit ();
}
/* The prepared expression */
if ($stmt = $mysqli-> prepare (" SELECT Code, Name FROM Country WHERE Code LIKE? LIMIT 5 ")) {
$stmt-> bind_param ("s", $code);
$code = " C % ";
$stmt-> execute ();
/* The announcement of variables for prepared vyrazhenija*/
$stmt-> bind_result ($col1, $col2);
/* Sample of values */
while ($stmt-> fetch ()) {
printf (" %s %sn ", $col1, $col2);
}
/* Closing expression */
$stmt-> close ();
}
/* Closing connection */
$mysqli-> close ();
?>
5. The resume
In this clause{article} we have resulted the review of opportunities and architecture ext/mysqli, and also a summary of a history of his{its} development. By this moment you should understand how to use and receive benefit from prepared expressions MySQL and should feel convenience of use of the object-oriented interface to ext/mysqli.
6. The dictionary of terms
ext/mysql - old expansion PHP for job with MySQL. does not support all opportunities MySQL of versions 4.1 and is higher.
ext/mysqli - new expansion PHP 5 for job with MySQL. Supports opportunities MySQL of versions from 3.22 up to 5.0
Client library MySQL - Component MySQL RDBMS (MySQL Relational DataBase Management System - the Control system of relational database MySQL - a comment of the translator) which allows programs to communicate with RDBMS.
MySQL server - Component MySQL RDBMS which processes and responds on searches, operates file data presentation inside base, etc.
[1] - Function mysql_pconnect () has been created for granting the mechanism of reduction of expenses for an establishment and break of connections with MySQL server. Unfortunately, because of interaction between architecture of Apache server and PHP, the big traffic on a site using pconnect, could pollute quickly MySQL server a plenty of unused connections which prevented active connections to get access to a database.
[2] - Opportunities of automatic connection allowed the certain calls of functions to incorporate to a database (if the correct information on connection was in a configuration file php.ini) automatically. The opportunity of connection by default worked by the following principle. Last open connection with base MySQL becomes used connection if the parameter of connection has not been obviously specified in arguments of function.
[3] Is an expansion still is in a stage of development. While the set of opportunities of a nucleus should be really stable, neither MySQL 4.1, nor PHP 5.0 have no stable releases (clause{article} has appeared up to output{exit} PHP 5.0.0 - a comment of the translator). Also, the additional set of opportunities which not so accurately uses client library MySQL, is still finished.
[4] - Attacks such as "SQL-injection" are possible{probable}, when the data enter into search, forcing it{him} to make unexpected and-or ill-intentioned actions. Let, for an example, the simple search in PHP a script such as " DELETE FROM grades WHERE class_name ='test _ $ class' " is given. Attacking can receive the control over a variable $class and have an opportunity an attacker who can gain control over the value of $class can force unintended deletes to occur by changing the value of $class to something like " oops' or class_name LIKE '%' ".
7. About authors
Zak Grent (Zak Greant) - the professional defender of concept Open Source, the writer and the programmer. He works in MySQL AB as the propagandist of Community. Zak supports both expansions PHP for job with MySQL and is co-author PHP Functions Essential Reference.
George Richter (Georg Richter) - the founder of expansion mysqli. He also supports expansions mysql and ncurses. He works in MySQL AB as the Senior Developer and is member Apache Software Foundation.
| 
