The 5th Annual China PHP Conference

The MySQLi class

Introduction

Represents a connection between PHP and a MySQL database.

Class synopsis

MySQLi {
/* Properties */
int $affected_rows;
string $client_info;
int $client_version;
string $connect_errno;
string $connect_error;
int $errno;
string $error;
int $field_count;
int $client_version;
string $host_info;
string $protocol_version;
string $server_info;
int $server_version;
string $info;
mixed $insert_id;
string $sqlstate;
int $thread_id;
int $warning_count;
/* Methods */
int MySQLi::mysqli_affected_rows ( mysqli $link )
bool mysqli::autocommit ( bool $mode )
bool mysqli::change_user ( string $user , string $password , string $database )
string mysqli_get_client_info ( mysqli $link )
bool mysqli::close ( void )
bool mysqli::commit ( void )
int mysqli_connect_errno ( void )
string mysqli_connect_error ( void )
mysqli mysqli_connect ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )
bool mysqli::debug ( string $message )
int mysqli_errno ( mysqli $link )
string mysqli_error ( mysqli $link )
int mysqli_field_count ( mysqli $link )
object mysqli::get_charset ( void )
string mysqli::get_client_info ( void )
array mysqli_get_client_stats ( void )
string mysqli_get_host_info ( mysqli $link )
string mysqli_get_server_info ( mysqli $link )
mysqli_warning mysqli::get_warnings ( void )
string mysqli_info ( mysqli $link )
mysqli mysqli::init ( void )
mixed mysqli_insert_id ( mysqli $link )
bool mysqli::kill ( int $processid )
bool mysqli::more_results ( void )
bool mysqli::multi_query ( string $query )
bool mysqli::next_result ( void )
bool mysqli::options ( int $option , mixed $value )
bool mysqli::ping ( void )
public int mysqli::poll ( array &$read , array &$error , array &$reject , int $sec [, int $usec ] )
mysqli_stmt mysqli::prepare ( string $query )
mixed mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )
bool mysqli::real_connect ([ string $host [, string $username [, string $passwd [, string $dbname [, int $port [, string $socket [, int $flags ]]]]]]] )
string mysqli::escape_string ( string $escapestr )
bool mysqli::real_query ( string $query )
public mysqli_result mysqli::reap_async_query ( void )
bool mysqli::rollback ( void )
bool mysqli::select_db ( string $dbname )
bool mysqli::set_charset ( string $charset )
bool mysqli::set_local_infile_handler ( mysqli $link , callback $read_func )
string mysqli_sqlstate ( mysqli $link )
bool mysqli::ssl_set ( string $key , string $cert , string $ca , string $capath , string $cipher )
string mysqli::stat ( void )
mysqli_stmt mysqli::stmt_init ( void )
mysqli_result mysqli::store_result ( void )
int mysqli_thread_id ( mysqli $link )
bool mysqli_thread_safe ( void )
mysqli_result mysqli::use_result ( void )
}

Table of Contents

add a note add a note

User Contributed Notes 1 note

up
0
Vasiliy Makogon
16 hours ago
The main drawbacks of all libraries for working with the database in PHP are:

1. Verbosity

    To prevent SQL injection, developers have two ways:

        - Use prepared requests.
        - Manually escaping parameters going into the body of an SQL query. String parameters are run via mysqli_real_escape_string (), and the expected numeric parameters lead to the corresponding types - int and float.

    Both approaches have enormous disadvantages:

        - Prepared queries are awfully verbose. To use "out of the box" the abstraction of PDO or the extension of mysqli, without aggregating all methods to get data from the DBMS is simply impossible - to get the value from the table you need to write a minimum of 5 lines of code! And so for every request!
        - Manual screening of parameters going to the body of an SQL query is not even discussed. A good programmer is a lazy programmer. Everything should be as automated as possible.

2. Failed to get SQL query for debugging

    To understand why the SQL-query does not work in the program, it needs to be debugged - to find either a logical or a syntactic error. To find the error, it is necessary to "see" the SQL query itself, to which the database was "bent", with parameters set in its body. Those. To have a fully-formed SQL.
    If the developer uses the PDO, with the requests being prepared, then this is done ... IMPOSSIBLE! No maximum convenient mechanisms for this in their native libraries are FOREWORD. It remains either to pervert, or climb into the database log.

Solution: Database - class for working with MySql - github.com/Vasiliy-Makogon/Database

     Eliminates the verbosity - instead of 3 or more lines of code for execution of one query when using the "native" library, you write only 1!
     Shields all parameters going to the body of the request, according to the specified type of placeholders - reliable protection against SQL injections.
     It does not replace the functionality of the "native" mysqli adapter, but simply complements it.

What are placeholders?

Placeholders are special typed markers that are written in the SQL query string instead of explicit values (query parameters). And the values themselves are passed "later", as subsequent arguments to the main method that executes the SQL query:

<?php
// Connect to the DBMS and get the Database_Mysql object
// Database_Mysql - "wrapper" over the "native" mysqli object
$db = Database_Mysql::create ("localhost", "root", "password")
      
// Select the database
      
-> setDatabaseName ("test")
      
// Select the character set
      
-> setCharset ("utf8");

// Get the result object Database_Mysql_Statement
// Database_Mysql_Statement - "wrapper" over the "native" object mysqli_result
$result = $db->query ("SELECT * FROM` users` WHERE `name` = '? S' AND` age` =? I "," Basil ", 30);

// Get the data (in the form of an associative array, for example)
$data = $result->fetch_assoc();

// Does the query not work? No problem - print it out:
echo $db->getQueryString();
?>

SQL query parameters passed through the placeholders system are processed by special screening functions, depending on the type of placeholders.

More information see on github.com/Vasiliy-Makogon/Database
To Top