MySQLi puts everything on the shelves

Who is the article for? The primary purpose of writing the article was to “sort everything out” for those who had already worked with mysqli, but did not penetrate deeply, but quickly wrote their wrappers and forgot about the original syntax. I tried to clarify the nuances I encountered myself when transferring data from a large and very old database, designed by a person who does not know about normalization, to a new one, with a greatly changed structure.

Is it possible to read this article to people who are still using the old mysql extension and only thinking about switching to PDO or MySqli? I think even necessary.

MySqli or PDO

In recent years, I have written websites exclusively on frameworks, which relieved me from working directly with the database. Some time ago I started working on a site on pure php and wondered what to use instead of the outdated and unrecommended old PHP MySQL extension.
')
It was necessary to choose between MySqli and PDO. After not very long study, I decided to stop at MySqli, since, as it seemed to me then, it is completely identical to PDO, except that it is not possible to abandon MySQL in favor of something else. As I write below, this is not quite true, at least one noticeable difference is.

MySqli is recommended for use by PHP developers themselves. [ 1 ]

OOP and procedural interface

MySqli allows you to write code in both OOP style and procedural. The PLO is closer to me like most of the community, so this article will use it.

Three main classes

MySqli has 3 main classes, which will be discussed in detail in this article.

1. mysqli - is required to establish a connection with the database and will be useful if we want to execute queries in the way we did in the old MySQL extension;
2. mysqli_stmt - is required to use the new MySqli feature: execute queries on prepared expressions;
3. mysqli_result - combines functions to get the results of queries made using mysqli or mysqli_stmt.

Consider each of them in more detail below.

DB connection

There are two ways.

Method one. If you just need to create a connection.

$ mysqli = new mysqli ( 'localhost' , 'my_user' , 'my_password' , 'my_db' ) ;
if ( $ mysqli -> connect_error ) {
die ( 'Connect Error (' . $ mysqli -> connect_errno . ')' . $ mysqli -> connect_error ) ;
}

The second way. If you need to use connection options.

$ mysqli = mysqli_init ( ) ;
if ( ! $ mysqli ) {
die ( 'mysqli_init failed' ) ;
}

if ( ! $ mysqli -> options ( MYSQLI_INIT_COMMAND , 'SET AUTOCOMMIT = 0' ) ) {
die ( 'Setting MYSQLI_INIT_COMMAND failed' ) ;
}

if ( ! $ mysqli -> real_connect ( 'localhost' , 'my_user' , 'my_password' , 'my_db' ) ) {
die ( 'Connect Error (' . $ mysqli -> connect_errno . ')' . $ mysqli -> connect_error ) ;
}

Using $ mysqli-> connect_errno and $ mysqli-> connect_error, we get the description and error code that occurred during the connection. Both new mysqli () and $ mysqli-> real_connect () with connection errors cause a PHP Warning error. Therefore, displaying errors using the above-mentioned functions makes sense if you have turned off the display of PHP errors, for example, on the production server, or if you need to somehow handle this data. I mentioned this here, because not all MySQLi functions call PHP Warning in case of an error, and in order to know that an error has occurred, it is necessary to refer to special functions, see below.

Obtained by connecting the object we assigned to the variable $ mysqli , in order to use it in the future. This is obvious for OOP style, but for the procedural style this object is also necessary, in contrast to the outdated MySQL extension, where the link to the connection was not necessary to be passed each time the mysql functions were used.

Buffered and Unbuffered Results

Before telling further, I would like to explain the difference between these two types of results.

Consider an unbuffered result. In this case, you can start reading the results without waiting for the mysql server to receive the result completely.

Benefits:

• The result can be started to read earlier, the waiting time is reduced;
• The result does not take place in the RAM.

Disadvantages:

• It is impossible to know how many rows are received;
• It is impossible to move to a specific result, that is, you can only read data from the beginning and in order;
• No other queries can be performed until this result is closed.

Buffered result is devoid of these disadvantages and, accordingly, is deprived of the listed advantages.

"Classic" requests

MySqli left the possibility of "classic" queries: when the user is asked to independently engage in the security of transmitted queries as it was in the outdated MySQL extension. For this purpose, it is proposed to use the $ mysqli-> real_escape_string () function, with which it is necessary to process all data before putting it into the request.

As with a connection, there are two ways to make such a request short and long.

Short.

$ result = $ mysqli -> query ( 'query text' , MYSQLI_USE_RESULT ) ;

Possible constants:
MYSQLI_STORE_RESULT - returns buffered result, default value
MYSQLI_USE_RESULT - Unbuffered

A long.

$ mysqli -> real_query ( 'query text' ) ;
echo ( $ mysqli -> field_count ) ; // returns the number of columns, as a result
// can be obtained before the start of the result, which gives additional flexibility
// compared to the short way, of course, it is not necessary to call
$ result = $ mysqli -> use_result ( ) ; // returns unbuffered result
// or
$ result = $ mysqli -> store_result ( ) ; // returns the buffered result

The functions $ mysqli-> use_result () or $ mysqli-> store_result () are also used for multi-requests (requests consisting of several requests). Multi requests in this article will not be considered.

Both syntaxes return the result as a mysqli_result object, which is a convenient interface for working with the result, both with buffered and not with unbuffered.

As I wrote above, not all MySQLi functions throw PHP errors, the functions described above from among them. If the request is incorrect and the server returns an error, PHP will not let you know about it. To test use functions:

• $ mysqli-> error - error description
• $ mysqli-> errno - error code

$ city = $ mysqli -> real_escape_string ( $ city ) ;
$ mysqli -> query ( "SELECT * FROM` city` WHERE `id` = ' $ city '" ) ;
if ( $ mysqli -> errno ) {
die ( 'Select Error (' . $ mysqli -> errno . ')' . $ mysqli -> error ) ;
}

Advantages of the “classic” query syntax:

1. It is much more compact than prepared expressions (class mysqli_stmt );
2. It allows you to get an unbuffered result in the form of mysqli_result . The prepared expressions allow you to get only the buffered result of mysqli_result , and you will have to work with unbuffered means using mysqli_stmt , which is much less flexible than mysqli_result .

Practical application of the “classical” query syntax I see:

1. In non-buffered queries, if you want to take advantage of working with mysqli_result ;
2. In INSERT, UPDATE, REPLACE or DELETE queries, if you prefer their shorter syntax.

For such queries properties will be useful:

• $ mysqli-> affected_rows - the number of affected lines by the previous query is not per sample
• $ mysqli-> insert_id - automatically generated ID for the last insert request.

Prepared requests

The advantages of prepared requests over the "classical":

• With a series of requests in which only the data is changed, the request is sent to the server only once, then only the changing data is sent;
• Protection against SQL injection.

The class mysqli_stmt is responsible for working with prepared queries in MySQLi.

Two ways to create a prepared query.

// first way - using the mysqli object
$ mysqli -> prepare ( "SELECT * FROM` sk2_articles` WHERE `id` =?" ) ;
if ( $ mysqli -> errno ) {
die ( 'Select Error (' . $ mysqli -> errno . ')' . $ mysqli -> error ) ;
}

// second way - using the mysqli_stmt object
$ stmt = $ mysqli -> stmt_init ( ) ;
$ stmt -> prepare ( "SELECT * FROM` sk2_articles` WHERE `id` =?" ) ;
if ( $ stmt -> errno ) {
die ( 'Select Error (' . $ stmt -> errno . ')' . $ stmt -> error ) ;
}

Differences in which object functions are called to obtain information about the error. I think the second way is more convenient, because error checking can be combined into one if block with other functions mysqli_stmt . How to do this will be seen in the examples below.

$ id_min = 81 ;
$ id_max = 88 ;
$ stmt = $ mysqli -> stmt_init ( ) ;
if (
// prepare the request, where do we mark the data with the symbol? (placeholds)
( $ stmt -> prepare ( "SELECT title FROM sk2_articles WHERE id>? and id <?" ) === FALSE )
// bind variables to placeholders
or ( $ stmt -> bind_param ( 'ii' , $ id_min , $ id_max ) === FALSE )
// send data that is currently in bound variables
or ( $ stmt -> execute ( ) === FALSE )
// bind a variable to get the result in it
or ( $ stmt -> bind_result ( $ title ) === FALSE )
// make the query buffered
// if this line were not, the request would be unbuffered
or ( $ stmt -> store_result ( ) === FALSE )
// get result in bound variable
or ( $ stmt -> fetch ( ) === FALSE )
// close the prepared request
or ( $ stmt -> close ( ) === FALSE )
) {
die ( 'Select Error (' . $ stmt -> errno . ')' . $ stmt -> error ) ;
}
echo $ title ;

A few explanations to the above code.

1. In $ stmt-> bind_param (), the first parameter specifies the type of data to be bound (i - integer, d - double, s - string). The second and subsequent variables are specified, which will be bound to the corresponding placeholders declared in $ stmt-> prepare () by the characters "?" in the order in which they are listed in $ stmt-> prepare () . This is a noticeable difference from PDO, where the matchholdors can be named, and therefore do not depend on the order of the advertisement.
2. $ stmt-> bind_result is in my opinion the weakest point of the mysqli_stmt class, since it requires you to know exactly which data will be obtained from the query and in what order they are declared in the query. For the case of “SELECT * FROM ...” it is hardly applicable. The mysqli_result class offers a much more flexible approach to working with the result. To be completely objective, it is worth mentioning that you can use the $ stmt-> result_metadata () function to get mysqli_result containing only metadata without the data itself, and see what columns are obtained, but this approach seems to me unnecessarily complicated, and I mentioned only to avoid accusations of inaccurate information.
3. $ stmt-> store_result () , as I wrote in the comment to the line, is optional and is needed so that you can use functions such as:
   
   • $ mysqli_stmt-> num_rows - the number of rows received to the result,
   • $ stmt-> data_seek - move the internal pointer to the specified result string,
   • $ stmt-> free_result () - and do not forget to call for freeing memory if you used $ stmt-> store_result () .
   
   
4. $ stmt-> fetch () returns TRUE / FALSE if the data was received successfully / unsuccessfully, and NULL if there is no more data.
5. Not all functions that are combined in an if block place it in $ stmt-> error in case of an error , some call PHP Warning, but all of them return FALSE in case of failure, so this construction seems to me to be a good one. If you need to execute $ stmt-> execute () or $ stmt-> fetch () several times, then this will not work.
6. Regardless of whether the request was buffered or not, the following properties are available:
   
   • $ stmt-> field_count - the number of columns in the result,
   • $ stmt-> affected_rows - the number of affected lines by the previous query is not per sample,
   • $ stmt-> insert_id - automatically generated id of the previous insert.
   
   

Modify the code to get the result as an instance of the mysqli_result object.

$ id_min = 81 ;
$ id_max = 88 ;
$ stmt = $ mysqli -> stmt_init ( ) ;
if (
( $ stmt -> prepare ( "SELECT title FROM sx2_links WHERE id>? and id <?" ) === FALSE )
or ( $ stmt -> bind_param ( 'ii' , $ id_min , $ id_max ) === FALSE )
or ( $ stmt -> execute ( ) === FALSE )
// get the buffered result as mysqli_result,
// unbuffered result cannot be obtained, what I wrote about in flaws
or ( ( $ result = $ stmt -> get_result ( ) ) === FALSE )
or ( $ stmt -> close ( ) === FALSE )
) {
die ( 'Select Error (' . $ stmt -> errno . ')' . $ stmt -> error ) ;
}
$ row = $ result -> fetch_row ( ) ;
echo $ row [ 0 ] ;

The class mysqli_result and work with the result using it

As shown above, you could get the mysqli_result object using a “classical” query using the mysqli class, then it can be buffered or unbuffered, or using the mysqli_stmt class, then it is buffered. The result of the functions of this class depends on what result you get, so you need to be well aware that if your query is unbuffered you do not have the whole result and therefore you cannot know how many lines are in the result and you can only read it line-by-line in order.

The set of functions in this class will seem familiar to the old extensions:

• $ result-> fetch_row () - gets the current result row as a numbered array
• $ result-> fetch_assoc () - in the form of an associative array,
• $ result-> fetch_array () - the type of the array is given by a constant,
  
  1. MYSQLI_ASSOC - associative,
  2. MYSQLI_NUM is numbered,
  3. MYSQLI_BOTH - both
  
  
• $ result-> fetch_object () - the result string as an object.

About $ result-> fetch_object () I would like to talk separately. This function has two parameters, both optional:

• class_name is the name of the class on which the object will be created,
• params - an array of parameters that will be passed to the constructor when creating an object.

As you can see, passing the class constructor suppose an associative array of a single result string using this function does not work. She herself for you assign properties of the class, coinciding with the names of the field results. If a class property is not found, it is created dynamically, with a public scope.

class Book
{
private $ some1 ;
public $ some2 ;
protected $ id ;

function __construct ( $ param1 , $ param2 ) {
$ this -> some1 = $ param1 ;
$ this -> some2 = $ param2 ;
}
}
$ book = $ result -> fetch_object ( 'Book' , array ( 1 , 2 ) ) ;
var_dump ( $ book ) ;

Displays if there was only one id field as a result

object ( Book ) [ 4 ]
private 'some1' => int 1
public 'some2' => int 2
protected 'id' => int 382

Other useful functions and properties of the class in question:

• $ result-> fetch_all () - getting all rows as an array of numbered / associative / both types,
• $ result-> data_seek () - setting the internal pointer to the specified result series,
• $ result-> num_rows - the number of rows in the result.

References:
MySQLi page in the official documentation on php.net
More MySQLi examples
List of MySQLi functions in Russian