H:/Workspace/php-axiom/www/php-axiom/libraries/axiom/axMySQLObject.class.php

Go to the documentation of this file.

<?php
class axMySQLObject extends axBaseModel {
    
    protected $_table;
    
    protected $_structure;
    
    protected function _init ($statement) {
        if (isset($this->_statements[$statement]))
            return $this->_statements[$statement];
            
        $pieces  = array();
        $columns = array();
        foreach ($this->_structure as $column => $infs) {
            $columns[] = "`$column`";
            if ($column == $this->_idKey && strpos($infs['EXTRA'], 'auto_increment') !== false)
                continue;
            $pieces[]  = "`{$column}`=:{$column}";
        }
        
        switch ($statement) {
            case 'create':
                $query = "INSERT INTO {$this->_table} SET " . implode(',', $pieces);
                break;
                
            case 'retrieve':
                $query = "SELECT " . implode(',', $columns) . " " .
                                                 "FROM {$this->_table} " .
                                                 "WHERE `{$this->_idKey}`=:{$this->_idKey}";
                break;
                
            case 'update':
                $query = "UPDATE {$this->_table} SET " . implode(',', $pieces) . " " .
                         "WHERE `{$this->_idKey}`=:{$this->_idKey}";
                break;
                
            case 'delete':
                $query = "DELETE FROM {$this->_table} WHERE `{$this->_idKey}`=:{$this->_idKey}";
                break;
                
            default:
                throw new InvalidArgumentException("Invalid statement $statement");
        }
        
        return $this->_statements[$statement] = $this->_pdo->prepare($query);
    }
    
    public function __construct (PDO $pdo, $id = null) {
        $args = func_get_args();
        if (empty($args))
            throw new InvalidArgumentException('Missing parameters: `$pdo`, `$table`');
            
        if (count($args) === 1)
            throw new InvalidArgumentException('Missing parameter: `$table`');
            
        if (!$args[0] instanceof PDO)
            throw new InvalidArgumentException('`$pdo` parameter must be a valid PDO instance');
            
        if (!is_string($args[1]))
            throw new InvalidArgumentException('`$tablename` parameter must be string');
            
        if (empty($args[1]))
            throw new InvalidArgumentException('`$tablename` parameter cannot be empty');
            
        list($pdo,$tablename,$id) = $args + array(null,'',null);
        
        if (!$this->_getTableStructure($tablename))
            throw new RuntimeException("Cannot determine {$tablename} structure");
            
        $this->_table = self::_sanitizeTablename($tablename);
        parent::__construct($pdo, $id);
    }
    
    public static function all (PDO $pdo, array $search_params = array(), array $options = array()) {    
        if (func_num_args() < 4)
            throw new InvalidArgumentException('Missing fourth parameter');
        
        $arg = func_get_arg(3);
        
        if ($arg instanceof axModel) {
            $mysql_obj = $arg;
        }
        elseif (is_string($arg)) {
            try  {
                $mysql_obj = new self($pdo, $arg);
            }
            catch (Exception $e) {
                trigger_error("Unable to create `axMySQLObject` instance: " . $e->getMessage(), E_USER_WARNING);
                return false;
            }
        }
        else {
            throw new InvalidArgumentException("Fourth argument is expected to be a valid `axModel` instance or ".
                "string, " . gettype($arg) . " given");
        }
            
        if (!isset($mysql_obj))
            $mysql_obj = new self($table);
            
        $query  = "SELECT `" . implode('`,`', $mysql_obj->getColumns()) . "` FROM " . $mysql_obj->getTable();
        $query .= self::_generateWhereClause($search_params);
        $query .= self::_generateOptionClause($options);
        
        $stmt = $pdo->prepare($query);
        if ($stmt->execute($search_params)) {
            $stmt->setFetchMode(PDO::FETCH_INTO, $mysql_obj);
            
            if (PHP_VERSION_ID < 50200) {
                $cquery = preg_replace('~SELECT.*FROM~', 'SELECT COUNT(*) FROM', $query);
                $cstmt = $pdo->prepare($query);
                !empty($search_params) ? $cstmt->execute($search_params) : $cstmt->execute();
                $count = (int)$cstmt->fetchColumn();
                $it->setCount($count);
            }
            
            return new axPDOStatementIterator($stmt);
        }
        return false;
    }
    
    public function getTable () {
        return $this->_table;
    }
    
    public function getColumns () {
        return array_keys($this->_structure);
    }
    
    public function getStructure () {
        return $this->_structure;
    }
    
    protected function _getTableStructure ($table) {
        if (!is_string($table) || empty($table))
            throw new InvalidArgumentException("First parameter is expected to be valid string");
            
        $table = self::_sanitizeTablename($table);
            
        if ($stmt = Axiom::database()->query("DESC $table")) {
            $this->_structure = array();
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $column) {
                if (isset($column['Key']) && strpos($column['Key'], 'PRI') !== false)
                    $this->_idKey = $column['Field'];
                
                $this->_structure[$column['Field']] = array_change_key_case($column, CASE_UPPER);
            }
            return true;
        }
        return false;
    }
    
    protected static function _sanitizeTablename ($table) {
        return '`' . implode('`.`', explode('.', str_replace(array('`', ' '), '', $table))) . '`';
    }
    
    protected static function _generateSelectQuery ($tablename,
                                                    array $columns = array(), 
                                                    array $search_params = array(),
                                                    array $options = array()) {
        if (!$tablename)
            throw new InvalidArgumentException('`$tablename` cannot be empty');
                                                        
        if (empty($columns))
            $columns = '*';
        else
            $columns = '`' . implode('`,`', $columns) .  '`';
                                                       
        $query  = "SELECT {$columns} FROM " . self::_sanitizeTablename($tablename);
        $query .= self::_generateWhereClause($search_params);
        $query .= self::_generateOptionClause($options);
        
        return $query;
    }
    
    protected static function _generateInsertQuery ($tablename, array $columns) {
    }
    
    protected static function _generateUpdateQuery ($tablename, array $columns, array $search_params = array()) {
    }
    
    protected static function _generateDeleteQuery ($tablename, array $search_params = array()) {
    }
    
    protected static function _generateWhereClause (array $search_params) {
        if (!empty($search_params)) {
            $pieces = array();
            
            foreach ($search_params as $key => $value) {
                if (preg_match('~\s*(?<field>\w+)\s*(?<operator>.*)\s*~', $key, $matches)) {
                    $field    = $matches['field'];
                    $operator = $matches['operator'];
                    unset($search_params[$key]);
                    $search_params['field'] = $value;
                    $pieces[] = "`{$field}`{$operator}:{$field}";
                }
                else {
                    $pieces[] = "`{$key}`=:{$key}";
                }
            }
            
            return " WHERE " . implode(' AND ', $pieces);
        }
        return "";
    }
    
    protected static function _generateOptionClause (array $options) {
        $query = "";
        
        if (!empty($options['group by'])) {
            $pieces = array();
            
            foreach((array)$options['group_by'] as $field)
                $pieces[] = "`{$field}`";
            
            $query .= " GROUP BY ".implode(',' ,$pieces);
        }
        
        if (!empty($options['order by'])) {
            $pieces = array();
            
            foreach($options['order by'] as $field)
                $pieces[] = "`{$field}`";
            
            $query .= " ORDER BY ".implode(',' ,$pieces);
            
            if (isset($options['order by type']) 
             && in_array(strtoupper($options['order by type']), array('ASC', 'DESC')))
                $query .= " " . strtoupper($options['order by type']);
        }
        
        if (!empty($options['limit'])) {
            if (count($options['limit']) == 1) {
                $options['limit'] = (array)$options['limit'];
                $query .= " LIMIT {$options['limit'][0]}";
            }
            
            if (count($options['limit']) == 2) {
                $query .= " LIMIT {$options['limit'][0]},{$options['limit'][1]}";
            }
        }
        
        return $query;
    }
}