Today I dusted off my PHP 5: Power Programming by Andi Gutmans, Stig Bakken and Derick Rethans.  It had been sitting on my shelf at work for some time and I hadn't looked at it for quite a while.  My original intention was to re-read the chapter about benchmarking (to continue Micro Benchmarking PHP functions) which I did (and I found a great new Benchmarking script).  I also read a little more about the __autoload function mentioned by John in his latest blog: PHP Automagic.

I came across chapter 6 which is all about using PHP with MySQLi.   I've never used MySQLi before so I decided to give it a go.  MySQLi stands for MySQL Improved and is an extension bundled with MySQL 5+.  The first thing that intrigued me about MySQLi was the ability to use it as an Object (vs procedural style):

// Create the MySQLi class/connection
$mysqli = new mysqli("localhost", "root", "", "mysqli");

// Test the Connection
if (mysqli_connect_errno()) {
    die("MySQLi Connect failed: " . mysqli_connect_error());
}

// Query the Database
if ($result = $mysqli->query("SELECT name FROM `test_table`")) {
    while ($obj = $result->fetch_object()) {
       $name = $obj->name;
       echo "$name ";
    }
   $result->free();
}
$mysqli->close();

Executing Multiple Statements
A great feature of MySQLi is the ability to execute multiple SQL statements using only one function call.  Seperate querys by a semicolon:

// Create the MySQLi class/connection
$mysqli = new mysqli("localhost", "root", "", "mysqli");
// Test the Connection
if (mysqli_connect_errno()) {
    die("MySQLi Connect failed: " . mysqli_connect_error());
}
// Set our multiple Query
$query = "SELECT name FROM `test_table`; SELECT last FROM `test_table`";

// Query the Database
if ($result = $mysqli->multi_query($query)) {
    do {
       // Store result set
       if ($result = $mysqli->store_result()) {
          while ($obj = $result->fetch_row()) {
               echo $obj[0] . "
";
          }
         $result->free();
       }

      // Print a seperator
      if ($mysqli->more_results()) {
          echo "------ Next Results -----
";
       }
   } while ($mysqli->next_result());
}
$mysqli->close();

Prepared Statements
Another major advantage of MySQLi over MySQL is Prepared Statements. Prepared Statements are query templates stored in the MySQL database.  The developer can then call on these statements stored in the MySQL buffer using a link. Prepared statements uses bound variables with two types total: input and output.  I'll show an example using input variables in the template and output variables to store/retrieving data:

// Create the MySQLi class/connection
$mysqli = new mysqli("localhost", "root", "", "mysqli");

// Test the Connection
if (mysqli_connect_errno()) {
    die("MySQLi Connect failed: " . mysqli_connect_error());
}

// Where condition
$where = "jordan";

// Set our multiple Query Template
$query = "SELECT name, last FROM `test_table` WHERE name=?";

// Create prepared statement
$stmt = $mysqli->prepare($query);

// Bind the params
$stmt->bind_param("s", $where); // Input variable

// Execute Query
$stmt->execute();

// Bind the results (name and last from query above)
$stmt->bind_result($name, $last); // Output variables

// Loop through results
while ($stmt->fetch()) {
    print "$last, $name";
}

// Close statement
$stmt->close();

// Close Connection
$mysqli->close();

Notice the "?" in the $query?  That is an example of an input template and you can see the use the input variable at $stmt->bind_param() function.  You can see the output variables at $stmt->bind_result function.


Much more
This was just a brief overview of MySQL Improved. There are many more functions and several benefits over using the old mysql extension.  For more information visit the PHP.net page: http://us.php.net/manual/en/class.mysqli.php