Create and Setup for web API on Raspberry Pi LAMP server. View References from MariaDB Website.
An Application Programming Interface, or API, defines the classes, methods, functions and variables that are called in order to carry out desired tasks. PHP applications that need to communicate with databases use exposed API via PHP extensions. Several options are available, this instruction page will explain the setup for Connectors for Raspberry LAMP server to connect to the database and Raspberry Pi, and any database-specific drivers, software libraries, and any other requirements that needs to be installed.
Selecting an API
It is recommended to use either the mysqli or PDO_MySQL extensions for new development. It is recommended to use the mysqlnd library for PHP.
To verify the the extensions are installed:
php -m | grep mysql*
To set up php to correctly read and display the character set for the data stored, can be done using mysqli->set_charset( ‘[character set]’)
Setting the charset for MySQL:
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
printf("Initial character set: %s\n", $mysqli->character_set_name());
if (!$mysqli->set_charset('utf8'))
{
printf("Error loading character set utf8: %s\n", $mysqli->error);
exit;
}
echo "New character set information:\n";
print_r( $mysqli->get_charset() );
?>
Buffered and Unbuffered Query
Buffered queries, “MYSQL_STORE_RESULTS”, use the buffered mode by default which transfers data from MySQL Server to PHP immediately and is kept in the memory of the PHP process until all references are unset or the data reference is explicitly freed, which happens automatically during request end the latest.
Unbuffered queries, “MYSQLI_USE_RESULT”, execute and return a resource for data to be fetched. The data result remains on MYSQL server until PHP data fetches full results after which new data can be queried on the same connection.
Example of unbuffered query: mysqli
query("SELECT Name FROM City", MYSQLI_USE_RESULT);
if ($uresult)
{
while ($row = $uresult->fetch_assoc())
{
echo $row['Name'] . PHP_EOL;
}
}
$uresult->close(); ?>
Connections
For setting a connection option, the connect operation has to be performed in three steps: creating a connection handle with mysqli_init, setting the requested options using mysqli_options, and establishing the network connection with mysqli_real_connect.
The mysqli extension supports persistent database connections, which are pooled connections, data put into a pool for later reuse, if a connection to the same server using the same username, password, socket, port and default database is opened. Reuse saves connection overhead. Depending on the web server deployment model, a PHP process may serve one or multiple requests so a pooled connection may be used by one or more scripts subsequently.
The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links. The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user to reset the state. Safe behavior has been made the default. The mysqli_change_user function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.
Executing statements
Statements can be executed with themysqli_query, mysqli_real_query and mysqli_multi_query functions.
The mysqli_query function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call.
Calling mysqli_query is identical to calling mysqli_real_query followed by mysqli_store_result.
After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query combines statement execution and result set buffering. PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory.
If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.