PDO getColumnMeta bug?

PHP
1

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?


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

Which database driver is that? SQLite is untyped.

3

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:


	/**
	 *	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;
		}
		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:


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

It’s MySQL. I figured MySQL would work?

5

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

Matt

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

6

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