Published Book on Amazon
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 1 | |
All of IOT Starting with the Latest Raspberry Pi from Beginner to Advanced – Volume 2 |
출판된 한글판 도서
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (상) | |
최신 라즈베리파이(Raspberry Pi)로 시작하는 사물인터넷(IOT)의 모든 것 – 초보에서 고급까지 (하) |
Original Book Contents
24.4.11 PHP and MySQL database
24.4.11.1 PHP MySQL extension
The functions for processing MySQL data in PHP are developed and provided as extensions of PHP. The following extensions have been developed so far.
■ Original MySQL extension
This is an extension function that was originally developed to access MySQL data in PHP. It was originally developed with the assumption that it will be applied only to systems prior to MySQL version 4.1.3. It can be applied to later versions, but it can not fully utilize the functions of the latest MySQL server, and it will be discarded in the future.
■ MySQL improved extension (mysqli)
It is a MySQL processing extension that is re-developed to be optimized for an object-oriented programming environment to take full advantage of the latest features of the MySQL database. Many features are included in PHP versions 5 and later. The <mysqli> extension has many advantages over the original MySQL extension. Major advantages are as follows.
√ Object-oriented interface
√ Support for Prepared Statements
√ Support for Multiple Statements
√ Support for Transactions
√ Enhanced debugging capabilities
√ Embedded server support
■ PDO extension
It is an extension that provides a database abstraction layer for PHP applications and allows PHP applications to use all database related functions consistently in the same format regardless of the actual database used. The advantage is that you can easily switch to another database if you need it, but you can not take advantage of all the features of a particular database.
The original MySQL extension will be obsoleted in the future, so you should use the <mysqli> extension or the PDO extension when developing new programs in the future.
For additional information, please see the followings:
■ http://php.net/manual/en/set.mysqlinfo.php
24.4.11.2 Interface Method of the <mysqli>
the <mysqli> extension supports the procedural interface as well as the object-oriented interface. The following shows examples of the object-oriented interface method and the procedural interface method.
<?php
//========================================= //====== procedural interface //========================================= $mysqli = mysqli_connect("example.com", "user", "password", "database"); if (mysqli_connect_errno($mysqli)) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); }
$res = mysqli_query($mysqli, "SELECT 'A world full of ' AS _msg FROM DUAL"); $row = mysqli_fetch_assoc($res); echo $row['_msg'];
//========================================= //====== object-oriented interface //========================================= $mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: " . $mysqli->connect_error; }
$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL"); $row = $res->fetch_assoc(); echo $row['_msg'];
?> |
The output of the above example is shown below
A world full of choices to please everybody. |
The usage differences between the two methods are as follows.
operation | Object-oriented | Procedural |
creating an object | new mysqli ( ) | mysqli_connect ( ) |
running a function | $object->function( … ) | mysqli_function($object, … ) |
referring to arrtibute | $object->attribute | mysqli_attribute($object) |
Basically, the object-oriented method systematically classifies and manages functions and attributes based on objects, so it is easy to understand and has various advantages in development and follow-up management. There is no difference in performance between the two methods, and the user can freely select the desired one according to his preference. You can also mix both ways in one document, but it is not recommended to make your code easier to understand.
24.4.11.3 Details of <mysqli> Object-Oriented Interface
Here we describe in detail the properties and methods of the object-oriented interface of <mysqli>.
● Property and method of "mysqli" class
To use the data in a MySQL database, you must first connect the database. Also, the connection should be maintained in the process of using the data in the database. The "mysqli connect" is responsible for connecting to the specified MySQL database. It is also responsible for interworking with MySQL in all future SQL processing.
"mysqli" class is an object class to manage and process this "mysqli connect". It must be created as an object in advance and used.
The properties and methods available in this class are as follows.
OOP Interface | Description | |
Properties | $mysqli::affected_rows | Gets the number of affected rows in a previous MySQL operation |
$mysqli::client_info | Returns the MySQL client version as a string | |
$mysqli::client_version | Returns MySQL client version info as an integer | |
$mysqli::connect_errno | Returns the error code from last connect call | |
$mysqli::connect_error | Returns a string description of the last connect error | |
$mysqli::errno | Returns the error code for the most recent function call | |
$mysqli::error | Returns a string description of the last error | |
$mysqli::field_count | Returns the number of columns for the most recent query | |
$mysqli::host_info | Returns a string representing the type of connection used | |
$mysqli::protocol_version | Returns the version of the MySQL protocol used | |
$mysqli::server_info | Returns the version of the MySQL server | |
$mysqli::server_version | Returns the version of the MySQL server as an integer | |
$mysqli::info | Retrieves information about the most recently executed query | |
$mysqli::insert_id | Returns the auto generated id used in the last query | |
$mysqli::sqlstate | Returns the SQLSTATE error from previous MySQL operation | |
$mysqli::warning_count | Returns the number of warnings from the last query for the given link | |
Methods | mysqli::autocommit() | Turns on or off auto-committing database modifications |
mysqli::change_user() | Changes the user of the specified database connection | |
mysqli::character_set_name(), mysqli::client_encoding | Returns the default character set for the database connection | |
mysqli::close() | Closes a previously opened database connection | |
mysqli::commit() | Commits the current transaction | |
mysqli::__construct() | Open a new connection to the MySQL server [Note: static (i.e. class) method] | |
mysqli::debug() | Performs debugging operations | |
mysqli::dump_debug_info() | Dump debugging information into the log | |
mysqli::get_charset() | Returns a character set object | |
mysqli::get_connection_stats() | Returns client connection statistics. Available only with mysqlnd. | |
mysqli::get_client_info() | Returns the MySQL client version as a string | |
mysqli::get_client_stats() | Returns client per-process statistics. Available only with mysqlnd. | |
mysqli::get_cache_stats() | Returns client Zval cache statistics. Available only with mysqlnd. | |
mysqli::get_server_info() | NOT DOCUMENTED | |
mysqli::get_warnings() | NOT DOCUMENTED | |
mysqli::init() | Initializes MySQLi and returns a resource for use with mysqli_real_connect. [Not called on an object, as it returns a $mysqli object.] | |
mysqli::kill() | Asks the server to kill a MySQL thread | |
mysqli::more_results() | Check if there are any more query results from a multi query | |
mysqli::multi_query() | Performs a query on the database | |
mysqli::next_result() | Prepare next result from multi_query | |
mysqli::options() | Set options | |
mysqli::ping() | Pings a server connection, or tries to reconnect if the connection has gone down | |
mysqli::prepare() | Prepare an SQL statement for execution | |
mysqli::query() | Performs a query on the database | |
mysqli::real_connect() | Opens a connection to a mysql server | |
mysqli::real_escape_string(), mysqli::escape_string() | Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection | |
mysqli::real_query() | Execute an SQL query | |
mysqli::refresh() | Flushes tables or caches, or resets the replication server information | |
mysqli::rollback() | Rolls back current transaction | |
mysqli::select_db() | Selects the default database for database queries | |
mysqli::set_charset() | Sets the default client character set | |
mysqli::set_local_infile_default() | Unsets user defined handler for load local infile command | |
mysqli::set_local_infile_handler() | Set callback function for LOAD DATA LOCAL INFILE command | |
mysqli::ssl_set() | Used for establishing secure connections using SSL | |
mysqli::stat() | Gets the current system status | |
mysqli::stmt_init() | Initializes a statement and returns an object for use with mysqli_stmt_prepare | |
mysqli::store_result() | Transfers a result set from the last query | |
mysqli::thread_id() | Returns the thread ID for the current connection | |
mysqli::thread_safe() | Returns whether thread safety is given or not | |
mysqli::use_result() | Initiate a result set retrieval |
Table 24‑1 Property and method of "mysqli" class
● property and method of "mysqli_stmt" class
"mysqli statement" stands for pre-prepared SQL statements that can be executed most efficiently depending on the current state of the database before the SQL statement actually executes. Using prepared SQL statement like this, it is possible to check the problem of SQL statement before executing SQL statement, and make preparation to execute SQL statement efficiently.
"mysqli_stmt" class is an object class to manage and process this "mysqli statement". It must be created as an object in advance and used.
The properties and methods that is avail in this class are as follows.
OOP Interface | Description | |
Properties | $mysqli_stmt::affected_rows | Returns the total number of rows changed, deleted, or inserted by the last executed statement |
$mysqli_stmt::errno | Returns the error code for the most recent statement call | |
$mysqli_stmt::error | Returns a string description for last statement error | |
$mysqli_stmt::field_count | Returns the number of field in the given statement - not documented | |
$mysqli_stmt::insert_id | Get the ID generated from the previous INSERT operation | |
$mysqli_stmt::num_rows | Return the number of rows in statements result set | |
$mysqli_stmt::param_count | Returns the number of parameter for the given statement | |
$mysqli_stmt::sqlstate | Returns SQLSTATE error from previous statement operation | |
Methods | mysqli_stmt::attr_get() | Used to get the current value of a statement attribute |
mysqli_stmt::attr_set() | Used to modify the behavior of a prepared statement | |
mysqli_stmt::bind_param() | Binds variables to a prepared statement as parameters | |
mysqli_stmt::bind_result() | Binds variables to a prepared statement for result storage | |
mysqli_stmt::close() | Closes a prepared statement | |
mysqli_stmt::data_seek() | Seeks to an arbitrary row in statement result set | |
mysqli_stmt::execute() | Executes a prepared Query | |
mysqli_stmt::fetch() | Fetch results from a prepared statement into the bound variables | |
mysqli_stmt::free_result() | Frees stored result memory for the given statement handle | |
mysqli_stmt::get_result() | Gets a result set from a prepared statement. Available only with mysqlnd. | |
mysqli_stmt::get_warnings() | NOT DOCUMENTED | |
$mysqli_stmt::more_results() | NOT DOCUMENTED Available only with mysqlnd. | |
$mysqli_stmt::next_result() | NOT DOCUMENTED Available only with mysqlnd. | |
mysqli_stmt::num_rows() | See also property $mysqli_stmt::num_rows | |
mysqli_stmt::prepare() | Prepare an SQL statement for execution | |
mysqli_stmt::reset() | Resets a prepared statement | |
mysqli_stmt::result_metadata() | Returns result set metadata from a prepared statement | |
mysqli_stmt::send_long_data() | Send data in blocks | |
mysqli_stmt::store_result() | Transfers a result set from a prepared statement |
Table 24‑2 Property and method of "mysqli_stmt" class
● Property and method of "mysqli_result" class
"mysqli result" is a query result created as a result of executing an SQL SELECT statement, which means a data set consisting of several rows retrieved from the specified tables of a particular database. Each row retrieved here is composed of multiple columns.
"mysqli_result" class is an object class to manage and process this "mysqli result". It must be created as an object in advance and used.
The properties and methods that are available in this class are as follows.
OOP Interface | Description | |
Properties | $mysqli_result::current_field | Get current field offset of a result pointer |
$mysqli_result::field_count | Get the number of fields in a result | |
$mysqli_result::lengths | Returns the lengths of the columns of the current row in the result set | |
$mysqli_result::num_rows | Gets the number of rows in a result | |
Methods | mysqli_result::data_seek() | Adjusts the result pointer to an arbitrary row in the result |
mysqli_result::fetch_all() | Fetches all result rows and returns the result set as an associative array, a numeric array, or both. Available only with mysqlnd. | |
mysqli_result::fetch_array() | Fetch a result row as an associative, a numeric array, or both | |
mysqli_result::fetch_assoc() | Fetch a result row as an associative array | |
mysqli_result::fetch_field_direct() | Fetch meta-data for a single field | |
mysqli_result::fetch_field() | Returns the next field in the result set | |
mysqli_result::fetch_fields() | Returns an array of objects representing the fields in a result set | |
mysqli_result::fetch_object() | Returns the current row of a result set as an object | |
mysqli_result::fetch_row() | Get a result row as an enumerated array | |
mysqli_result::field_seek() | Set result pointer to a specified field offset | |
mysqli_result::free(), mysqli_result::close, mysqli_result::free_result | Frees the memory associated with a result |
Table 24‑3 Property and method of "mysqli_result" class
24.4.11.4 Usage of <mysqli> Object-Oriented Interface
In PHP, you need to connect to the database in advance to use the data in the MySQL database.
● Creating "connect" object
To connect to a MySQL database, you first perform connection operation using the database connection information, create an object, and store the connect information for that database to "connect" object. Later, when it is necessary to actually execute SQL statements to refer to the data in the database, you execute the SQL statement through "connect" object created earlier.
To connect to a MySQL database, you execute "mysqli( )" function, and use "new" command to create the object, and then stores the connect information to "connect" object. The basic format is as follows.
$connect_object = new mysqli( ‘host-name’ , ‘user-id’ , ‘password’ , ‘database-name’ ) |
● Running SQL to query database data
"query( )" function is used to process specific SQL statements against a MySQL database. "query( )" function returns the result of executing the SQL statement, returning the result as an object of the form "result" object. The basic processing format is as follows.
$result_object = $connect_object->query( "SELECT-SQL" ) |
● Processing queried database data
A "result" object may contain multiple data elements. Executing "fetch_assoc( )" function returns the data contained in the result object one by one, returning the result as an object of the form "row" object. The basic processing format is as follows.
$row_object = $result_object->fetch_assoc( ) |
A "row" object contains data for several columns together. To refer to the data for each column, the column is specified in the format ["column-name"]. The basic processing format is as follows.
$data-variable = $row_object["column-name"] |
Normally, a "result" object has several data, so for sequential iterative processing, it can be usually processed as follows by using "while" command.
while ( $row_object = $result_object->fetch_assoc( ) ) { statement-1 ; statement-2 ; } |
● Closing "connect"
If you no longer need a database connection, you should terminate "connect" using "close( )" function as follows. The basic processing format is as follows.
$connect_object->close( ) |