Page 1 of 1

getting php type from mysql type value

Posted: Wed Sep 27, 2006 1:18 pm
by Luke
I am building an active record library. In my library, each field in a record is represented by an object. Each object has an array of rules that it must adhere to before $model->save() is allowed. One of the rules it must adhere to is a type-check. The problem is that there are a lot more types in mysql than there are in php. I'm just looking for advice on the best way to convert a type retrieved from mysql to a php type... here is the code if that doesn't make sense....

Code: Select all

class Mysql_Model_Field_Rule_type extends Mysql_Model_Field_Rule{
        /**
         * Type in format retrieved from a DESCRIBE TABLE query ie: "bigint" or "blob"
         * the width has been stripped from this value... ie: for int(11) the (11) part is
         * stripped before being sent to this class
         */
	private $type = '';
        /**
         * Class Constructor
         * @param string
         */
	public function __construct($type){
		$this->type = $type;
	}
        /**
         * Check if value adheres to this rule
         * @param string
         * @return bool
         */
	public function isValid($value){
		// What to do here
	}
}
I was considering just defining an array for each php type and filling them with corresponding mysql types like this:

Code: Select all

$types = array(
    'integer' => array('int', 'bigint', 'smallint', 'etc'),
    'string' => array('blob', 'text', 'etc')
);
And then just checking that array to validate... does this seem logical, or do you think there is a better way to accomplish this?

Posted: Wed Sep 27, 2006 2:04 pm
by Jenk
mysql_field_type may be of use - mostly because of the user comments regarding php types vs mysql types :)

Posted: Wed Sep 27, 2006 4:33 pm
by Luke
thanks man... here's what I've got now... if anybody has any suggestions, comments, etc. Fire away!

Code: Select all

<?php
class Mysql_Model_Field_Rule_type extends Mysql_Model_Field_Rule{
	/**
	 * Type in format retrieved from a DESCRIBE TABLE query ie: "bigint" or "blob"
	 * the width has been stripped from this value... ie: for int(11) the (11) part is
	 * stripped before being sent to this class
	 */ 
	private $type = '';
	public function __construct($type){
		$this->type = $this->convertType($type);
	}
	public function isValid($value){
		return ($this->type == gettype($value));
	}
	public function convertType($type){
		$ret = 'null';
		$phpTypes = array(
			'string' => array('CHAR', 'VARCHAR', 'TINYTEXT', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT', 'ENUM', 'SET', 'DATETIME','TINYBLOB', 'MEDIUMBLOB', 'LONGBLOB', 'BLOB', 'TIMESTAMP', 'YEAR', 'DATE', 'TIME'), 
			'integer' => array('TINYINT', 'SMALLINT', 'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT'),
			'double' => array('FLOAT', 'DOUBLE', 'DECIMAL', 'NUMERIC'),
		);
		foreach($phpTypes as $key => $search_array){
			if(in_array(strtoupper($type), $search_array)){
				$ret = $key;
			}
		}
		return $ret;
	}
}
?>

Posted: Sat Sep 30, 2006 5:46 pm
by Ollie Saunders
You're probably better of writing classes implementing a value object pattern (described in Sweat's "Guide to PHP Design Patterns" and also Beck's "TDD by Example" in greater depth) for a lot of these. Certainly ENUM and SET and some of the larger integer types that PHP can't store without BC.
Also look at the MySQL Documentation on DECIMAL, its not a float. I thought I knew how it worked but reading the documentation myself has left me very confused.

Posted: Sun Oct 01, 2006 5:15 am
by Mordred
Tsk, tsk, tsk...
If you start listening to the 'no premature optimisations' guys and code like that, you're going to end with a slow application with no bottleneck to optimise postmaturely... postmortem rather.

Code: Select all

foreach($phpTypes as $key => $search_array){ 
                        if(in_array(strtoupper($type), $search_array)){ 
                                $ret = $key; 
                        } 
                } 
                return $ret;
This:

Code: Select all

foreach($phpTypes as $key => $search_array){ 
                        if(in_array(strtoupper($type), $search_array)){ 
                                return  $key; 
                        } 
                } 
                return 'null';
or this:

Code: Select all

foreach($phpTypes as $key => $search_array){ 
                        if(in_array(strtoupper($type), $search_array)){ 
                                $ret = $key; 
                                break;
                        } 
                } 
                return $ret;

You may have potential troubles with the mapping of php and (my)sql types - depends on what you are doing with them. For example BIGINT is a 64 bit value, which cannot be represented with php's integer.