Php & my sql - how do pdo, mysq-li, and x devapi do what they do

PHP & MySQL -- How do PDO, MySQLi, and X DevAPI Do What They Do Dave Stokes @stoker David.Stokes @Oracle.com MySQL Community Manager MySQL Community Team Slides available at slideshare.net/davestokes Copyright © 2021 Oracle and/or its affiliates.

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2019 Oracle and/or its affiliates.

Overview of the MySQL PHP drivers/connectors/API ¶ There are several PHP APIs for accessing the MySQL database. Users can choose between mysqli, PDO_MySQL, or X DevAPI All three use mysqlnd driver

Overview of the MySQL PHP drivers/connectors/API ¶ There are several PHP APIs for accessing the MySQL database. Users can choose between the mysqli, PDO_MySQL, or X DevAPI extensions. All three use mysqlnd driver → drop-in replacement for the MySQL Client Library

The mysqlnd library is highly optimized for and tightly integrated into PHP. The MySQL Client Library cannot offer the same optimizations because it is a general-purpose client library. The mysqlnd library is using PHP internal C infrastructure for seamless integration into PHP. In addition, it is using PHP memory management, PHP Streams (I/O abstraction) and PHP string handling routines. The use of PHP memory management by mysqlnd allows, for example, memory savings by using read-only variables (copy on write) and makes mysqlnd apply to PHP memory limits. Additional advantages include: ● Powerful plugin API to extend feature set ● Asynchronous, non-blocking queries ● 150+ performance statistics ● Ships together with the PHP 5.3, and later, source ● No need to install MySQL Client Library ● Powerful plugins. See Below.

Why this talk came about No three way comparisons Examples lacking Lots of old information Truth versus mythology

PDO PDO_MYSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL databases (and several other RDMS). PDO_MYSQL uses emulated prepared statements by default. Supported Databases ● CURBID ● SQL Server ● Firebird ● DB2 ● Informix ● MySQL ● SQL Server/Sybase ● Oracle ● PostgreSQL ● SQLite ● ODBC/DB2

MySQLi The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. No client side prepared statements.

X DevAPI The X DevAPI is a common API provided by multiple MySQL Connectors providing easy access to relational tables as well as collections of JSON documents. The X DevAPI uses the X Protocol, the new generation client-server protocol of the MySQL 8.0 server. NOT AN ORM!!

The old mysql connector End of Life was with PHP 5.5 (2016) Still a lot of code out there to be ported DO NOT USE!!! Use mysqli

Somethings are going to be similar for all three After all you are connecting to the same database So things like authentication will be the same hostname username password port schema/database extra

// mysqli $mysqli = new mysqli("example.com", "user", "password", "database"); $result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message"); $row = $result->fetch_assoc(); // PDO $pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password'); $statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message"); $row = $statement->fetch(PDO::FETCH_ASSOC); // X DevAPI $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost/p1:33060"); $collection = $schema->getCollection("stuff"); var_dump($collection->find("name = 'Dave'")->execute()->fetchOne());

Test Data https://dev.mysql.com/doc/index-other.html world, world_x, and sakila datasets

SSL/TLS MySQL 8.0 is designed to be secure by default and will generate certificates for you (if you do not want to use your own).

PHP & MySQL -- Things in common ● Open Source ● LAMP Stack ● Extremely Popular ● ‘Backbone’ tech for the internet ● ~25 years old ● Predicted to be dead for ~24.5 years ● Sexy developers!

Login to MySQL Instance Specify • host • user • password (please use good ones) • schema (optional)

mysqli <?php $servername = "localhost"; $username = "root"; $password = "hidave"; // Create connection $conn = new mysqli($servername, $username, $password, "sakila" ); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully*<br>"; ?>

PDO <?php $servername = "localhost"; $username = "demo"; $password = "Passw0rd!"; try { $conn = new PDO("mysql:host=$servername;dbname=world", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } ?>

X DevAPI <<?PHP try { $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost"); } catch (Exception $e) { die("Connection could not be established: " / $e->getMessage()); } ?>

Accessing your data Again this stuff is pretty similar

mysqli <?php $servername = "localhost"; $username = "root"; $password = "hidave"; // Create connection $conn = new mysqli($servername, $username, $password, "world" ); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $result = $conn->query("SELECT * FROM country WHERE Code='USA'"); while ($row = $result->fetch_row()) { printf("%s (%s)n", $row[0], $row[1]); }

pdo <?php $servername = "localhost"; $username = "dave"; $password = "Passw0rd!"; try { $conn = new PDO("mysql:host=$servername;dbname=world", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } foreach($conn->query("SELECT * FROM country WHERE Code = 'USA'") as $row) { printf("%s (%s)n", $row[0], $row[1] ); }

X DevAPI JSON <?PHP try { $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost"); } catch (Exception $e) { die("Connection could not be established: " / $e->getMessage()); } $schema = $session->getSchema("world_x"); $collection = $schema->getCollection("countryinfo"); print_r($collection->find("Code = 'USA'") ->fields("[Code,Name]") ->execute() ->fetchOne());

X DevAPI table <?PHP try { $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost"); } catch (Exception $e) { die("Connection could not be established: " / $e->getMessage()); } $schema = $session->getSchema("world"); $table = $schema->getTable("country"); $row = $table->select('Name','Code') ->where("Code = 'USA'") ->execute() ->fetchALL(); print_r($row);

X DevAPI table <?PHP try { $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost"); } catch (Exception $e) { die("Connection could not be established: " / $e->getMessage()); } $schema = $session->getSchema("world"); $table = $schema->getTable("country"); $row = $table->select('Name','Code') ->where("Code = 'USA'") ->execute() ->fetchALL(); print_r($row); Not SQL!!

Document Store vs. Relational Tables Document Store $schema = $session->getSchema("world_x"); $collection = $schema->getCollection("countryinfo"); print_r($collection->find("Code = 'USA'")->fields("[Code,Name]")->execute()->fetchOne()); Relational Table $schema = $session->getSchema("world"); $table = $schema->getTable("country"); $row = $table->select('Name','Code')->where("Code = 'USA'")->execute()->fetchALL(); print_r($row);

X DevAPI SQL function <?PHP try { $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost/world_x"); } catch (Exception $e) { die("Connection could not be established: " / $e->getMessage()); } $rows = $session->sql("SELECT * FROM country LIMIT 2")->execute()->fetchAll(); print_r($rows);

Parameters & Prepared Statements PDO allows positional and named parameters mysqli uses positional ? placeholder X DevAPI uses named bindings

A prepared statement is a feature used to execute the same SQL statements repeatedly with high efficiency. 1. An SQL statement template is created and sent to the database with certain parameters marked as reserved ‘place holders’ 2. The database syntax checks and performs query optimization on the SQL statement template, storing the result without executing it 3. Sometime later the application binds the values to the parameter and the database executes the statement. The application may execute the statement as many times as it wants with different values

Main advantages of prepared statements • Query plan is only generated once -- big savings in optimization • Sending only the parameters saves bandwidth over sending the entire query. • Useful in reducing SQL injections! The parameter values are transmitted using a binary protocol and bound parameters do not need to be escaped as they are never substituted into the query string directly. If the original statement template is not derived from external input, SQL injection cannot occur. So no ‘Little Bobby Drop Tables’

Main disadvantage of prepared statements • For a simple one-off query you need an extra round trip to send statement and

MySQL 8.0 provides support for server-side prepared statements This support takes advantage of the efficient client/server binary protocol. • Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts. • Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

Example in SQL mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1;

mysqli $a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute();

$a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute(); Placeholder for auto_incremented column

mysqli $a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute(); Data Types

mysqli $a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute(); Data Types Character Description i Integer d Double s String b Blob

mysqli $a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute(); $a $b $c

m03.php $a = 72; $b = 4; $c = "Testing"; $stmt = $mysqli->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL,?,?,?);"); $stmt->bind_param("iis",$a, $b, $c); $stmt->execute(); $a $b $c INSERT INTO x (id,a,b,c) VALUES (NULL,72,4,”Testing”)

pdo $a = 33; $b = 101; $c = "Whohoo!"; $conn=->setAttributes(PDO::ATTR_EMULATE_PREPARES,false); $stmt = $conn->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL, :a, :b, :c)"); $stmt->execute([':a' => $a, ':b' => $b, ':c' => $c]);

pdo $a = 33; $b = 101; $c = "Whohoo!"; $conn=->setAttributes(PDO::ATTR_EMULATE_PREPARES,false); $stmt = $conn->prepare("INSERT INTO x (id,a,b,c) VALUES (NULL, :a, :b, :c)"); $stmt->execute([':a' => $a, ':b' => $b, ':c' => $c]); This says to use prepared statements not emulated prepared statements!

Emulated prepared statements - PDO Default PDO doesn’t send the SQL string to the server during prepare(). ● The SQL string is on the client-side, in memory in PDO code. ● The SQL query for is not checked for syntax or other errors! When execute() runs the query that was saved as an emulated-prepared query performs string-replacement on the saved SQL string before it submits the final SQL string to the server. ● This is the first time the server has seen that query so the query must be have the syntax checked and the passed to query optimizer.

xdevapi $collection->find("Code = :code") ->fields("[Code,Name]") ->bind(['code' => 'USA']) ->execute() ->fetchOne();

MySQL 8.0 provides support for server-side prepared statements. ALTER TABLE ALTER USER ANALYZE TABLE CACHE INDEX CALL CHANGE MASTER CHECKSUM {TABLE | TABLES} COMMIT {CREATE | DROP} INDEX {CREATE | RENAME | DROP} DATABASE {CREATE | DROP} TABLE {CREATE | RENAME | DROP} USER {CREATE | DROP} VIEW DELETE DO FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES | LOGS | STATUS | MASTER | SLAVE | USER_RESOURCES} GRANT INSERT INSTALL PLUGIN KILL LOAD INDEX INTO CACHE OPTIMIZE TABLE RENAME TABLE REPAIR TABLE REPLACE RESET {MASTER | SLAVE} REVOKE SELECT SET SHOW BINLOG EVENTS SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW} SHOW {MASTER | BINARY} LOGS SHOW {MASTER | SLAVE} STATUS SLAVE {START | STOP} TRUNCATE TABLE UNINSTALL PLUGIN UPDATE

Buffering Queries are buffered by default. Query results are stored in memory, which allows additional operations like counting the number of rows or seeking the current result pointer.

mysqli -- unbuffered m04.php $result = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

PDO -- unbuffered p04.php $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

'comparisons are odorous' - Shakespeare’s Much Ado About Nothing So you would think that comparing three connectors would be comparing ‘apples to apples’. Nope

CREATE TABLE x (id SERIAL PRIMARY KEY NOT NULL, a INT UNSIGNED NOT NULL, b INT UNSIGNED NOT NULL, c INT UNSIGNED NOT NULL); #!/usr/bin/python import random print("SET AUTOCOMMIT=0;"); for i in range(10): print(‘BEGIN TRANSACTION;’) for j in range(1000): a = random.randint(1,1000000000) b = random.randint(1,10000000) c = random.randint(1,1000000) s = 'INSERT INTO x VALUES (NULL,' + repr(a) + ', ' + repr(b) + ', ' + repr(c) + ');' print(s) print(“COMMIT;”) Schema Script to generate rows

for ($x=0; $x < 1000000 ; $x++) { $y=rand(1,10000); $result= = $conn->query(“SELECT * FROM x WHERE id=$y;”); } m06-mt.php real 1m11.647s user 0m5.911s sys 0m8.310s p06-mt.php real 1m9.242s user 0m8.578s sys 0m7.987s x06x-mt.php real 3m5.109s user 1m42.753s sys 0m27.542s select(‘*’) ->where(“id = :id”) ->bind([‘id’ => $y]) ->execute()-fetchone(); Averages of 10 runs, high/low tossed out

And then I changed Hardware... And yup, the numbers skewed differently and testing multiple threads, bigger datasets, and other approaches muddled things further. PDO was usually just slightly faster than mysqli, just a smidge. Benchmark your environment

Who supports the code? mysqli Oracle engineers X DevAPI Oracle engineers PDO Oracle engineers* * with reservations

Conclusion Use X DevAPI if you do not like SQL or are using the MySQL Document Store NoSQL database. PDO does support multiple databases which is a plus if you really can support using another data & can port. mysqli can handle asynchronous calls, mysql->info().

Copyright © 2020, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 59 Get $300 in credits and try MySQL Database Service free for 30 days. https://www.oracle.com/cloud/free/ Test Drive MySQL Database Service For Free Today

Startups get cloud credits and a 70% discount for 2 years, global exposure via marketing, events, digital promotion, and media, plus access to mentorship, capital and Oracle’s 430,000+ customers Customers meet vetted startups in transformative spaces that help them stay ahead of their competition Oracle stays at the competitive edge of innovation with solutions that complement its technology stack We have saved around 40% of our costs and are able to reinvest that back into the business. And we are scaling across EMEA, and that’s basically all because of Oracle.” —Asser Smidt CEO and Cofounder, BotSupply Oracle for Startups - enroll at oracle.com/startup A Virtuous Cycle of Innovation, Everybody Wins. Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 60

Then please consider buying my book on the JSON data type, how to use the supporting functions, and it is filled with example code to get you up to speed! Interested in using JSON with MySQL? Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 61

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2021 Oracle and/or its affiliates.

Php &amp; my sql - how do pdo, mysq-li, and x devapi do what they do

More Related Content

What's hot

Similar to Php &amp; my sql - how do pdo, mysq-li, and x devapi do what they do

More from Dave Stokes

Recently uploaded

Php &amp; my sql - how do pdo, mysq-li, and x devapi do what they do

Php & my sql - how do pdo, mysq-li, and x devapi do what they do

Similar to Php & my sql - how do pdo, mysq-li, and x devapi do what they do

Php & my sql - how do pdo, mysq-li, and x devapi do what they do