Raspberry Pi_Eng_24.4.11 PHP and MySQL database


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-nameuser-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( )