php中使用sqlcondition,把sql的拼写变成对象,基于pdo的占位符使用

posted by David Chen at April 1, 2008, 1:12 AM    

下面这个类,是用于php的sql condition, 可以减少在php开发中的sql拼写, 基于pdo的占位符用法,
动态根据condition的数量生成相应的占位符,自动拼到sql中,不支持 in (?) 这种sql, 一般的where
语句的条件都支持!
在看以下这个类的同时,最好同时看一下我的下一篇文章: SQLHelper

    /*
    $arr = SQLFilter::push_filter( new SQLFilter('and', 'cat_ID', '=', 100), $arr );
    $arr = SQLFilter::push_filter( new SQLFilter('and', 'category_count', '=', 0), $arr );
    $conn = new PDO( ... );
    $sql = SQLFilter::get_holders($arr, "SELECT * FROM wp_categories where 1=1");
    $stmt = SQLFilter::fill_statement(
                $conn->prepare( $sql ),
                $arr
                );
    $stmt->execute();
    */

    class SQLFilter {

        private $column;
        private $operation;
        private $value;
        private $logicals;
        private $type;

        const PLACEHOLDER = '?';
        const TYPE_UNDEFINE = 0;
        const TYPE_INT = PDO::PARAM_INT;
        const TYPE_STR = PDO::PARAM_STR;
        const TYPE_BOOL = PDO::PARAM_BOOL;
        const TYPE_LOB = PDO::PARAM_LOB;
        const TYPE_STMT = PDO::PARAM_STMT;
        const TYPE_INPUT_OUTPUT = PDO::PARAM_INPUT_OUTPUT;
        const TYPE_NULL = PDO::PARAM_NULL;

        public function SQLFilter( $logicals, $column, $operation, $value, $type = 0) {
            $this->logicals = $logicals;
            $this->column = $column;
            $this->operation = $operation;
            $this->value = $value;
            $this->type = $type;
// 指写为相应的类型,用于statement的填充
        }

        public static function push_filter( $sqlfilter, $filter_list = NULL ) {
// 因为sql condition是可能有多个的, 用链表或数组记起来.
            if( is_null( $filter_list ) || !is_array( $filter_list ) )
                $filter_list = array();
            array_push ( $filter_list, $sqlfilter );
            return $filter_list;
        }

        public static function get_holders( $filter_list, $sql = '' ) {
// 这里自动生成占位符的sql condition,
            $condition = '';
            foreach ( $filter_list as $filter ) {
                $condition .= ' ' . $filter->logicals;
                $condition .= ' ' . $filter->column;
                $condition .= ' ' . $filter->operation;
                $condition .= ' ' . self::PLACEHOLDER;
                $condition .= ' ';
            }
            return $sql . $condition;
        }

        public static function fill_statement( $pdo_statement, $filter_list ) {
// 这里给pdo的statement的占位符自动填充参数值
// 如果指定了参数的类型, 以指定形式添加参数,否则自动处理
            $idx = 0;
            foreach ( $filter_list as $filter ) {
                if( intval($filter->type) == self::TYPE_UNDEFINE )
                    $pdo_statement->bindValue( ++ $idx, $filter->value );
                else
                    $pdo_statement->bindValue( ++ $idx, $filter->value, $filter->type );
            }
            return $pdo_statement;
        }

        public static function get_parameters( $filter_list ) {
// 将参数值以对象数组形式返回,没什么用,
            $arr = array();
            foreach ( $filter_list as $filter ) {
                array_push ( $arr, $filter->value );
            }
            return $arr;
        }

    }