SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PDO getColumnMeta bug?

    Hi,

    I'm trying to get the correct PDO data type number (pdo_type) from PDO_Statement::getColumnMeta() but the only value I ever get is 2, which is PDO::PARAM_STR. Anyone know how to get this to work?

    PHP Code:
    $stmt $pdo->query('select * from my_table');
    $i 0;
    $fields = array();
    echo 
    '<pre>';
    while (
    $column $stmt->getColumnMeta($i++)) {
        
    print_r($column);


  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which database driver is that? SQLite is untyped.

  3. #3
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is from the Manual on this function:
    Warning

    This function is EXPERIMENTAL. The behaviour of this function, the name of this function, and anything else documented about this function may change without notice in a future release of PHP. Use this function at your own risk.
    I wouldn't use this function if I were you, explicitly because of that statement. Instead, use a "SHOW COLUMNS FROM [table]" query to retrieve all the column data. That function will then retrieve column information that you can then parse into the appropriate binding types. I actually had to do this myself a while ago, and I made a quick and dirty function for it that I'll share with you:

    Function to get column types:
    PHP Code:
        /**
         *    Automatically get column metadata
         */
        
    protected function getColumnMeta()
        {
            
    // Clear any previous column/field info
            
    $this->_fields = array();
            
    $this->_fieldMeta = array();
            
    $this->_primaryKey NULL;

            
    // Automatically retrieve column information if column info not specified
            
    if(count($this->_fields) == || count($this->_fieldMeta) == 0)
            {
                
    // Fetch all columns and store in $this->fields
                
    $columns $this->db->query("SHOW COLUMNS FROM " $this->getTableName(), PDO::FETCH_ASSOC);
                foreach(
    $columns as $key => $col)
                {
                    
    // Insert into fields array
                    
    $colname $col['Field'];
                    
    $this->_fields[$colname] = $col;
                    if(
    $col['Key'] == "PRI" && empty($this->_primaryKey)) {
                        
    $this->_primaryKey $colname;
                    }
                    
                    
    // Set field types
                    
    $colType $this->parseColumnType($col['Type']);
                    
    $this->_fieldMeta[$colname] = $colType;
                }
            }
            return 
    true;
        } 
    The function to parse the PDO returned column information
    PHP Code:
        /**
         *    Parse PDO-produced column type
         *    [internal function]
         */
        
    protected function parseColumnType($colType)
        {
            
    $colInfo = array();
            
    $colParts explode(" "$colType);
            if(
    $fparen strpos($colParts[0], "("))
            {
                
    $colInfo['type'] = substr($colParts[0], 0$fparen);
                
    $colInfo['pdoType'] = '';
                
    $colInfo['length']  = str_replace(")"""substr($colParts[0], $fparen+1));
                
    $colInfo['attributes'] = isset($colParts[1]) ? $colParts[1] : NULL;
            }
            else
            {
                
    $colInfo['type'] = $colParts[0];
            }
            
            
    // PDO Bind types
            
    $pdoType '';
            foreach(
    $this->_pdoBindTypes as $pKey => $pType)
            {
                if(
    strpos(' '.strtolower($colInfo['type']).' '$pKey)) {
                    
    $colInfo['pdoType'] = $pType;
                    break;
                } else {
                    
    $colInfo['pdoType'] = PDO::PARAM_STR;
                }
            }
            
            return 
    $colInfo;
        } 
    And the column mappings array:
    PHP Code:
        /**
         *    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 $_pdoBindTypes = 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
            
    ); 
    It's a little crude, but it gets the job done well.

    NOTE: This was used in a class, so you will need to make adjustments as needed.

  4. #4
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    Which database driver is that? SQLite is untyped.
    It's MySQL. I figured MySQL would work?

  5. #5
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Czaries View Post
    This is from the Manual on this function:
    Wow, thanks! I'm checking it out now.

    Matt

    EDIT, I meant thanks for posting the code!
    Last edited by mwmitchell; Aug 14, 2007 at 20:01.

  6. #6
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem - hope it helps you at least a little bit.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •