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);
}
}
?>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;
}
}
?>