Location: PHPKode > scripts > Ultimate DB Access Wrapper > ultimate-db-access-wrapper/db.php
<?php
/* db.php
 *     The database class for all the database needs. Database access wrapper.
 * 
 * Bogdan Lupandin ~ (hide@address.com)
 *      Please leave this notice intact. Thank you.
 */

class db {

    // Variables used throughout the class for connection purposes
    var $user = 'user';                 // User used to connect to the MySQL Database
    var $host = 'host';                 // Host used to connect to the MySQL Database
    var $pass = 'password';             // Password used to connect to the MySQL Database
    var $dbname = 'database';           // The database name that would be selected after connection to the MySQL is made
    var $prefix = 'prefix';             // Prefix on all the tables in the database previously defined
    var $persistant = false;            // If we need to use a presistant connection, set this to true
    var $free_result = false;           // Clears the resource result after every call
    var $admin_debug = false;           // If the debugging should be created for the admin... ONLY if for admin
    
    // Configuration and feature variables used to trigger events
    var $mysql_link;                    // MySQL Connection link resource
    var $last_result;                   // Last result produced/ran through the class
    var $last_sql;                      // Last SQL used in the class
    var $data_css;                      // The CSS for the generated SQL Data Result Set (last_sql_data())
    
    // Allowed SQL query functions to be performed.
    var $sql_queries = array('SELECT', 'FROM', 'LEFT JOIN', 'JOIN', 'RIGHT JOIN', 'INNER JOIN', 'OUTER JOIN', 'OUTER RIGHT JOIN',
                             'OUTER LEFT JOIN', 'INNER RIGHT JOIN', 'INNER RIGHT JOIN', 'WHERE', 'ORDER BY', 'ASC', 'DESC', 'GROUP BY',
                             'HAVING', 'ON', 'AVG', 'COUNT', 'SUM', 'FIRST', 'LAST', 'MAX', 'MIN', 'UCASE', 'LCASE', 'MID', 'LEN',
                             'ROUND', 'NOW', 'FORMAT', 'ABS', 'SIGN', 'MOD', 'ROUND', 'POW', 'SQRT', 'LEAST', 'GREATEST', 'LOWER',
                             'UPPER', 'CONCAT', 'LENGTH', 'LTRIM', 'TRIM', 'RTRIM', 'SUBSTRING', 'DATEOB', 'USER', 'PRIVGROUPS',
                             'IF', 'COALESCE', 'UNIQUEKEY', 'TONUMBER', 'RAND', 'SELECT DISCTICT', 'UPDATE', 'DELETE', 'INSERT',
                             'UNION');
    
    // Allowed SQL functions (0 = Not Allowed; 1 = Allowed)
    var $all_funcs = array('CREATE' => 0,
                           'DROP' => 0,
                           'ALTER' => 0,
                           'CREATE INDEX' => 1,
                           'UPDATE' => 1,
                           'INSERT' => 1,
                           'SELECT' => 1
                           );
    
    /*
     * function connect([ string $dbname])
     *      @string $dbname - over-rides the $this->dbname if both are set...
     *                 otherwise used $this->dbname if set
     * 
     * Connects to MySQL and selects the database
     */

    function connect($dbname = null)
    {
        // Checking if we are already connected to MySQL
        if(is_resource($this->mysql_link) || @mysql_ping($this->mysql_link))
        {
            return true;
        }
        
        // Connecting to MySQL
        if($this->persistant)
        {
            // Persistant connect
            $this->mysql_link = @mysql_pconnect($this->host, $this->user, $this->pass);
        }
        else
        {
            // Normal Connect
            $this->mysql_link = @mysql_connect($this->host, $this->user, $this->pass);
        }
        
        // If we are still not connected, then die with some debugging script
        if(!is_resource($this->mysql_link))
        {
            // Generating the admin part of the debug if needed
            $admin_debug = null;
            if($this->admin_debug == true)
            {
                $admin_debug = <<<EOT
<p>The following MySQL Connection Credentials were passed:<br />
<br />
<strong>Username:</strong> {$this->user}<br />
<strong>Host:</strong> {$this->host}<br />
<strong>Password:</strong> {$this->pass}</p>
EOT;
            }
            
            // Killing the script with the debug script written
            die("<html>
   <head>
      	<title>MySQL Connection Error</title>
   </head>
<body>
<p>There was an error connecting to the database. The following things could go wrong:</p>
<ol>
    <li>If you are connecting to the local server:
        <ol>
            <li>Make sure the database name, host and username are correct
                <ol>
                    <li>The host would usually be <strong>localhost</strong> or <strong>127.0.0.1:3306</strong>, but instead of 3306, the server may sometimes use <strong>3307</strong> or <strong>3308</strong>.</li>
                    <li>Try the mysql_connect function raw. Sometimes the correct name, host and username is filled in the PHP.ini, and the function defaolts to those values if no arguments are passed to the function.</li>
                </ol>
            </li>
            <li>Make sure that the user is created with the correct password associated to it.</li>
            <li>Check for any possible spelling errors.</li>
        </ol>
    </li>
    <li>If you are connecting to a remote server
        <ol>
            <li>You may need to get in touch with the remote server's tech support... 
                <ol>
                    <li>...to ensure that you can get through its firewall. It is not necessarily enough to have your server number listed in the recipient site's cpanel remote access host list. It depends on how the server company has things set up.</li>
                    <li>...to find out what port number they are using for database connections, which may not be the defaolt used by mysql_connect</li>
                    <li>...If you are using ODBC, the host to which you are trying to connect may or may not have any ODBC drivers installed</li>
                    <li>...If you are working from a dynamic IP, they may be set up to accommodate it, or you may have to use a proxy. See <a href=\"http://forge.mysql.com/wiki/MySQL_Proxy\">http://forge.mysql.com/wiki/MySQL_Proxy</a>
                </ol>
            </li>
            <li>If you are working from a shared server yourself, the server number you were sent in the sign-up letter is probably NOT the server number you shoold be using to connect to a remote database. You need the server number of the machine on which your site is sitting, not your virtual account server number on that machine. You can get this from your own tech support.</li>
        </ol>
    </li>
</ol>
<p>The following MySQL Error was returned:<br />
<br />
" . mysql_error() . "</p>
$admin_debug
</body>
</html>");
        }
        
        // Selecting the Database
        $this->dbselect($dbname);
    }
    
    /*
     * function dbselect([ string $dbname])
     *      @string $dbname - over-rides the $this->dbname if both are set...
     *                 otherwise uses $this->dbname if set
     * 
     * Selects the database
     */
    
    function dbselect($dbname = null)
    {
        // Setting the database 
        $dbname = (($dbname == null) ? $this->dbname : $dbname);
        
        // Setting the returned placeholder to the database name used
        $this->returned = $dbname;
        
        // Selecting a database
        return mysql_select_db($dbname) or mysql_error();
    }
    
    /*
     * function close( void )
     *      Closes a MySQL Connection
     */
    
    function close()
    {
        // Resetting the variables
        $this->last_sql = null;
        $this->last_result = null;
        
        // Closing the MySQL connection
        if(is_resource($this->mysql_link))
        {
            $this->returned = mysql_close($this->mysql_link);
        }
        
        // Setting the MySQL Link variable to null
        $this->mysql_link = null;
    }
     
     /*
      * function restart( string $dbname)
      *     @string $dbname - Database to select once connected to MySQL
      * 
      * Restarts a connection to MySQL and selects a named database
      */
    
    function restart($dbname = null)
    {
        // Closing MySQL connection
        $this->close();
        
        // Connecting to MySQL and selecting a database
        $this->connect($dbname);
    }
    
    /*
     * function fetch_row( string $sql [, bool $type])
     *      @string $sql - the sql that would be querying the database
     *      @boolean $type - the type of return you would like to get
     *          1 - mysql_fetch_assoc (Default)
     *          2 - mysql_fetch_array
     *          3 - mysql_fetch_object
     *          4 - mysql_fetch_row
     * 
     *  Function that fetches the current row
     */
    
    function fetch_row($sql, $type = 1)
    {
        // Creating a result resource
        $results = $this->set_result_resource($sql, true);
        
        // Checking what type of result we want
        if($type == 1)
        {
            $result = mysql_fetch_assoc($results);
        }
        elseif($type == 2)
        {
            $result = mysql_fetch_array($results);
        }
        elseif($type == 3)
        {
            $result = mysql_fetch_object($results);
        }
        elseif($type == 4)
        {
            $result = mysql_fetch_row($results);
        }
        else
        {
            $result = mysql_fetch_assoc($results);
        }
        
        // Checking if we need to free the result resource
        if($this->free_result == true)
            $this->free_result();
        
        // Returning the result
        return $result;
    }
    
    /*
     * function fetch_row( string $sql [, bool $type])
     *      @string $sql - the sql that would be querying the database
     *      @boolean $type - the type of return you would like to get
     *          1 - mysql_fetch_assoc (Default)
     *          2 - mysql_fetch_array
     *          3 - mysql_fetch_object
     *          4 - mysql_fetch_row
     * 
     *  Function that fetches all of the rows
     */
    
    function fetch_rowset($sql, $type = 1)
    {
        // Creating a result resource
        $results = $this->set_result_resource($sql, true);
        
        // Initiating the result array
        $result = array();
        
        // Checking what type of result we want
        if($type == 1)
        {
            while($row = mysql_fetch_assoc($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 2)
        {
            while($row = mysql_fetch_array($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 3)
        {
            while($row = mysql_fetch_object($results))
            {
                $result[] = $row;
            }
        }
        elseif($type == 4)
        {
            while($row = mysql_fetch_row($results))
            {
                $result[] = $row;
            }
        }
        else
        {
            while($result = mysql_fetch_assoc($results))
            {
                $result[] = $row;
            }
        }
        
        // Checking if we need to free the result resource
        if($this->free_result == true)
            $this->free_result();
        
        // Returning the results
        return $result;
    }
    
    /*
     * function num_rows( string $sql [, bool $type])
     *      @string $sql - the sql that would be querying the database
     *      @boolean $type - the type of return you would like to get
     *          1 - mysql_num_fields
     *          [Anything else] - mysql_num_rows (Default)
     * 
     * Function that counts the number of rows there are from
     *  a query
     */
    
    function num_rows($sql, $type = 2)
    {
        // Setting the result resource
        $results = $this->set_result_resource($sql, true);
        
        // Getting the requested result
        $result = (($type == 1) ? mysql_num_fields($results) : mysql_num_rows($results));
        
        // Checking if we need to free the result resource
        if($this->free_result == true)
            $this->free_result();
        
        // Returning the result
        return $result;
    }
    
    /*
     * function _get_( string $what, string $from, string $where [, string $amount])
     *      @string $what - The column name you want to retrieve
     *      @string $from - The table from where you want the data to be
     *          retrieved from.
     *      @string $where - The where clause of a SELECT statement. Could
     *          be set to null if you don't want any
     *      @string $amount - Amount of results expected. If one, only one
     *          result would be returned, if it be greater than one, an array
     *          of all found results would be returned that is the [$what].
     * 
     * A simple function that creates the SQL... carries it out and returns the
     *  result(s).
     */
    
    function _get_($what, $from, $where, $amount = 1)
    {
        // Generating the SQL for the query
        $sql = array('SELECT' => $what,
                     'FROM' => $from);
        
        // Generating the WHERE clause
        if(!is_null($where))
        {
            if(is_array($where))
            {
                $where = $this->build_where($where, false);
                
            }
            
            // Merging the WHERE clause with the rest of the SQL array
            $sql = array_merge($sql, array('WHERE' => $where));
        }
        
        // Setting the generated SQL to $sql
        $sql = $this->build_key_query($sql);
        
        // Retrieving the number of results there are to the query
        $num = (($amount == 1) ? 1 : $this->num_rows($sql));
        
        // Checking if the $num is greater than 1
        if($num > 1)
        {
            $return = $this->fetch_rowset($sql) or die(mysql_error());
        }
        else
        {
            $return = $this->fetch_row($sql) or die(mysql_error());
        }
        
        // Returning the appropriate result (set).
        return (string) ((isset($return[$what])) ? $return[$what] : $return);
    }
    
    /*
     * function set_result_resource( string $sql)
     *      @string $sql - The SQL used for the result resource
     * 
     * Function that sets the result resource
     */
    
    function set_result_resource($sql = null, $return = false)
    {
        // Checking if we are connected to MySQL
        if(!is_resource($this->mysql_link))
        {
            $this->connect();
        }
        
        // Checking if the SQL is empty
        if(is_null($sql))
        {
            $sql = $this->get_last_sql();
        }
        
        // Getting the resource into a variable
        $resource = mysql_query($sql) or die(mysql_error());
        
        // Setting the last result variable
        $this->last_result = $resource;
        
        // Checking if the result needs to be returned
        if($return)
        {
            // Returning the SQL resource
            return $resource;
        }
        else
        {
            // Checking if the result resource was created successfully
            if($resource)
            {
                return true;
            }
        }
        return false;
    }
    
    /*
     * function free_result([ resource $result])
     *      @resource $result - The result resource to free
     * 
     * Function that frees a MySQL result set
     */
    
    function free_result($result = null)
    {
        // Checking if the resource result is checked. If not, using the last one set
        $result = ((is_null($result)) ? $this->last_result : $result);
        
        // Freeing the result set
        return mysql_free_result($result);
    }
    
    /*
     * function san_query( string|array $query)
     *      @string /or\ array $query - Query to be sanitized
     * 
     * Sanitizes a string or an array from possible injections.
     * Prepares a string to be an SQL
     */
    
    function san_query($query)
    {
        // Checking if the query is an array
        if(is_array($query))
        {
            // Initiating the sanitized query array
            $san_query = array();
            
            // Sanitizing the values in the array
            foreach($query as $value)
            {
                // Checking if the value is an array
                if(!is_array($value))
                {
                    $san_query[] = mysql_real_escape_string($value);
                }
                else
                {
                    $san_query[] =  $this->san_query($value);
                }
            }
        }
        else
        {
            // Sanitizing the value
            $san_query = mysql_real_escape_string($value);
        }
        
        // Returning the sanitized values
        return $san_query;
    }
    
    /*
     * function unsan_sql( string $query)
     *      @string $query - Query to prepare to be a string
     * 
     * Prepares a sanitized SQL query to be a string
     */
    
    function unsan_sql($query)
    {
        return stripslashes($query);
    }
    
    /*
     * function gen_allowed( void)
     * 
     * Function to generate allowed SQL functions. Returns them in an array.
     */
    
    function gen_allowed()
    {
        // Checking which SQL functions are allowed
        foreach($this->all_funcs as $key => $value)
        {
            // Checking if the SQL Function is allowed
            if($value == 1)
            {
                // Setting the return value
                $return[] = $key;
            }
        }
        
        // Returning the created array
        return ((count($return) > 0) ? $return : false);
    }
    
    /*
     * function build_key_query( array $sql)
     *      @array $sql - the SQL array
     * 
     * Function that builds queries using an array
     * Usage example...
     * 
     * $sql = array(
     *              'SELECT'    => '*',
     *              'FROM'      => '`database`.`table`',
     *              'JOIN'      => '`table2',
     *              'ON'        => '`table1`.`field1` = `table2`.`field2`',
     *              'WHERE'     => array(
     *                             '`field1`' => 'value1',
     *                             '`field2`' => 'value2'),
     *              'ORDER BY'  => '`field2`',
     *              'ASC'       => null
     * );
     */
     
    function build_key_query($sql)
    {
        // Checking if $sql is an array
        if(!is_array($sql))
        {
            trigger_error('The variable <strong>$sql</strong> is not an array ', E_USER_ERROR);
        }
        
        // Checking if $sql is a valid array
        if(!$this->rkey_exists($sql, $this->sql_queries))
        {
            trigger_error('The variable <strong>$sql</strong> is not a valid array ', E_USER_ERROR);
        }
        
        // Getting the allowed SQL functions
        $haystack = $this->gen_allowed();
        
        // Retrieving the first key in the array
        $key = $this->retaval($sql, 0);
        
        // Checking if the SQL function used is allowed
        if(!in_array($key, $haystack)) // if($all_funcs[$key] == 0)  <- Backup way
        {
            trigger_error($key . ' is not allowed to be used in SQL', E_USER_ERROR);
        }
        
        // Initiating the result variable
        $bquery = null;
        
        // Looping through the SQL array and creating the query
        foreach($sql as $type => $query)
        {
            // Retrieving the key from the array
            $key = $this->key_as_value($query);
            
            // Making sure we get the WHERE and build it appropriately
            if(is_array($query) && ($type == 'WHERE' || $type == 'where'))
            {
                $bquery .= $this->build_where($query);
            }
            else
            {
                $bquery .= ' ' . strtoupper($type) . ' ' . $query;
            }
        }
        
        // Trimming the query from any white-spaces
        $bquery = trim($bquery);
        
        // Returning the built query if it is valid
        return (($this->valid_query($bquery)) ? $bquery : false);
    }
    
    /*
     * function fkey_exists( array $needle, array $haystack)
     *      @array $needle - The array that is the search query
     *      @array $haystack - The array that would be searched
     * 
     * Function that searches the keys of the $needle array
     *  for the values in the $haystack array.
     */
    
    function rkey_exists($needle, $haystack)
    {
        // Checking if $needle or $haystack are arrays
        if(!is_array($needle) || !is_array($haystack))
        {
            trigger_error('The variable <strong>$needle</strong> or <strong>$haystack</strong> is not an array ', E_USER_ERROR);
        }
        
        // Setting the counter to be 0
        $i = 0;
        
        // Looping through the needle and setting the $stack to be the key
        foreach($needle as $stack => $dull)
        {
            // Checking if $stack exists in $haystack
            if(!in_array($stack, $haystack))
            {
                // $stack does not exist in $haystack... return false, then break.
                return false;
                break;
            }
            
            ++$i;
        }
        
        // All of the $stacks were in $haystack... return true
        return true;
    }
    
    /*
     * function retaval( array $var, int $akey [, boolean $array [, boolean $value [, boolean $numeric]]])
     *      @array $var - The array to retrieve the value/key from
     *      @integer $akey - The key to retrieve in numeric terms (1, 2...)
     *      @boolean $array - Determines if the result should come as an array with the key and the
                                value.
     *      @boolean $value - Set true if you want the value, set false if you want the key...
     *                          set to null if you want an array returned with the key and value
     *      @boolean $numeric - Set to true if you want the key to be numeric... otherwise set
     *                              to false if you want the key to be what it previously was
     * 
     * A function to retrieve a value/key from an array. Returns a string by default, or an
     *  array if you need the key to be numeric
     *  
     */
    
    function retaval($var, $akey, $array = false,  $value = false, $numeric = false)
    {
        // Setting some variables
        $i = 0;
        $num = count($var);
        
        // Looping though the array and choosing the key/value that was asked for
        foreach($var as $key => $val)
        {
            // Setting the return value to the current key/value
            if($array == false)
            {
                $return = (($value == false) ? $key : $val);
            }
            
            // Checking if we need the result as an array or a string
            if($array != false)
            {
                // Setting the key to be whatever it needs to be
                $key = (($numeric == false) ? $key : 0);
                
                // Setting the return array to be whatever it was meant to be
                $return[$key] = (($value == false) ? ((is_null($value)) ? $value : $key) : $val);
            }
            
            // Making sure we stop when we have the correct key/value
            if($i == $akey)
            {
                break;
            }
            
            // Incrementing the counter
            ++$i;
        }
        
        //  Returning the return value if there is any
        return (($i > $num) ? false : $return);
    }
    
    /*
     * function build_insert( @string $tbl_name, @array $values)
     *      @string $tbl_name = The table name into which we are going to insert the values
     *      @array $values = The values to insert into the tbl_name... has to be an associative
     *          array with column names as keys and their values set to their corresponding
     *          column names.
     * 
     * Creates an insert SQL statement
     */
    
    function build_insert($tbl_name, $values)
    {
        // Starting the INSERT statement generation
        $sql = "INSERT INTO `$tbl_name` (";
        
        // Counting the number of values we've got here
        $num_vals = count($values);
        
        // Initiating the counter to keep track at which value we're at
        $counter = 1;
        
        // Looping through each value and getting it's column name
        foreach($values as $column_name => $null)
        {
            // Generating the column names part of the INSERT statement
            $sql .= "`{$column_name}`";
            
            // Making sure we put commas where appropriate
            if($counter !== $num_vals)
            {
                $sql .= ', ';
            }
            
            // Increasing the counter
            ++$counter;
        }
        
        // Continuing the INSERT statement with VALUES
        $sql .= ") VALUES (";
        
        // Resetting the counter
        $counter = 1;
        
        // Looping through the values to retrieve the column values
        foreach($values as $column_value)
        {
            // Making sure we keep the datatype of the values
            if(is_string($column_value))
            {
                $sql .= "'{$column_value}'";
            }
            else
            {
                $sql .= (($column_value === null) ? 'null' : (($column_value === false) ? 'false' : $column_value));
            }
            
            // Making sure we put commas where appropriate
            if($counter !== $num_vals)
            {
                $sql .= ', ';
            }
            
            // Increasing the counter
            ++$counter;
        }
        
        // Finishing the INSERT statement generation
        $sql .= ")";
        
        // Returning the trimmed result
        return trim($sql);
    }
    
    /*
     * function build_insert( @string $tbl_name, @array $values)
     *      @string $tbl_name = The table name in which we are going to update the columns
     *      @array $values = The values to update in the tbl_name... has to be an associative
     *          array with column names as keys and their values set to their corresponding
     *          column names.
     * 
     * Creates an update SQL statement
     */

    function build_update($tbl_name, $values, $where = null)
    {
        // Starting the UPDATE statement generation
        $sql = "UPDATE `{$tbl_name}` SET ";
        
        // Counting number of values we've got
        $num_vals = count($values);
        
        // Initiating the counter
        $counter = 1;
        
        // Looping through the values to create the UPDATE sequence
        foreach($values as $column_name => $column_value)
        {
            // The column name
            $sql .= "`{$column_name}` = ";
            
            // Keeping the column value's datatype
            if(is_string($column_value))
            {
                $sql .= "'{$column_value}'";
            }
            else
            {
                $sql .= (($column_value === null) ? 'null' : (($column_value === false) ? 'false' : $column_value));
            }
            
            // Making sure we put commas where appropriate
            if($counter !== $num_vals)
            {
                $sql .= ', ';
            }
            
            // Increasing the counter
            ++$counter;
        }
        
        // Checking if we need a WHERE statement
        if(!is_null($where))
        {
            // Generating the WHERE statement
            $sql .= ' ' . $this->build_where($where);
        }
        
        // Returning the trimmed result
        return trim($sql);
    }
    
    /*
     * function build_where( array $w_fields)
     *      @array $w_fields - Short for 'where fields' it would be an associative
     *          array holding the field names as keys and the field values as values
     *          associated to their respective array key (Or the field name in this case).
     * 
     * A function to build a 'where' clause for SQL
     */
    
    function build_where($w_fields, $beg_clause = true)
    {
        // Counting the number of WHERE fields we've got to work with
        $num_fields = count($w_fields);
        
        // Setting the count variable to 1
        $count = 1;
        
        // Starting the WHERE generation
        $where = (($beg_clause == true) ? ' WHERE ' : null);
        
        if(is_array($w_fields))
        {
            // Generating the WHERE clause
            foreach($w_fields as $column_name => $column_value)
            {
                // Setting the field names with their associated field values
                $where .= "{$column_name} = '{$column_value}'";
                
                // Making sure we are putting the AND where it belongs
                if($count !== $num_fields)
                {
                    $where .= ' AND ';
                }
                
                // Increasing the count variable by 1 every loop
                ++$count;
            }
        }
        else
        {
            $where .= $w_fields;
        }
        
        // Returning a right trimmed WHERE clause
        return rtrim($where);
    }
    
    /*
     * function build_query( array $sql)
     *     @array $sql - the SQL array
     * 
     * A simple way to build a query using an array
     * Usage example...
     * 
     * $sql = array(
     *              '1' => '*',                     // SELECT
     *              '2' => '`database`.`table`',    // FROM
     *              '3' => "field = 'value'",       // WHERE
     *              '4' => 'value ASC'              // ORDER BY
     *              );
     *          - OR -
     * $sql = array('*','`database`.`table`', "field = 'value'", 'value ASC');
     * 
     * The SQL has to be in that order associated with
     * those keys for those SQL functions
     */
     
    function build_query($sql)
    {
        // Checking if $sql was an array
        if(!is_array($sql))
        {
            trigger_error('The variable <strong>$sql</strong> is not an array ', E_USER_ERROR);
        }
        
        // Chekcing if $sql was a valid array
        if(!$this->num_keys($sql))
        {
            trigger_error('The array <strong>$sql</strong> is not a valid array ', E_USER_ERROR);
        }
        
        // Checking the WHERE property
        $where = ((isset($sql[2])) ? ' WHERE ' . trim($sql[2]) : null);
        
        // Checking the ORDER BY property
        $orderby = ((isset($sql[3])) ? ' ORDER BY ' . trim($sql[3]) : null);
        
        // The generated SQL ready to be prepared and returned
        $sql = "SELECT {$sql[0]} FROM {$sql[1]} {$where} {$orderby}";
        
        // Checking if the built query is of correct format
        if($this->valid_query($sql))
        {
            // Returning the built and prepared SQL query
            return trim($sql);
        }
        else
        {
            // There was an error in the SQL Generation, return false
            return false;
        }
    }
    
    /*
     * function num_keys( array $key)
     *      @array $key - The array that would be checked upon
     * 
     * Checks if the array has numbered keys
     */
     
    function num_keys($key)
    {
        // Checking if $keys was an array
        if(!is_array($key))
        {
            trigger_error('The variable <strong>$key</strong> is not an array ', E_USER_ERROR);
        }
       
        // Looping through $sql with $keys being the key value
        foreach($key as $keys => $trash)
        {
            // Checking if $key (the $sql's key) is numeric
            if(!is_numeric($keys))
            {
                // The $key was not numeric... return false then break
                return false;
                break;
            }
        }
        
        // The key was numeric... return true
        return true;
     }
     
     /*
      * function get_last_sql( void )
      * 
      * Retrieves the last SQL
      */
      
    function get_last_sql()
    {
        return $this->last_sql;
    }
    
    /*
     * function get_last_result( void )
     * 
     * Retrieves the last RESULT resource
     */
    
    function get_last_result()
    {
        return $this->last_result;
    }
    
    /*
     * function start_transaction( void )
     * 
     * Starts a MySQL Transaction
     */
    
    function start_transaction()
    {
        // Starting a tramsaction
        if(!@mysql_query("BEGIN", $this->mysql_link))
        {
            return false;
        }
        return true;
    }
    
    /*
     * function rollback_action( void )
     * 
     * Function that undos the last SQL
     */
    
    function rollback_action()
    {
        // Undo-ing a transaction
        if(!@mysql_query("ROLLBACK", $this->mysql_link))
        {
            return false;
        }
        return true;
    }
    
    /*
     * function end_transaction( void )
     * 
     * Function that ends the MySQL transaction
     */
    
    function end_transaction()
    {
        // Ending transaction
        if(!@mysql_query("COMMIT", $this->mysql_link))
        {
            return false;
        }
        return true;
    }
    
    /*
     * function create_index( string|array $tbl_name [, string|array $idx_name [, boolean $silent]])
     *      @string /or\ array $tbl_name - The table name(s) to create the index on
     *      @string /or\ array $idx_name - The index name for the table names
     *      @boolean $silent - If set to true, the errors would be silenced and would
     *          be stored into the error variable.
     * 
     * Creates an index for table name(s) put into the script with the index name
     * either generated automatically (if none provided) or used.
     * 
     * Generated INDEX values are IDX_($tbl_name) (As a string... arrays are broken down to strings.
     */
    
    function create_index($tbl_name, $idx_name = null, $silent = false)
    {
        // Checking if we are allowed to create an index
        if($this->all_funcs['CREATE_INDEX'] != 1)
        {
            trigger_error("Current MySQL user could not create index on $tbl_name. ", E_USER_ERROR);
        }
        
        // Initiating the IDX array
        $idx = array();
        
        // Checking if submitted information are of correct datatype
        if(is_array($tbl_name) && is_null($idx_name) || !is_array($idx_name))
        {
            // Iterating through the $tbl_name array and adding the name to the array
            foreach($tbl_name as $value)
            {
                $idx[$value] = 'IDX_' . $value;
            }
        }
        
        // Checking if both variables are arrays
        if(is_array($idx_name) && is_array($tbl_name) && !is_null($idx_name))
        {
            // Counting number of values in $tbl_name and $idx_name
            $tbls = count($tbl_name);
            $idxs = count($idx_name);
            
            // Checking if $tbls equal $idxs
            if($tbls == $idxs)
            {
                // Creating the IDX array holding the information
                for($i = 0; $i<=$tbls; ++$i)
                {
                    $idx[$tbl_name[$i]] = $idx_name[$i];
                }
            }
            else
            {
                return false;
            }
        }
        
        // Checking if it's only one table to create index on
        if(!is_array($tbl_name) && !is_array($idx_name))
        {
            // Creating the IDX array for it.
            $idx[$tbl_name] = $idx_name;
        }
        
        // Checking if $idx_name is of illegal type
        if(!is_array($idx_name) || !is_null($idx_name) && is_array($tbl_name))
        {
            // Checking if the errors were silenced
            if($silent)
            {
                $this->error = 'The variable <strong>$idx_name</strong> is of illegal datatype in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
            }
            else
            {
                trigger_error('The variable <strong>$idx_name</strong> is of illegal datatype ', E_USER_ERROR);
            }
            return false;
        }
        
        // Initiating the SQL array
        $sql = array();
        
        // Filling the SQL array with SQL query data
        foreach($idx as $tbl => $idx)
        {
            $sql[] = "CREATE INDEX {$idx} ON {$tbl}";
        }
        
        // Carrying out the action
        foreach($sql as $query)
        {
            // Carrying out the SQL queries and making sure that they are not false
            if(!$this->set_result_resource($query, true))
            {
                $return = false;
                break;
            }
        }
        
        // Checking if there were any errors in the script
        if($return === false)
        {
            
            // Checking if the errors were silenced
            if($silent)
            {
                $this->error = mysql_error() . ' in <strong>' . __FILE__ . '</strong> on line <strong>' . __LINE__ . '</strong>.';
            }
            else
            {
                // Echoing out the MySQL Error
                trigger_error(mysql_error(), E_USER_ERROR);
            }
        }
        
        // Returning true if there were no errors
        if($return != false)
        {
            return true;
        }
    }
    
    /*
     * function last_sql_data([ boolean $linked [, string  $title [, boolean $return]]])
     *      @boolean $linked - Determines if the generated HTML should be a whole page
     *      @string $title - The title of the SQL Result set
     *      @boolean $return - Determines if the page should be returned or echoed
     * 
     * Creates a TABLE like result using CSS div's using
     * the last SQL used in the class
     */
    
    function last_sql_data($linked = false, $return = true, $title = 'SQL Results Data')
    {
        // Setting the starting time
        $time1 = microtime(true);
        
        // Retrieving the last sql
        $sql = (($this->valid_query($this->get_last_sql())) ? $this->get_last_sql() : null);
        
        // Checking if $sql is null
        if(is_null($sql))
        {
            trigger_error('The SQL for function last_sql_data() is invalid. Should be a valid SELECT query ', E_USER_ERROR);
        }
        
        // Creating a result resource
        $this->set_result_resource($sql);

        // Storing the CSS used for the class
        $this->data_css = "            h1
            {
                width: 100%;
                clear: both;
                margin: 0px;
                color: #ffffff;
                text-align: center;
                background-color: #333333;
            }
            
            h2
            {
                width: 100%;
                clear: both;
                color: #ffffff;
                text-align: center;
                background-color: #666666;
            }
            
            p {
                margin-top: 0px;
                margin-left: 10px;
            }
            
            .container
            {
                width: 100%;
                padding: 0px;
                margin-top: 30px;
                background-color: #eeeeee;
                border: 1px solid #000000;
            }
            
            .column_name
            {
                width: 20%;
                float: left;
                margin-right: -1px;
                border-top: 1px solid #000000;
            }
            
            .column_value
            {
                width: 80%;
                float: left;
                border-top: 1px solid #000000;
                border-left: 1px solid #000000;
            }";
        
        // Generating the first half of the page
        if($linked)
        {
            $page = "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Strict//EN\" 
   \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd\">
<html xmlns=\"http://www.w3.org/1999/xhtml\" lang=\"en\" xml:lang=\"en\">
    <head>
        <title>{$title}</title>
        <style type=\"text/css\">
        <!--
{$this->data_css}
        //-->
        </style>
    </head>
    <body>
";
        }
        else
        {
            $page = null;
        }
        
        // Setting the result resource variable
        $result = ((isset($this->last_result)) ? $this->last_result : $this->set_result_resource($sql, true));
        
        if($this->num_rows($sql) > 0)
        {
            // Pointing the MySQL seek pointer to the first result
            mysql_data_seek($result, 0);
        }
        
        // The result number
        $p = 1;
        
        // Getting the result form the last SQL
        while($row = mysql_fetch_assoc($result))
        {
            // The result number page HTML
            $page .= "        <div class=\"container\">
            <h1>Result #{$p}</h1>\n";
            
            // Creating the resulting page
            foreach($row as $column_name => $column_value)
            {
                // Formatting the column value to what we need
                $column_value = nl2br($this->spchars($column_value));
                
                // Making sure that empty results are still generated
                if($column_value == false)
                {
                    $column_value = '&nbsp;';
                }
                
                // Generating the main portion of the data table
                $page .= "            <div class=\"column_name\">
                <p><strong>{$column_name}</strong></p>
            </div>
            <div class=\"column_value\">
                <p>{$column_value}</p>
            </div>\n";
            }
            
            
            $page .= "            <p style=\"clear: both; height: 0px; margin-bottom: 0px;\" />
        </div>\n";
            
            // Incrementing the result number
            ++$p;
        }
        
        // Checking if we need to free the result resource
        if($this->free_result == true)
            $this->free_result();
        
        // Finishing off the generation of the page
        if($linked)
        {
            $page .= "
        <h2>Page generated in {TIME} seconds.</h2>
    </body>
</html>";
        }
        else
        {
            $page .= "        <h2>Page generated in {TIME} seconds.</h2>\n";
        }
        // Setting the ending time
        $time2 = microtime(true);
        
        // Determining how long it took to generate the page
        $time = $time2 - $time1;
        
        // Replacing the {TIME} to be the time it took the page to generate
        $page = str_replace('{TIME}', substr($time, 0, -10), $page);
        
        // Checking if the page should be returned or echoed
        if($return)
        {
            // Returning the generated page
            return $page;
        }
        else
        {
            // Printing the generated page on the screen
            echo $page;
        }
    }
    
    /*
     * function valid_query( string $query)
     *      @string $query - The query to check if it's a valid SQL query
     * 
     * Checks if the given query is a valid SQL query (SELECT, UPDATE, INSERT, or DELETE)
     */
    
    function valid_query($query)
    {
        // Checking if the query is a valid SELECT query
        if(preg_match("#(SELECT\s[\w\*\`\')\(\,\s]+\sFROM\s[\w`\']+)| (UPDATE\s[\w`\']+\sSET\s[\w\,\'\=\`\']++\sWHERE\s[\w`\']+)| (INSERT\sINTO\s[\d\w\'\`]+[\s\w\d\`\')\(\,]*\sVALUES\s\([\d\w\'\,\)\'\`]+)| (DELETE\sFROM\s[\d\w\'\=\'\`]+)#i", $query))
        {
            return true;
        }
        return false;
    }
    
    /*
     * function spchars( string $text)
     *      @string $text - The text to convert some special characters
     * 
     * Function that converts certain HTML Characters.
     */
    
    function spchars($text)
    {
        // Conversion map
        $conversion_map = array(
            '<' => '&lt;',
            '>' => '&gt;'
        );
        
        // Returning the converted string
        return trim(strtr($text, $conversion_map));
    }
    
    /*
     * function key_as_value( string|array $array)
     *      @string|array $array - The array whose keys should be put as values
     * 
     * Puts the keys as value in an array
     */
    
    function key_as_value($array)
    {
        // Initiating the return array
        $return = array();
        
        // Checking if the 'key' is an array
        if(is_array($array))
        {
            // Going through the array and making the keys be the values
            foreach($array as $key => $value)
            {
                $return[] = $key;
            }
        }
        else
        {
            $return[] = $array;
        }
        
        // Returning the made array
        return $return;
    }
}
?>
Return current item: Ultimate DB Access Wrapper