{"id":2228,"date":"2020-08-16T18:34:01","date_gmt":"2020-08-17T01:34:01","guid":{"rendered":"https:\/\/doubleecpu.com\/?page_id=2228"},"modified":"2020-08-23T02:22:16","modified_gmt":"2020-08-23T09:22:16","slug":"mysql-php-api","status":"publish","type":"page","link":"https:\/\/doubleecpu.com\/index.php\/mysql-php-api\/","title":{"rendered":"MySQL PHP API"},"content":{"rendered":"\n

Create and Setup for web API on Raspberry Pi LAMP server. View References from MariaDB Website.<\/a> <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

Selecting an API<\/h2>\n\n\n\n

It is recommended to use either the mysqli<\/a> or PDO_MySQL<\/a> extensions for new development. It is recommended to use the mysqlnd<\/a> library for PHP.
To verify the the extensions are installed:<\/p>\n\n\n\n

php -m | grep mysql*<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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:<\/p>\n\n\n\n
 <?php<\/strong> \n   $mysqli = new mysqli(\"localhost\", \"my_user\", \"my_password\", \"world\"); \n   printf(\"Initial character set: %s\\n\", $mysqli->character_set_name()); \n   if (!$mysqli->set_charset('utf8')) \n     { \n      printf(\"Error loading character set utf8: %s\\n\", $mysqli->error); \n      exit; \n     } \n   echo \"New character set information:\\n\"; \n   print_r( $mysqli->get_charset() ); \n?><\/strong><\/pre>\n\n\n\n
Buffered and Unbuffered Query<\/h4>\n\n\n\n
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<\/p>\n\n\n\n
query(\"SELECT Name FROM City\", MYSQLI_USE_RESULT); \nif ($uresult) \n   { \n     while ($row = $uresult->fetch_assoc()) \n      { \n       echo $row['Name'] . PHP_EOL; \n      } \n   } \n$uresult->close(); ?><\/pre>\n\n\n\n
Connections<\/h4>\n\n\n\n
For setting a connection option, the connect operation has to be performed in three steps: creating a connection handle with\u00a0mysqli_init<\/code><\/a>, setting the requested options using\u00a0mysqli_options<\/code><\/a>, and establishing the network connection with\u00a0mysqli_real_connect<\/code><\/a>.

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\u00a0mysqli.allow_persistent<\/a>. The total number of connections opened by a script can be limited with\u00a0mysqli.max_links<\/a>. The maximum number of persistent connections per PHP process can be restricted with\u00a0mysqli.max_persistent<\/a>. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls\u00a0mysqli_change_user<\/code><\/a>\u00a0to reset the state.\u00a0 Safe behavior has been made the default. The\u00a0mysqli_change_user<\/code><\/a>\u00a0function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag\u00a0MYSQLI_NO_CHANGE_USER_ON_PCONNECT<\/code>\u00a0being set. <\/p>\n\n\n\n
Executing statements<\/h4>\n\n\n\n
Statements can be executed with themysqli_query<\/code><\/a>,\u00a0 mysqli_real_query<\/a><\/code> and\u00a0mysqli_multi_query<\/code><\/a>\u00a0functions. 
The\u00a0mysqli_query<\/code><\/a>\u00a0function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. 
Calling\u00a0mysqli_query<\/code><\/a>\u00a0is identical to calling\u00a0mysqli_real_query<\/code><\/a>\u00a0followed by\u00a0mysqli_store_result<\/code><\/a>.
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<\/a><\/code> 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.<\/p>\n\n\n\n
Extensions<\/h4>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n
Read more “MySQL PHP API”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2228","page","type-page","status-publish","hentry"],"featured_media_urls":[],"_links":{"self":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages\/2228","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/comments?post=2228"}],"version-history":[{"count":0,"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/pages\/2228\/revisions"}],"wp:attachment":[{"href":"https:\/\/doubleecpu.com\/index.php\/wp-json\/wp\/v2\/media?parent=2228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}