PDO getColumnMeta bug?


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?

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

Which database driver is that? SQLite is untyped.

This is from the Manual on this function:


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:

	 *	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) == 0 || 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

	 *	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;
			$colInfo['type'] = $colParts[0];
		// PDO Bind types
		$pdoType = '';
		foreach($this->_pdoBindTypes as $pKey => $pType)
			if(strpos(' '.strtolower($colInfo['type']).' ', $pKey)) {
				$colInfo['pdoType'] = $pType;
			} else {
				$colInfo['pdoType'] = PDO::PARAM_STR;
		return $colInfo;

And the column mappings array:

	 *	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.

It’s MySQL. I figured MySQL would work?

Wow, thanks! I’m checking it out now.


EDIT, I meant thanks for posting the code! :slight_smile:

No problem - hope it helps you at least a little bit. :slight_smile: