Page 1 of 1

Introduction to MYSQLi and Prepared Statements Newer, Faster, Secure, why aren't you using it? Rate Topic: -----

#1 joeyadms  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 41
  • View blog
  • Posts: 178
  • Joined: 04-May 08

Posted 09 June 2008 - 12:09 AM

*
POPULAR

Introduction to Mysql Improved (mysqli) and Prepared Statements

What 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 Style
Basic Usage Example:
// 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
// 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 Way
Basic Usage:
// 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:
$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).
// 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.


Conclusion
The 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/mysqli
MYSQL Abstraction Pro: http://www.dreaminco...snippet1942.htm

Is This A Good Question/Topic? 5
  • +

Replies To: Introduction to MYSQLi and Prepared Statements

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Posted 14 July 2010 - 07:59 AM

Good tutorial : - ). I am fairly new to PHP(and the IT industry in general) and am working at my first job using it. I learned from tutorials and from the code on the project I am working on, and it is still done the old procedural way. I have been meaning to start learning to use mysqli since I much prefer an OO approach. I intend on using it for my next personal project and this was a good overview, thanks much :bigsmile:
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,179
  • Joined: 08-June 10

Posted 16 July 2010 - 02:39 PM

too bad that the Prepared Statements in MySQLi donít support fetch options like the regular query does. :(
Was This Post Helpful? 0
  • +
  • -

#4 ReohnXXV  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 25
  • Joined: 25-April 14

Posted 30 June 2014 - 09:03 AM

Great tutorial! Helped me work through some mysqli implementations for a recent project! The only issue I found when following the code examples is where you cycle through the results. I don't know if things changed since this was posted but the part where you call

$mysqli->fetch_object($result)



did not work for me. Instead I had to call fetch_object on the $result like this

$result->fetch_object()



which worked perfectly.

Thanks!
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,179
  • Joined: 08-June 10

Posted 01 July 2014 - 12:57 AM

must have been a glitch from the procedural style where you call mysqli_fetch_object($result).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1