getting php type from mysql type value

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

getting php type from mysql type value

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

mysql_field_type may be of use - mostly because of the user comments regarding php types vs mysql types :)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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;
	}
}
?>
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Post 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.
Post Reply