Skip to content
Snippets Groups Projects
databaseTable.php 3.61 KiB
Newer Older
<?php

class DatabaseTable
{

    public $fields      = array();
    public $field_meta  = array();
    public $primary_key = NULL;

    private $database;
    private $table_name;

    public function __construct( $database, $table_name )
    {
        $this->database   = $database;
        $this->table_name = $table_name;
    }

    /**
    * Will attempt to bind columns with datatypes based on parts of the column type name
    * Any part of the name below will be picked up and converted unless otherwise sepcified
    *     Example: 'VARCHAR' columns have 'CHAR' in them, so 'char' => PDO::PARAM_STR will convert
    * all columns of that type to be bound as PDO::PARAM_STR
    * If there is no specification for a column type, column will be bound as PDO::PARAM_STR
    */
    protected $pdo_bind_types = array(
                                'char'   => PDO::PARAM_STR,
                                'int'    => PDO::PARAM_INT,
                                'bool'   => PDO::PARAM_BOOL,
                                'date'   => PDO::PARAM_STR,
                                'time'   => PDO::PARAM_INT,
                                'text'   => PDO::PARAM_STR,
                                'blob'   => PDO::PARAM_LOB,
                                'binary' => PDO::PARAM_LOB
    );


    /**
    * Parse PDO-produced column type
    * [internal function]
    */
    protected function parseColumnType($col_type)
    {
        $col_info = array();
        $col_parts = explode(" ", $col_type);
        if( $fparen = strpos($colParts[0], "(") )
        {
            $col_info['type']       = substr($col_parts[0], 0, $fparen);
            $col_info['pdo_type']   = '';
            $col_info['length']     = str_replace(")", "", substr($col_parts[0], $fparen+1));
            $col_info['attributes'] = isset($col_parts[1]) ? $col_parts[1] : NULL;
        }
        else
        {
            $col_info['type'] = $col_parts[0];
        }

        // PDO Bind types
        $pdo_type = '';
        foreach($this->pdo_bind_types as $pKey => $pType)
        {
            if(strpos(' '.strtolower($col_info['type']).' ', $pKey))
            {
                $col_info['pdo_type'] = $pType;
                break;
            }
            else
            {
                $col_info['pdo_type'] = PDO::PARAM_STR;
            }
        }
        return $col_info;
    }


    /**
    * Automatically get column metadata
    */
    protected function getColumnMeta($refresh = false)
    {
        if( $refresh )
        {
            // Clear any previous column/field info
            $this->fields      = array();
            $this->field_meta  = array();
            $this->primary_key = NULL;
        }

        // Automatically retrieve column information if column info not specified
        if( count($this->fields) == 0 || count($this->field_meta) == 0 ) 
        { 
            // Fetch all columns and store in $this->fields
            // TODO prepared statement ...
            $columns = $this->database->query("SHOW COLUMNS FROM " . $this->table_name, PDO::FETCH_ASSOC);
            foreach( $columns as $key => $col )
            {   
                // Insert into fields array
                $col_name = $col['Field'];
                $this->fields[$col_name] = $col;
                if( $col['Key'] == "PRI" && empty($this->primary_key) )
                {   
                    $this->primary_key = $col_name;
                }   

                // Set field types
                $col_type = $this->parseColumnType($col['Type']);
                $this->field_meta[$col_name] = $col_type;
            }   
        }   
        return true;
    }


}