Active Record - advice and features

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

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

Active Record - advice and features

Post by Luke »

I put together a few classes for an active-record system I am working on. I realize active record has some issues, but the idea sounded interesting so I thought I'd try building one. I would like some critique on how I'm doing so far, and then maybe some advice on features that would make it useful to more people than just myself.

My classes make use of D11wqt's Mysql Iterator... slightly modified of course

Here is the main class MC2_Mysql_Model:

Code: Select all

<?php
class MC2_Mysql_Model{
	/**
	 * Mysql connection class
	 * Must be set statically to a connected MC2_Mysql object
	 * @static
	 */
	private static $mysql;
	/**
	 * Table's primary key
	 * @string
	 */
	protected $primaryKey;
	/**
	 * Name of associtated table
	 * @string
	 */
	protected $table;
	/**
	 * Fields in this table
	 * @array
	 */
	protected $fields = array();
	/**
	 * Constructor
	 * @param Mysql connection class
	 */
	public function __construct(){
	
		if(!self::$mysql){
			throw new MC2_Mysql_Model_Exception('You must set Mysql_Model::mysql to a Mysql object to use Mysql_Model');
			return;
		}
		$this->defineFields();
		
	}
	/**
	 * Sets static $mysql property to a MC2_Mysql object
	 * @param Mysql connection class
	 */
	static public function setConnection($Mysql){
	
		if($Mysql instanceof MC2_Mysql){
			self::$mysql = $Mysql;
			return;
		}
		throw new MC2_Mysql_Model_Exception('You must set Mysql_Model::mysql to a Mysql object to use Mysql_Model');
		
	}
	/**
	 * Load field definitions
	 */
	protected function defineFields(){
	
		$sql = "SHOW COLUMNS FROM `" . $this->table . "`";
		$result = self::$mysql->query($sql);
		for($result->first(); !$result->end(); $result->next()){
			$field = new MC2_Mysql_Model_Field(
				$result->Field,
				$result->Type,
				$result->Null,
				$result->Key,
				$result->Default,
				$result->Extra
			);
			$this->fields[$result->Field] = $field;
			
			if($field->isPk()) $this->primaryKey = $result->Field;
			
		}
		
	}
	/**
	 * Get total rows in database
	 * @returns integer
	 */
	public function count($conditions=null){
	
		$sql = "SELECT COUNT(*) as `count` FROM `" . $this->table . "`";
		if($conditions) $sql .= "WHERE " . $conditions;
		$result = self::$mysql->query($sql);
		return $result->count;
		
	}
	/**
	 * Loads a record
	 * @param string $conditions Where clause (optional)
	 * @param array $fields fields to load (optional)
	 */
	public function load($conditions, $fieldsArray=null){
		
		$select_fields = is_array($fieldsArray) ? implode(",", $fieldsArray) : '*';
		$sql = "SELECT " . $select_fields . " FROM `" . $this->table . "` WHERE " . $conditions . " LIMIT 1";
		$results = self::$mysql->query($sql);
		
		if($results->length()){
			foreach($this->fields as $field){
				$field->setValue($results->{$field->name});
			}
			return true;
		}
		return false;
				
	}
	/**
	 * Inserts a record
	 */
	public function insert(){
		
		// Set default arrays for insert values
		$names = array();
		$values = array();
		// Loop through fields to perform all pre-insertion logic
		foreach($this->fields as $field){
			// Store array fields and values in arrays
			array_push($names, $field->getName());
			array_push($values, $field->getValue());
			
			// If an auto-increment field has been set for some reason, reset it
			$autoIncrement = false;
			if($field->isAutoIncrement()){
				$field->setValue(null);
				$autoIncrement = $field;
			}
		}
		
		// Prepare SQL query
		$sql  = "INSERT INTO `" . $this->table . "` (" . implode(",", $names) . ") ";
		$sql .= "VALUES ('" . implode("','", $values) . "')";

		// Execute query and store result
		$result = self::$mysql->query($sql);
		
		if($id = $result->id()){
			
			// update object to reflect auto incrememnt value (if any)
			if($autoIncrement) $autoIncrement->setValue($id);
			
			// Insert successful
			return true;
			
		}
		throw new MC2_Mysql_Model_Exception(self::$mysql->getError());
		
	}
	/**
	 * Saves a record (uptate)
	 */
	public function save(){
	//NOTE: Mysql will not update if no data has changed
	//TODO: Figure out a way to deal with this
	
		// Prepare SQL query
		$sql  = "UPDATE `" . $this->table . "` SET ";
		foreach($this->fields as $field){
			// Store all but primary key into the query set
			if(!$field->isPk()){
				$name = $field->getName();
				$value = $field->getValue();
				$assignments[] = "`" . $name . "` = '" . $value . "'";
			}
		}
		$sql .= implode(",", $assignments);
		$sql .= " WHERE " . $this->primaryKey . " = '" . $this->fields[$this->primaryKey]->getValue() . "'";
		
		// Execute query and store result
		$result = self::$mysql->query($sql);
		if($result->affectedRows() > 0){
			
			// Insert successful
			return true;
			
		}

		// Otherwise, find out why record wasn't inserted...
		throw new MC2_Mysql_Model_Exception(self::$mysql->getError());
		return false;
		
	}
	/**
	 * This provides a way to find records.
	 * @param $conditions (WHERE $conditions)
	 * @param $fieldsArray array of fields you would like returned
	 * @param $start record you would like results to start at
	 * @param $limit record limit
	 * @returns an array full of MC2_Mysql_Model Objects
	 */
	public function find($conditions, $fieldsArray=null, $start=null, $limit=null/*, $association=null*/){
		
		$className = get_class($this);
		$select_fields = is_array($fieldsArray) ? implode(",", $fieldsArray) : '*';
		
		$start_limit = '';
		if(!is_null($limit)){
			
			$start_limit .= ' LIMIT ';
			$start_limit .= !is_null($start) ?  $start . ', ' : '';
			$start_limit .= $limit;
			
		}

		$sql = "SELECT " . $select_fields . " FROM `" . $this->table . "` WHERE " . $conditions . $start_limit;
		$results = self::$mysql->query($sql);

		if($results->length()){

			$returnArray = array();
			for($results->first(); !$results->end(); $results->next()){
			
				$resultArray = $results->getCurrentRecord();
				$record = new $className;
				$record->loadFromArray($resultArray, $fieldsArray);
				array_push($returnArray, $record);
				
			}
			return $returnArray;

		}
		return false;
				
	}
	/**
	 * Loads a record into the object from an associative array
	 * Use keys as column names and values as values
	 */
	public function loadFromArray($array, $fieldsArray=array()){
		
		if(!is_array($array)){
		
			throw new MC2_Mysql_Model_Exception('Passed wrong parameter type to MC2_Mysql_Model::loadFromArray');
			return false;
			
		}
		
		foreach($array as $key => $val){
			
			$checkFieldsArray = true;
			if(!is_null($fieldsArray)){
				$checkFieldsArray = in_array($key, $fieldsArray);
			}
			
			if(array_key_exists($key, $this->fields) && $checkFieldsArray){
				$this->fields[$key]->setValue($val);
			}
		}
		
		return true;
		
	}
	/**
	 * Get a single value
	 */
	public function get($key){
		
		return array_key_exists($key, $this->fields) ? stripslashes($this->fields[$key]->getValue()) : null;
		
	}
	/**
	 * Set a single value
	 */
	public function set($key, $value){
	
		if(array_key_exists($key, $this->fields)){
			$this->fields[$key]->setValue($value);
			$this->saved = false;
			return true;
		}
		return false;
		
	}
	public function __get($key){
	
		return $this->get($key);
		
	}
	public function __set($key, $value){
	
		$this->set($key, $value);
		
	}
}
?>
And it's child class, MC2_Mysql_Model_Field

Code: Select all

<?php
class MC2_Mysql_Model_Field{
	public function __construct($name, $type, $null, $key, $default, $extra){
		$this->name = $name;
		$this->type = $type;
		$this->null = (strtoupper($null) == 'YES');
		$this->setKey($key);
		$this->setValue($default);
		$this->extra = $extra;
	}
	public function setValue($value){
		$this->value = $value;
	}
	protected function setKey($key){
		$this->key = $key;
	}
	public function isValid(){
		if(!$this->null && !$this->isAutoIncrement()){
			if(is_null($value)) return false;
		}
	}
	public function isPk(){
		return strtoupper($this->key) == 'PRI';
	}
	public function isAutoIncrement(){
		return $this->extra == 'auto_increment';
	}
	public function getName(){
		return $this->name;
	}
	public function getValue(){
		return $this->value;
	}
}
?>
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

too much code to critique?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Yes. A couple of thoughts.

1. It would be good to allow the programmer to pass defineFields() an array to curcumvent having to do "SHOW COLUMNS ".

2. I don't know if setters//getters does not see right for most of the properties in the Field object.

3. It would be good to make base classes and then build a MySQL layer on top if it.

4. What about JOINs?
(#10850)
Post Reply