A little wrapper for php's mysql handlers

MysqlUtil.php

<?php /* A little wrapper for php's mysql handlers

Usage:
1) Drop this code on a page
2) Create a table as you like
3) Initialize an object like this: $db = new MysqlUtil( $db_host, $db_name, $db_user, $db_pass );
4) Run queries like this: 
$db->query( "select * from `my_table` where `field_name`='value';" ); 
5) Escape input using sprintf notation & logic like this:
$db->query( "insert into `my_table` ( `field1` ) values ( '%s' )", "my ' value" );

Example: see this in action http://github.com/erikeldridge/bbauth-to-oauth-example
License: Yahoo! BSD http://gist.github.com/375593
*/

class MysqlUtil {
    function __construct( $host, $db_name, $user_name, $password ){
        
        $this->db_name = $db_name;
        
        $this->db = mysql_connect( $host, $user_name, $password );
        if (!$this->db) {
            throw( new Exception( 'db host connection error'. mysql_error() ) );
        }
        if ( !mysql_select_db( $this->db_name ) ) {
            throw( new Exception( 'db selection error'. mysql_error() ) );
        }
        return $this;
    }
    
    function __destruct(){
        mysql_close($this->db);
    }
    
    function query( /* query, val1, val2, ... */ ){
        
        $args = func_get_args();
        $query = array_shift( $args );
        
        // if args left over, escape them and insert them into query using sprintf rules
        if ( $args ) {
            $escaped = array_map ( 'mysql_real_escape_string', $args );
            $query = vsprintf( $query, $escaped );
        }
        
        $result = mysql_query( $query );
        
        if ( mysql_error() ){
            throw( new Exception( 'db query error: '.mysql_error() ) );
        }
        
        // mysql_query returns true for success w/ some queries 
        if ( true === $result ) {
           return true;
        }
        
        // mysql_query returns resource for other types of queries         
        $rows = array();
        while ( $row = mysql_fetch_assoc( $result ) ) {
            $rows[] = $row;
        }
        return $rows;
    }
}
?>

test.php

<?php // some simple tests

error_reporting(E_ALL);

require 'MysqlUtil.php';

$db = new MysqlUtil('Localhost', 'table', 'username', 'password');

// setup
try {
    $results = $db->query(
        'create table `foo` ( 
            `key` int(32) auto_increment primary key, 
            `field1` text, 
            `field2` text, 
            `field3` text );'
    );
} catch ( Exception $e ) {
    printf( '<pre>%s</pre>', print_r( $e, true ) ); 
    die;
}

// test success insert (w/o escaping)
try {    
    $results = $db->query( sprintf( 
        "INSERT INTO `%s`.`foo` (`field1`, `field2`, `field3`) 
        VALUES ( '%s', '%s', '%s' );",
        $db->db_name, 'val1', 'val2', 'val3'
    ) );
    
    assert( true === $results );
    
    $results = $db->query( sprintf( 
        "SELECT * FROM `foo` 
        WHERE `field1` = 'val1' 
        AND `field2` = 'val2';",
        'asd123'
    ) );

    assert( 'val1' == $results[0]['field1'] && 'val2' == $results[0]['field2'] && 'val3' == $results[0]['field3'] );    

} catch ( Exception $e ) {
    printf( '<pre>%s</pre>', print_r( $e, true ) ); 
    die;
}

// test bad insert (w/o escaping)
try {    
    $results = $db->query( sprintf( 
        "INSERT INTO `%s`.`foo` (`badfield`, `field2`, `field3`) 
        VALUES ( '%s', '%s', '%s' );",
        $db->db_name, 'val1', 'val2', 'val3'
    ) );
    
    // the above should throw exception
    assert( false );
    
} catch ( Exception $e ) {
    assert( "db query error: Unknown column 'badfield' in 'field list'" == $e->getMessage() ); 
}

// test good insert (w/ escaping)
try {    
    $results = $db->query( 
        "INSERT INTO `$db->db_name`.`foo` (`field1`, `field2`, `field3`) 
        VALUES ( '%s', '%s', '%s' );",
        'val1', 'val2', "'drop tables;"
    );
    
    assert( true === $results );
    
    $results = $db->query( 
        "SELECT * FROM `foo` 
        WHERE `field1` = '%s'",
        "' OR ''='"
    );
    
    assert( count( $results ) === 0 );
    
} catch ( Exception $e ) {
    printf( '<pre>%s</pre>', print_r( $e, true ) ); 
    die;
}

// cleanup
try {
    $results = $db->query( 'drop table `foo`;' );
} catch ( Exception $e ) {
    printf( '<pre>%s</pre>', print_r( $e, true ) ); 
    die;
}
?>