Location: PHPKode > scripts > MySQL Done Right > mysql-done-right/examples.php
  Class         : mysqli_ext
  Purpose       : Usage examples for mysqli_ext.
  Version       : 0.24
  Filename      : examples.php
  Author        : Daren Schwenke
  Date released : 2009-10-19 
  License       : http://www.gnu.org/licenses/lgpl.txt

# Set up constants for mysqli database connection.
define('DB_USERNAME', 'your_db_username');
# Next line when set to TRUE will enable caching of the prepared queries.  

# Require in the class.  Using require instead of include as we also open the 
# db connection.

# Other mysqli object methods will work as well.  If you need to do something
# like change your character set, you can do it against the normal handle.

# Examples.

# Prepare,bind,execute.  Returns statement handle.  Use for multiple row 
# returns.  Please see end of document for LIMIT and ORDER BY examples.
$some_sth = $db->pbind('SELECT someint,somestring FROM sometable WHERE someint = ?','i',$int);

# Statement handle is a normal mysqli handle.  Other built in mysqli methods 
# that are compatible with prepared statements will work as well.
$num_rows = $some_sth->num_rows(); # Get number of rows.
$some_sth->data_seek(9); # Jump to row number 9.

# Loop through that handle via object references.  Calling the fetch_object 
# method against the mysqli_stmt handle doesn't seem to work with prepared 
# queries.  This class implements it with a workaround.
while ( $row = $some_sth->fetch_object() ) {
  print $row->somestring;
  print $row->someint;

# How to use that handle to return arrays.
while ( $row = $some_sth->fetch_assoc() ) {
  print $row['somestring'];
  print $row['someint'];

# Single row Selects.  Prepare, bind, execute, retrieve.  Returns 1st row as an
# object reference.
if ( $obj = $db->psingle('SELECT someint,somestring FROM sometable WHERE someint = ?','i',$int) ) {
  print $obj->someint;
  print $obj->somestring;

# Inserts.  Returns last insert id as a scalar.
$last_insert_id = $db->pinsert('INSERT INTO sometable (somestring,someint) VALUES (?,?)','si',$string,$int);

# Updates.  Returns count of rows affected as a scalar.
$rows_affected = $db->pexecute('UPDATE sometable SET somestring = ? WHERE someint = ? LIMIT 1','si',$string,$int);

# Deletes.  Returns count of rows affected as a scalar.
$rows_affected = $db->pexecute('DELETE FROM sometable WHERE someint = ? LIMIT 1','i',$int);

# Count rows returned.  It's how I do true/false, without the need to retrieve 
# the results.  Returns count of rows returned as a scalar.
$row_returned = $db->prows('SELECT something FROM sometable WHERE somestring = ? AND someint >= ?', 'si' , $string,$int );


The ? placeholder in the query string can only be used for string literals.  
This basically means that things like LIMIT ? and ORDER BY ? will not work and 
will still have to be placed in the query portion.  Putting variables in the 
query portion effectively eliminates the security provided by bound parameters 
so this data MUST be sanitized.  Some simple examples of how I do this.
# For user provided input that is supposed to be an integer, ensure it is by 
# silently changing the the user input type.
settype($limit, 'integer');

# For user provided input destined for ORDER BY, I limit the user input to a 
# pre-defined list of specific valid answers.  It is more secure than a generic 
# regex, and generally faster anyway.

$valid_orders = array('someint','somestring','someint DESC,somestring ASC');
if ( in_array($unsafe_order,$valid_orders) ) { 
  $order = $unsafe_order; # Exact match to one of our valid_orders. 
} else { 
  $order = 'someint'; # You hacker!  Fall back to something.

# Now we can still do our queries with LIMIT and ORDER BY without worry of SQL 
# injection attacks.

$some_sth = $db->pbind('SELECT someint,somestring FROM sometable WHERE someint > ? ORDER BY ' . $order . ' LIMIT ' . $limit,'i',$int);
Return current item: MySQL Done Right