MySQL and PHP
			Jim Winstead
			  MySQL AB
		       jimw@mysql.com

		       July 10, 2003
       Unix Users Association of Southern California
			Los Angeles

---------------------------------------------------------------

* Intro to PHP

- Hello world:

  Hello world.

- Something with some actual code:

  Hello <?php echo "world!"?>

- Aimed squarely at what Rasmus calls "The Web Problem"

---------------------------------------------------------------

* Intro to MySQL

- An SQL database server

> SELECT author,title FROM books ORDER BY title DESC;

> INSERT INTO books SET author="Herman Melville",
                        title="Moby Dick";

> UPDATE books SET rating = 1 WHERE author = "Tom Clancy";

- Client-server architecture

- Runs on basically any Unix, Windows, NetWare, QNX, ...

- Accessible from C, Java, Perl, Python, .NET, PHP, ...

---------------------------------------------------------------

* Intro to PHP and MySQL

<?php

$conn = mysql_connect("localhost", "username", "password")
  or die("failed to connect: " . mysql_error());
mysql_select_db("library")
  or die("failed to select database: ".mysql_error());

$query = "SELECT title, author FROM books ORDER BY author";

$result = myqsl_query($query, $conn)
  or die("query failed: " . mysql_error());

while ($row = mysql_fetch_assoc($result)) {
  echo "$row[title] by $row[author]\n";
}

?>

---------------------------------------------------------------

* Current State of PHP

- PHP 4.3 Released December 27, 2002
  * Streams API
  * New build system
  * Bundled GD library

- PHP 5 Beta 1 Released June 30, 2003
  * Zend Engine 2: new object model, abstract classes,
    interfaces, exceptions
  * XML support revamped (based on libxml2)
  * SQLite bundled (serverless SQL-driven data store)

---------------------------------------------------------------

* Current State of MySQL

- MySQL 4.0 Generally Available (GA) Released March 15, 2003
  * InnoDB made a standard feature
  * Query cache
  * Dynamic server parameters (cache sizes, etc)
  * Embedded server library
  * NetWare port

- MySQL 4.1 Alpha Released April 3, 2003
  * Subqueries
  * Character sets and collations
  * Unicode (UTF-8 and UCS-2) support
  * OpenGIS (spatial data) support
  * New protocol supporting prepared statements

---------------------------------------------------------------

* Future of PHP

- PEAR and PECL
  * PEAR =~ CPAN
  * PECL makes it easy to install extensions

- ?

---------------------------------------------------------------

* Future of MySQL

- MySQL 5.0 Pre-alpha Available as of April 2003
  * Stored procedures (based on SQL-99 standard)
  * Query timeouts
  * Warning/info system
  * True VARCHAR (no space trimming)
  * ARRAY and BIT column types
  * More planned (online backup)
  * Alpha in the fall?

- The Database Formerly Known as SAP DB
  * Partnership with SAP AG announced May 2003
  * More info in the fall

---------------------------------------------------------------

* Query Cache

> SET GLOBAL query_cache_size = 32*1024*1024;

in my.cnf:
 [mysqld]
 query_cache_size=32M

> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_queries_in_cache | 9069      |
| Qcache_inserts          | 37067262  |
| Qcache_hits             | 198013545 |
| Qcache_not_cached       | 11042724  |
| Qcache_free_memory      | 6719448   |
| Qcache_free_blocks      | 2088      |
| Qcache_total_blocks     | 21203     |
+-------------------------+-----------+

- Total queries = Qcache_inserts + Qcache_hits + Qcache_not_cached = 246,123,531
- Hit rate = Qcache_hits / Total queries = 80%

* Subqueries

- An example:

> SELECT name,population FROM city AS o WHERE population >
   (SELECT AVG(population) FROM city WHERE country = o.country);

- Derived Tables

> SELECT * FROM 1,(SELECT table FROM t2 WHERE a > 5) t3
    WHERE t1.a = t3.a;

- Expression Sub-queries:

> SELECT t1.a, (SELECT a FROM t2 WHERE t2.b = t1.b) FROM t1;
> SELECT t1.a FROM t1
    WHERE (a,b) = (SELECT c,d FROM t2 WHERE t2.e = t1.e);
> SELECT t1.a FROM t1 WHERE a in (SELECT a FROM t2);
> SELECT t1.a FROM t1 WHERE (a, b) IN (SELECT c,d FROM t2);
> SELECT t1.a FROM t1 WHERE a < ALL (SELECT b FROM t2);

---------------------------------------------------------------

* Joins vs. subqueries

> SELECT name, code FROM country
    WHERE NOT EXISTS (SELECT * FROM city WHERE country = code);

> SELECT country.name, country.code FROM country
    LEFT JOIN city ON (country.code = city.country)
    WHERE city.name IS NULL;

---------------------------------------------------------------

* Character Sets and Collations

- 72 collations for 33 character sets

- Unicode: UTF-8, UCS-2

- Set at:
  * server             * database
  * table              * connection
  * _charset'String'   * CONVERT(<expr> USING <transcoding>)

> SELECT x FROM t ORDER BY x COLLATE <name>

+-----------------+--------------+--------------+
| Swedish/Finnish | German DIN-1 | German DIN-2 |
+-----------------+--------------+--------------+
| Muffler         | Muffler      | Müller       |
| MX Systems      | Müller       | Muffler      |
| Müller          | MX Systems   | MX Systems   |
| MySQL           | MySQL        | MySQL        |
+-----------------+--------------+--------------+

---------------------------------------------------------------

* Spatial Data

- OpenGIS Consortium
- OpenGIS Simple Feature Specifications for SQL
- SQL with Geometry Types environment

- Well-Known Text (WKT) Representation

  * POINT(10 10)
  * LINESTRING(10 10, 20 20, 30 40)
  * POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))

- Well-Known Binary (WKB) Representation

- R-Tree index

> SELECT person FROM table WHERE
    MBRContains(GeomFromText('Polygon((300 150, 310 155, 300 160, 300 150))'), location);

---------------------------------------------------------------

* PHP 5's New Object Model

- Handle-based
- __construct() and __destruct()
- public, private, and protected variables and members
- static variables and members
- final
- constants (const)
- __clone()
- dereferencing: $this->that->the_other_thing();
- __autoload()
- __get() and __set()
- __call()
- abstract classes
- interfaces

---------------------------------------------------------------

* Exceptions

- try, throw, and catch

<?php
  try {
    if ($input > MAXIMUM) {
      throw new Exception("$input is too big!");
    }
  } catch (Exception $e) {
    echo $e->getMessage();
  }
?>

---------------------------------------------------------------

* Type hinting

- Restrict arguments to complex types

<?php
  function juice(Fruit $ingredient) {
    /* do stuff */
  }

  my $food = new Hamburger();

  try {
    juice($food);
  } catch (Exception $e) {
    echo $e->getMessage();
  }
?>

---------------------------------------------------------------

* Simple XML

- Presents XML input as a bunch of associative arrays

<library>
 <book>
  <author>Herman Melville</author>
  <title>Moby Dick</title>
 </book>
 ...
</library>

<?php

$library = simplexml_load_file("library.xml");

foreach ($library->book as $book) {
  echo $book->title, " by ", $book->author, "\n";
}

?>

---------------------------------------------------------------

* SQLite

- Embedded flat-file SQL database
- Built-in by default
- Database-level locking
- All data stored as strings

<?php

$db = sqlite_open('database', 0666, $sqliterror)
  or die("failed to open database: $sqliterror");

$query = "SELECT title,author FROM library";
$result = sqlite_query($db, $query)
  or die("query failed: " . sqlite_error_string(sqlite_last_error($db)));

while ($record = sqlite_fetch_assoc($result)) {
  echo "$row[title] by $row[author]\n";
}
?>

---------------------------------------------------------------

* New MySQL/PHP Interface: mysqli

 - object-oriented interface (in parallel to procedural)
 - support for SSL and gzip compressed connections
 - prepared statements and variable binding
 - transaction control
 - master/slave awareness
 - profiler

---------------------------------------------------------------

* mysqli interface example

<?
$dbh = new mysqli();
$dbh->real_connect(array(
          "host"   => "localhost",
          "user"   => "username",
          "passwd" => "password",
          "database" => "library",
          "flags" => MYSQLI_CLIENT_COMPRESSED,
        ))
  or die("unable to connect: " . mysqli_error());

$query = "SELECT title, author FROM books"
       . " WHERE published > ?";

$stmt = $dbh->prepare($query);

$stmt->bind_param('2002-01-01');

$stmt->execute()
  or die("query failed: " . mysqli_error());

echo "<ul>\n";
while ($row = $stmt->fetch_assoc()) {
  echo "<li><i>$row[title]</i> by $row[author]</li>\n";
}
echo "</ul>\n";

?>

---------------------------------------------------------------

* PHP History

- PHP started with Rasmus Lerdorf in 1995
- Really matured in 1997 with the beginning of PHP 3
- With PHP 3, the core team of developers grew to 6
  (including me)
- Core group is now 9 developers
- BSD-licensed (was dual GPL/BSD until PHP 4)

---------------------------------------------------------------

* MySQL History

- Database engine that grew an SQL interface in 1995
- Originally under a 'Free Public License'
  -- prohibited commercial use without paid license
- Released under GPL in June/July 2000
- Started by three people: Monty, David, and Allan
- There are now 80 people working for MySQL AB,
  at least 30 of them developers