PHP 5 + MySQL 5 A Perfect 10

1. PHP 5 + MySQL 5 A Perfect 10

2. 1. mysqli extension • i is for improved! • All new MySQL extension for PHP 5 • Result of • New binary client protocol in MySQL 4.1 • Old mysql extension showing age • Biggest change is that database handle is now mandatory and is the first argument

3. 1. mysqli extension $db = mysqli_connect($server, $user, $password, "users"); $r = mysqli_query($db, "SELECT user FROM users"); while ($row = mysqli_fetch_assoc($r)) { print $row['user']; } mysqli_free_result($r); mysqli_close($db);

4. 2. Object-Oriented Interface • Create a MySQL object! • No real advantages over the procedural interface, except that objects are inherently cooler than functions. • Actually, there are a few neat things you can do by subclassing: • Create specialized classes • Redefine methods

5. 2. Object-Oriented Interface $db = new mysqli($server, $user, $password, "users"); $r = $db->query("SELECT user FROM users"); while ($row = $r->fetch_assoc()) { print $row['user']; } $r->free_result(); unset($db);

6. 3. Prepared Statements • Define a query “template” • Faster for MySQL to execute • Send less data • Defense against SQL injection attacks

7. 3. Prepared Statements $db = mysqli_connect($server, $user, $password, 'stocks'); $sql = 'SELECT price FROM stocks WHERE ticker = ?'; $stmt = mysqli_stmt_init($db); if (mysqli_stmt_prepare($stmt, $sql)) { // More to come… }

8. 4. Bound Parameters • Map PHP variables with MySQL fields • Works with stored procedures • Can bind for both input and output • Watch your variable scope

9. 4. Bound Parameters $ticker = 'EBAY'; if (mysqli_stmt_prepare($stmt, $sql)) { mysqli_stmt_bind_param($stmt, 's', $ticker); mysqli_stmt_bind_result($stmt, $price); mysqli_stmt_execute($stmt); print "$ticker trades at $price\n"; } EBAY trades at 75.01

10. 4. Bound Parameters $ticker = 'EBAY'; $stmt = $db->stmt_init(); if ($stmt->prepare($sql)) { $stmt->bind_param('s', $ticker); $stmt->bind_result($price); $stmt->execute(); print "$ticker trades at $price\n"; } EBAY trades at 75.01

11. 5. SSL Connections • Encrypt the connection between PHP and MySQL • Slows things down • Useful when you don’t control the path between the client application and MySQL • Somewhat painful to set up if you’re an SSL novice

12. 5. SSL Connections $db = mysqli_init(); mysqli_ssl_set($db, '/usr/local/mysql/server-key.pem', '/usr/local/mysql/server-cert.pem', '/usr/local/mysql/cacert.pem', NULL, NULL); mysqli_real_connect($db, 'external.example.org', 'ssl-user', 'password', 'database');

13. 5. SSL Connections $db = mysqli_init(); mysqli_options($db, MYSQLI_READ_DEFAULT_FILE, '/etc/my.cnf'); mysqli_real_connect($db, 'external.example.org', 'ssl-user', 'password', 'database');

14. 6. Multi-Query Statements • Send multiple SQL queries all at once • Super useful for phpMyAdmin • Increases the danger of SQL injection attacks • Requires special set of functions • Forcibly disabled in mysqli_query() • More work to iterate; made easier by using an Iterator™

15. 6. Multi-Query Statements if (mysqli_multi_query($db, $query)) { do { if ($r = mysqli_store_result($db)) { while ($row = mysqli_fetch_row($r)) { print "$row[0]\n"; } mysqli_free_result($result); } } while (mysqli_next_result($db)); }

16. 6. Multi-Query Statements $it = new MySQLiQueryIterator($db, $query); foreach ($it as $r) { if ($r) { while ($row = mysqli_fetch_row($r)) { print "$row[0]\n"; } } }

17. 7. Subselects • New in MySQL 4.1 • Run a query within a query • Makes it faster and easier to filter data • Places work inside MySQL instead of PHP • Could often by “worked around” using a self-join, but not always • Know what your query will return • One row (=) or many (IN())?

18. 7. Subselects mysql> SELECT speaker FROM speakers WHERE topic = (SELECT topic FROM speakers WHERE speaker = 'Adam Trachtenberg'); • Better hope I’m not talking on multiple subjects

19. 7. Subselects mysql> SELECT speaker FROM speakers WHERE topic IN (SELECT topic FROM speakers WHERE speaker = 'Adam Trachtenberg');

20. 8. Character Sets • New in MySQL 4.1 • Store data using different character sets • Collate data using different character sets • Important when • You want a case-insensitive sort • Different cultures place the same letter in different positions in their alphabets • What to do with • n vs ñ • u vs ü

21. 8. Character Sets Four records • Muffler • Müller • MX Systems • MySQL mysql> SELECT X FROM T ORDER BY X COLLATE collation_name;

22. 9. Stored Procedures • New in MySQL 5.0 • Sequence of SQL statements stored on your MySQL server • Make request with set of parameters, get back chunk of relatively complete data • Works regardless of client language • Speedier than even prepared statements • More secure (can wall off access to tables except through pre-defined procedures) • A work in progress…

23. 9. Stored Procedures mysql> CREATE PRODCEDURE getNumberOfSpeakers (OUT n INT) BEGIN SELECT COUNT(*) INTO n FROM speakers; END mysql> CALL getNumberOfSpeakers(@number); mysql> SELECT @number;

24. 9.1. Cursors • New in MySQL 5.0 • CURrent Set of RecordsS • Lets you refer to the results of a SELECT statement on the server • Works within stored procedures and functions • Still fairly limited. Just a test.

25. 9.1. Cursors mysql> DECLARE speakers CURSOR FOR SELECT speaker, topic FROM speakers; mysql> OPEN speakers; mysql> FETCH speakers INTO s, t; mysql> CLOSE speakers;

26. 10. Views • New in MySQL 5.0 • Let you create a “virtual” table based on SQL queries • CREATE VIEW view AS SELECT ... • SELECT statement can include JOINs • You can now refer to “view” as if it was a real table: SELECT * FROM view WHERE ... • Changing rows in the view alters the data back in the original table.

27. 11. Next Year: Go to Eleven • NIGEL: What we do is if we need that extra...push over the cliff...you know what we do? • MARTY: Put it up to eleven. • NIGEL: Eleven. Exactly. One louder. • MARTY: Why don't you just make ten louder and make ten be the top... number...and make that a little louder? • NIGEL:<looking somewhat befuddled> ...these go to eleven.

28. Shameless Plug: • PHP 5, MySQL 4.0, and 4.1 • New mysqli extension • Everything covered here, but in greater detail. (Except 5.0) • How to migrate • From PHP 4 / mysql / MySQL 4.0 • To PHP 5 / mysqli / MySQL 4.1