Introduction to Mysql Improved (mysqli) and Prepared StatementsWhat is mysqli?MYSQL has had a firm collaboration with PHP for years now. Both have matured quite
a bit, and since PHP5 and MYSQL 4.1, new features have been created that are not
supported by the mysql adapter we have grown to love. The answer was found when Georg Richter,
the creator of PHP's mysql extensions, created a new driver called MYSQLi or mysql improved, that
supports the new features found in both technologies.
Why Make the switch?Maintainable:
MYSQL has been known to have pretty horrible code maintenance issues, even when using
Abstraction layers.
Similiar Syntax:
You will not have to relearn syntax so to speak. MYSQLi syntax is very, very similiar to
the MYSQL extension.
New Interface:
MYSQLi provides access in both procedural form, common to the mysql extension, and also a new Object Oriented interface using the power of PHP5.
Advanced Options:
You can now set advanced connection options during initialization.
Speed:
Database Overhead is a common problem for performance issues within PHP scripts, the new extension has improved speed dramatically.
SECURITY!!!!!!!!:
Since the binary protocol in mysql has changed, and now supports prepared statements, this gives you an extremely secure method for executing queries. This completely elminated the possibility
of SQL Injections.
Also the authentication protocol has changed, making it harder to extract hashes over the network.
What are Prepared Statements, How do they work, and What is the '?' in queries?Prepared statements seperate SQL logic and data. First, when you prepare a query, MYSQL will parse the query string and set aside placeholders for the question marks in the query.
Then when data is bound, MYSQL will execute the query with the data bound to the question marks. You can actually prepare one query string and execute it multiple times with different data with no additional
overhead, very very useful.
A BIG NOTE! Please look at the php manual for binding data. You must provide a string with data-types for the data being bound. Say we wanted to bind a string and integer to a query, we would provide bind_param("si","string",1);
How To Switch?NOTE: Many functions of mysqli require you to provide the link from the initialization, in the way you would provide the file handler to input/output functions in php.I really recommend having both extensions, (ext/mysql and ext/mysqli) installed, so that you can test applications using both interfaces. I make it a requirement to try to get my applications to be compatible with both adapters. More on that later.
To make the switch you need to install the new extension, either by recompiling PHP with mysqli, or adding the extension to your extensions folder and adding a line to your extensions section of php.ini. Restart your web server, and you will
have access to the new extension.
MYSQLi Procedural StyleBasic Usage Example:
CODE
// Connect to database
$link = mysqli_connect('host','username','password','default database');
// Check for Errors
if(mysqli_connect_errno()){
echo mysqli_connect_error();
}
// Prepare Query
$query = "SELECT * FROM table";
// Escape Query
$query = mysqli_real_escape_string($link,$query);
// Perform Query
if($result = mysqli_query($link,$query)){
// Cycle through results
while($row = mysqli_fetch_object($result)){
echo $row->column;
}
// Free Result Set
mysqli_free_result($result);
}
// Close Connection
mysqli_close($link);
Prepared Statements
CODE
// Setup Query
$query = "SELECT `id` FROM employees WHERE `name`=?";
// Setup parameter to be bound into query
$name = "Joey";
// Get instance of statement
$stmt = mysqli_stmt_init($link);
// Prepare Query
if (mysqli_stmt_prepare($stmt, $query)) {
// Bind Parameters [s for string]
mysqli_stmt_bind_param($stmt, "s", $name);
// Execute Statement
mysqli_stmt_execute($stmt);
// Bind results to variable
mysqli_stmt_bind_result($stmt, $employee_id);
// Fetch Value
mysqli_stmt_fetch($stmt);
// Echo Result
echo "$name has an ID of $employee_id";
// Close Statement
mysqli_stmt_close($stmt);
}
Fresh, New, Hip, The OO WayBasic Usage:
CODE
// New Connection
$mysqli = new mysqli('host','username','password','default database');
// Check for errors
if(mysqli_connect_errno()){
echo mysqli_connect_error();
}
// Create Query
$query = "SELECT * FROM table";
// Escape Query
$query = $mysqli->real_escape_string($query);
// Execute Query
if($result = $mysqli->query($query)){
// Cycle through results
while($row = $mysqli->fetch_object($result)){
echo $row->column;
}
// Free result set
$result->close();
}
// Close connection
$mysqli->close();
Prepared Statements:
CODE
$query = "SELECT `id` FROM employees WHERE `name`=?";
// Setup parameter to be bound into query
$name = "Joey";
// Get instance of statement
$stmt = $mysqli->stmt_init();
// Prepare Query
if($stmt->prepare($query)){
// Bind Parameters [s for string]
$stmt->bind_param("s",$name);
// Execute statement
$stmt->execute();
// Bind result variables
$stmt->bind_result($employee_id);
// Fetch Value
$stmt->fetch();
// Echo results
echo "$name has an ID of $employee_id";
// Close Statement
$stmt->close();
}
Fear of Commitment?From now the snippets will be OO style, check php.net for procedural references.
In mysqli, you can use commit() to execute a number of queries at once, there is also a multi_query (check the manual for info) for making multiple queries.
Say we want to make multiple inserts without the overhead of multiple executions, or maybe our last transaction should not have happened (for whatever reason, I mean your hardcoding
this).
CODE
// Turn off auto commit
$mysqli->autocommit(FALSE);
// Insert Values
$mysqli->query("INSERT INTO employees VALUES (1234, 'JOey')");
$mysqli->query("INSERT INTO employees VALUES (1235, 'Kyle')");
$mysqli->query("INSERT INTO employees VALUES (1236, 'Steuart')");
// Commit queries
$mysqli->commit();
// OOPS WE MADE A MISTAKE, We don't want these people!!
// Rollback last transaction
$mysqli->rollback();
Creating for Both!It is good habit to create applications that can easily switch between either adapter. The solution is a good framework, or Abstraction layer that supports usage of either adapter through configuration.
I have created a pretty neat abstraction layer just like this with some added goodies and put it in the snippets
here on the site, you can find the link at the bottom.
ConclusionThe new MYSQLi extension is extremely powerful, I have only scratched the surface with this tutorial, please visit the link at the bottom to the php manual for more information. I highly suggest using the new interface for obvious security and performance reasons. As well as the OO interface.
MYSQLi PHP Manual:
http://www.php.net/mysqliMYSQL Abstraction Pro:
http://www.dreamincode.net/code/snippet1942.htm
Introduction to MYSQLi and Prepared Statements, Newer, Faster, Secure, why aren't you using it?
8 Jun, 2008 - 11:09 PM
| 