<?

/*
 * Class: SQL
 *
 * A class to act as a wrapper around the main PEAR SQL query methods.
 *
 * Use this class in conjuction with the SQL_Dictionary class to 
 * provide a neatly abstracted way of maintaining and separating
 * all of the SQL used in a project.
 *
 * Use this class to encapsulate all of your SQL and specifically
 * to query the Scores database table, for use in conjunction with
 * the Scoreboard class.
 *
 * SQL also provides so simple debugging from the query string - just
 * append '&dbg=1' to any URL to receive printed reports of any executed
 * SQL statements.
 * 
 * For this to work you'll need to enter your connection details at the
 * relevant line in the constructor. You'll also need to have PEAR installed
 * and accessible from somewhere in your include path.
 *
 * Distributed under the LGPL license:
 * http://www.gnu.org/licenses/lgpl.html
 *
 * Duncan Gough
 * 3rdSense.com
 *
 * Home  http://www.suttree.com
 * Work  http://www.3rdsense.com
 * Play! http://www.playaholics.com
 */

require_once( "PEAR/DB/DB.php" );
require_once( "SQL/SQL_Dictionary/SQL_Dictionary.php" );

class SQL extends SQL_Dictionary{

    // Constructor - initialize the dictionary and connect
    function SQL() {

        // Your connection details go here:
        $this->dsn = "mysql://" . $username . ":" . $password . "@" . $hostname . "/" . $database;

        parent::SQL_Dictionary();
        $this->_connect();

    }

    // Executes an SQL statement and returns a result set
    function prepareAndExecute( $sql, $bindParameters ) {

        $start = $this->getmicrotime();

        $sth = $this->db->prepare( $sql );

        $result = $this->db->execute( $sth, $bindParameters );

        if( DB::isError( $result ) )
            die( "Failed query => " . $this->_dump( $sql ) . "\n\nParams => " . $this->_dump( $bindParameters ) );

        $duration = $this->getmicrotime() - $start;

        if( $_GET[ "dbg" ] ) {

            echo "<p>Query: " . $this->db->last_query . "</p>";
            echo "<p>Parameters: " . $this->_dump( $bindParameters ) . "</p>";
            echo "<p>Time elapsed: " . $duration . "</p>";

        }
        
        if( is_object( $result ) ) {

            if( $result->numRows() == 1 ) {

                $result->fetchInto( $row );

                return $row;

            } else {
            
                while( $result->fetchInto( $row ) ) {

                        $data[] = $row;

                }

            }

            $result->free();
            
            return $data;

        } else {

            return $result;

        }

    }

    // Executes a simple SQL statement and returns the result set
    function query( $sql ) {

        $start = $this->getmicrotime();

        $result = $this->db->query( $sql );

        if( DB::isError( $result ) )
            die( "Failed query => " . $this->_dump( $sql ) );

        $duration = $this->getmicrotime() - $start;

        if( $_GET[ "dbg" ] ) {

            echo "<p>Query: " . $this->db->last_query . "</p>";
            echo "<p>Parameters: " . $this->_dump( $bindParameters ) . "</p>";
            echo "<p>Time elapsed: " . $duration . "</p>";

        }

        return $result;

    }

    // For debugging
    function getmicrotime() { 

        list( $usec, $sec ) = explode( " ",microtime() ); 
        return ( (float)$usec + (float)$sec ); 

    }

    // Establishes a database connection
    function _connect() {

        $this->db = DB::connect( $this->dsn );

        if( DB::isError( $this->db ) )
            die( "Failed connect => " . $this->_dump( $this->db ) );

        $this->db->setFetchMode( DB_FETCHMODE_ASSOC );

    }

    // For debugging
    function _dump( $data ) {

        ob_start();
        print_r( $data );
        $return = ob_get_contents();
        ob_end_clean();

        return $return;

    }
}


?>