Classe MySQL

Bom, vou deixar aqui a classe de Database que eu uso. Sei que já tem várias, mas acho que mais uma não faz mal :)

Eu implementei somente o que estava precisando nela, daí ficou pouca coisa (um CRUD simples, pra ser mais exato), mas de qualquer forma, ela ta aí pra quem quiser estudar/usar.

<?php

/**
* Database Access Object
*
* @package Database
* @author Raphael C. <raphael@engenhosweb.com.br>
* @copyright 2011 Raphael C.
* @license http://creativecommons.org/licenses/by/3.0/ Creative Commons Attribution 3.0 Unported License.
* @version SVN: $Id$
*/
class Database {

/**
* @access protected
* @var object
*/

 protected $_dbh ;

 /**
 * @var string
 */
 var $last_query ;

 /**
 * @var int
 */
 var $insert_id ;

 /**
 * @var int
 */
 var $affected_rows ;

 /**
 * @var string
 */
 var $charset ;

 /**
 * @var bool
 */
 var $debug ;

 /**
 * @var array
 */
 var $trace ;

 // -------------------------------------------------------------------------------

 /**
 * Connects to the database server and selects a database
 *
 * @return void
 */
 function __construct( $host, $username, $passwd, $dbname, $charset = null, $debug = null ) {
 /** Create a new mysql connection */
 $this->_dbh = @mysql_connect( $host, $username, $passwd ) ;
 @mysql_select_db( $dbname, $this->_dbh ) ;

 /** Error reporting */
 if ( mysql_errno( $this->_dbh ) ) {
 throw new RuntimeException( mysql_error( $this->_dbh ), mysql_errno( $this->_dbh ) ) ;
 return ;
 }

 /** Defines charset */
 $this->debug( $debug ) ;
 $this->charset( $charset ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * PHP5 style destructor and will run when database object is destroyed.
 *
 * @return void
 */
 function __destruct() {
 if ( $this->_dbh ) {
 mysql_close( $this->_dbh ) ;
 }

 return true ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Sets the default client character set
 *
 * @param string $charset <p>
 * The charset to be set as default.
 * If ommited, this function will only return the current charset.
 * </p>
 * @return mixed Returns the current charset on success or false on failure.
 */
 function charset( $charset = null ) {
 if ( is_null( $charset ) == false ) {
 $set = function_exists( 'mysql_set_charset' ) ?
 mysql_set_charset( $charset, $this->_dbh ) : @$this->query( $this->bind_params( 'SET NAMES %s;', $charset ) ) ;

 if ( $set == false ) {
 throw new UnexpectedValueException( 'Invalid or not supported character set <code>' . $charset . '</code>' ) ;
 return false ;
 }

 $this->charset = $charset ;
 }

 return $this->charset ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Sets the debug mode
 *
 * @param string $debug <p>
 * The charset to be set as default.
 * If ommited, this function will only return the current charset.
 * </p>
 * @return mixed Returns the current charset on success or false on failure.
 */
 function debug( $debug = null ) {
 if ( is_null( $debug ) == false ) {
 $this->debug = $debug ;
 }

 return $this->debug ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Executes a mysql query.
 *
 * @param string $query The query string.
 * Data inside the query should be properly escaped.
 * @return mixed Returns the insert id on INSERT, affected rows on DELETE|UPDATE|REPLACE|ALTER or
 * a mysqli_result instance for another kinds of queries.
 */
 function query( $query ) {
 $dbh = &$this->_dbh ;

 $start = $this->timer() ;
 $q = @mysql_query( $query, $dbh ) ;
 $time = $this->timer() - $start ;

 /* Save last made query */
 $this->last_query = $query ;

 if ( mysql_errno() === 0 ) {
 $return = null ;
 /* Check if statement returns another kind of value */
 if ( preg_match( "/^\\s*(DELETE|UPDATE|INSERT|REPLACE|ALTER) /i", $query ) == true ) {
 /* Take note of the insert id */
 if ( preg_match( "/^\\s*(INSERT|REPLACE) /i", $query ) == true ) {
 $this->insert_id = mysql_insert_id( $dbh ) ;
 }

 /* Number of affected rows */
 $return = $this->affected_rows = mysql_affected_rows( $dbh ) ;
 } else {
 /* The MySQL result itself */
 $return = $q ;
 }

 if ( $this->debug == true ) {
 $this->trace[ ] = array (
 'time' => $time,
 'query' => $query,
 'affected_rows' => $this->affected_rows,
 ) ;
 }

 return $return ;
 }

 /* Error reporting. */
 throw new ErrorException( mysql_error( $dbh ), mysql_errno( $dbh ), E_USER_WARNING ) ;
 return false ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Prepares a SQL query for safe execution.
 *
 * This function only supports a small subset of the sprintf syntax; it only supports %d (decimal number), %f (float), %s (string).
 * Does not support sign, padding, alignment, width or precision specifiers.
 *
 * @uses Database::bind_params_array()
 *
 * @param string $query Query statement with sprintf()-like placeholders
 * @param mixed $args First variable to substitute into the query's placeholders
 * @param mixed $... Further variables to substitute into the query's placeholders
 *
 * @return string Sanitized query string, false if there is an error
 */
 function bind_params( $query ) {
 $values = func_get_args() ;
 array_shift( $values ) ;

 return $this->bind_params_array( $query, $values ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Prepares a SQL query for safe execution.
 *
 * This function only supports a small subset of the sprintf syntax; it only supports %d (decimal number), %f (float), %s (string).
 * Does not support sign, padding, alignment, width or precision specifiers.
 *
 * @see Database::bind_params()
 *
 * @param string $query Query statement with sprintf()-like placeholders
 * @param array $args The array of variables to substitute into the query's placeholders
 *
 * @return string Sanitized query string, false if there is an error
 */
 function bind_params_array( $query, array $args ) {
 /* Escape strings */
 array_walk( $args, array ( &$this, '_escape_string' ) ) ;

 $query = str_replace( array ( "'%s'", '"%s"' ), '%s', $query ) ;
 $query = preg_replace( '|(?<!%)%s|', "'%s'", $query ) ;

 return @vsprintf( $query, $args ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Insert a row into a table.
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $data Data to insert (in column => value pairs).
 * Both $data columns and $data values should be "raw" (neither should be SQL escaped).
 * @return mixed The number of rows inserted, or false on error.
 */
 function insert( $table, array $data ) {
 return $this->_insert_replace( $table, $data, 'INSERT' ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Replace a row into a table.
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $data Data to insert (in column => value pairs).
 * Both $data columns and $data values should be "raw" (neither should be SQL escaped).
 * @return mixed The number of rows inserted, or false on error.
 */
 function replace( $table, array $data ) {
 return $this->_insert_replace( $table, $data, 'REPLACE' ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Update a row in the table
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $data Data to update (in column => value pairs).
 * Both $data columns and $data values should be "raw" (neither should be SQL escaped).
 * @param array $where A named array of WHERE clauses (in column => value pairs).
 * Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
 * @param int $limit Limit of rows to be updated
 * @return mixed The number of rows updated, or false on error.
 */
 function update( $table, array $data, array $where = array ( ), $limit = 0 ) {
 $values = array ( ) ;

 /* Check var type and assign %s for string, %f for float and %d for int */
 foreach ( $data as $field => $value )
 $values[ ] = '`' . $field . '`=' . $this->_get_type( $value ) ;

 /* Compose the SQL and execute. */
 $query = 'UPDATE `' . $table . '` SET ' . join( ', ', $values ) ;

 $query .= sizeof( $where ) > 0 ?
 $this->_where( $where ) : '' ;

 $query .= $limit > 0 ?
 ' LIMIT ' . $limit : '' ;

 $query .= ' ;' ;

 $query = $this->bind_params_array( $query, array_merge( $data, $where ) ) ;
 return $this->query( $query ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Retrieve a row in the table
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $where A named array of WHERE clauses (in column => value pairs).
 * Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
 * @param int $limit Limit of rows to be deleted
 * @return type
 */
 function select( $table, $where = array ( ), $limit = 0 ) {
 return $this->_select_delete( $table, $where, $limit, 'SELECT' ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Delete a row in the table
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $where A named array of WHERE clauses (in column => value pairs).
 * Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
 * @param int $limit Limit of rows to be deleted
 * @return type
 */
 function delete( $table, $where = array ( ), $limit = 0 ) {
 return $this->_select_delete( $table, $where, $limit, 'DELETE' ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Helper function for delete and select.
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $where A named array of WHERE clauses (in column => value pairs).
 * Multiple clauses will be joined with ANDs. Both $where columns and $where values should be "raw".
 * @param int $limit Limit of rows to be deleted
 * @return type
 */
 private function _select_delete( $table, $where, $limit, $type ) {
 $type = strtoupper( $type ) ;
 if ( in_array( $type, array ( 'DELETE', 'SELECT' ) ) == false ) {
 throw new InvalidArgumentException( 'insert_replace() 3rd parameter should be equal to INSERT or REPLACE.' ) ;
 return ;
 }

 /* Compose the SQL and execute. */
 $query = $type . ' FROM `' . $table . '` ' ;

 $query .= sizeof( $where ) > 0 ?
 $this->_where( $where ) : '' ;

 $query .= $limit > 0 ?
 ' LIMIT ' . $limit : '' ;

 $query .= ' ;' ;

 $query = $this->bind_params_array( $query, $where ) ;
 return $this->query( $query ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Helper function for insert and replace.
 *
 * @see Database::query()
 * @see Database::bind_params_array()
 *
 * @param string $table Table name
 * @param array $data Data to update (in column => value pairs).
 * Both $data columns and $data values should be "raw" (neither should be SQL escaped).
 * @return mixed The number of rows updated, or false on error.
 */
 private function _insert_replace( $table, array $data, $type ) {
 $type = strtoupper( $type ) ;
 $fields = array_keys( $data ) ;

 if ( in_array( $type, array ( 'INSERT', 'REPLACE' ) ) == false ) {
 throw new InvalidArgumentException( 'insert_replace() 3rd parameter should be equal to INSERT or REPLACE.' ) ;
 return ;
 }

 /* Check var type and assign %s for string, %f for float and %d for int */
 $values = array_map( array ( &$this, '_get_type' ), $data ) ;

 /* Compose the SQL and execute. */
 $query = $this->bind_params_array( $type . ' INTO `' . $table . '` (`' . join( '`, `', $fields ) . '`) VALUES (' . join( ', ', $values ) . ');', $data ) ;
 return $this->query( $query ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * {@internal Missing Description}}
 *
 * @param type $message
 * @param type $code
 */
 function get_error() {
 return array (
 'error' => mysql_error( $this->_dbh ),
 'errno' => mysql_errno( $this->_dbh ),
 ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * Escapes special characters in a string for use in a SQL statement
 *
 * @param string $unescaped_string The string that is to be escaped.
 * @return string the escaped string, or false on error.
 */
 function _escape_string( $string ) {
 if ( $this->_dbh )
 return mysql_real_escape_string( $string, $this->_dbh ) ;

 else
 return addslashes( $string ) ;
 }
 // -------------------------------------------------------------------------------

 /**
 * {@internal Missing Description}}
 * @param type $data
 * @return type
 */
 private function _where( $data ) {
 $where = array ( ) ;
 foreach ( $data as $field => $value )
 $where[ ] = '`' . $field . '`=' . $this->_get_type( $value ) ;

 return ' WHERE (' . join( ' AND ', $where ) . ') ' ;
 }
 // -------------------------------------------------------------------------------

 /**
 * {@internal Missing Description}}
 *
 * @param mixed $var Variable
 * @return string Type of variable
 */
 private function _get_type( $var ) {
 switch ( 1 ) {
 case is_bool( $var ):
 case is_long( $var ):
 return '%d' ;
 break ;

 case is_double( $var ):
 return '%f' ;
 break ;

 case is_string( $var ):
 default:
 return '%s' ;
 break ;
 }
 }
 // -------------------------------------------------------------------------------

 /**
 * {@internal Missing Description}}
 *
 * @return type
 */
 function timer() {
 list($usec, $sec) = explode( ' ', microtime() ) ;
 return ( float ) $usec + ( float ) $sec ;
 }
 // -------------------------------------------------------------------------------
}