-
SomeGuy
Junior Member
-
Posts: 36
Threads: 5
Joined: Jan 2015
Reputation:
2
11-07-2017, 08:16 PM
(This post was last modified: 11-09-2017, 10:13 AM by SomeGuy.
Edit Reason: More robust
)
(11-07-2017, 02:59 PM)PaulD Wrote: In my tables I have for example a recipes table: recipe_id, recipe_name and in an ingredients table: ingredient_id, ingredient_name. Just be careful not to use recipe as your table name base for any other table. So the recipe_items table is: item_id, item_recipe_id and item_ingredient_id.
No conflicts, no problems, very clear to use. (IMHO)
Paul
Best practice for me has always been to load each record as a model. Then name your columns whatever you want, the database logic will be tied to the model and you would just use $my_model->full_name which could be something like... (Warning: untested code)
# In some controller method
PHP Code: <?php $user_model = new User(ci()->session->user_data('id'));
# In some view
Code: <p><?php echo $user_model->full_name ?></p>
<div class="work-history">
<?php if(0 === $user_model->work_history->total) : ?>
<p class="none">No work history was found.</p>
<?php else : ?>
<ul>
<?php foreach($user_model->work_history->limit(5) as $work_history_model) : ?>
<li><a href="<?php echo $work_history_model->company->details_url ?>" class="out"><?php $work_history_model->company->legal_name ?></a>, <?php echo $work_history_model->formatted_date_range ?></em>)</li>
<?php endforeach ?>
</ul>
<?php if(TRUE === $user_model->work_history->has_more) : ?>
<p class="view-all"><a href="<?php echo $user_model->details_url ?>">View full work history</a></p>
<?php endif ?>
<?php endif ?>
</div>
/application/config/config.php
PHP Code: <?php defined('BASEPATH') OR exit('No direct script access allowed');
define('ALLOW_DEBUG', FALSE); define('STATUS_ERROR', 'error'); define('STATUS_SUCCESS', 'success');
/* |-------------------------------------------------------------------------- | Auto-load models, controllers, libraries, etc. |-------------------------------------------------------------------------- | Keep the php autoloader in this file - do not place in autoloader.php | If it's moved out of this file, classes will not be loaded soon enough. */
function application_autoloader($class) { $class = $class.'.php';
if(ALLOW_DEBUG) : $locations = array( APPPATH.'models-TESTING'.DIRECTORY_SEPARATOR.strtolower($class), APPPATH.'controllers-TESTING'.DIRECTORY_SEPARATOR.strtolower($class), ); else : $locations = array(); endif;
$locations = array_merge($locations, array( APPPATH.'models'.DIRECTORY_SEPARATOR.strtolower($class), APPPATH.'collections'.DIRECTORY_SEPARATOR.strtolower($class), APPPATH.'controllers'.DIRECTORY_SEPARATOR.$class, APPPATH.'libraries'.DIRECTORY_SEPARATOR.$class, APPPATH.'core'.DIRECTORY_SEPARATOR.$class, ));
foreach($locations as $location) : if(file_exists($location)) : include $location; break; endif; endforeach; } spl_autoload_register('application_autoloader');
function system_autoloader($class) { $class = ucfirst(str_replace('CI_', '', $class)).'.php';
$locations = array( BASEPATH.'libraries'.DIRECTORY_SEPARATOR.$class, BASEPATH.'core'.DIRECTORY_SEPARATOR.$class, );
foreach($locations as $location) : if(file_exists($location)) : include $location; break; endif; endforeach; } spl_autoload_register('system_autoloader');
# ... Rest of config file...
/application/helpers/utilities.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
if(!function_exists('ci')) : function ci() { return get_instance(); } endif;
if(!function_exists('formatEmailAddress')) : function formatEmailAddress($string) { return trim(strtolower($string)); } endif;
if(!function_exists('formatPhoneNumber')) : function formatPhoneNumber($string) { return $string; } endif;
if(!function_exists('isValidEmailAddress')) : function isValidEmailAddress($string) { return 3 <= strlen($string) && strstr($string, '@'); } endif;
if(!function_exists('isValidPhoneNumber')) : function isValidPhoneNumber($string) { return TRUE; } endif;
/application/core/MY_Model.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class MY_Model { const DB_TBL_ALIAS = 'model.'; const DB_TBL_NAME = NULL; const DB_TBL_PK = NULL;
protected $process_results = array(); protected $properties = NULL; public function __construct($id=NULL) { if(is_numeric($id)) : $this->loadById($id); endif; } public function __get($key) { if(array_key_exists($key, $this->properties)) : return $this->properties[$key]; endif; $lazy_load_property = '__lazy_load_'.$key; if(array_key_exists($lazy_load_property, $this->properties)) : return $this->properties[$lazy_load_property]; endif;
$lazy_load_method = '__load'.str_replace(' ', '', ucwords(str_replace('_', ' ', $key))); if(method_exists($this, $lazy_load_method)) : $this->properties[$lazy_load_property] = $this->$lazy_load_method(); return $this->properties[$lazy_load_property]; endif; return NULL; }
public function __loadDetailsUrl() { return NULL; }
public function __loadIsLoaded() { return NULL !== $this->properties && NULL === $this->deleted_date; }
public function __modifyLoadQuery() { return TRUE; }
public function getProcessResults() { return empty($this->process_results) ? NULL : $this->process_results; }
public function loadByArray($properties=NULL) { $this->properties = NULL;
if(is_array($properties) && !empty($properties)) : $this->properties = $properties; endif;
return $this; }
public function loadByColumn($column, $value) { $this->properties = NULL;
if(empty($value) || !is_string($value)) : return $this; endif;
$column = 'id' === $column ? static::DB_TBL_PK : $column; $column = strstr($column, '.') ? substr($column, strpos($column, '.')+1) : $column;
ci()->db ->select(static::DB_TBL_ALIAS.'*') ->from(trim(static::DB_TBL_NAME.' '.str_replace('.', '', static::DB_TBL_ALIAS)) ->where(static::DB_TBL_ALIAS.$column, $value);
$this->__modifyLoadQuery();
$qr = ci()->db->get(); if(0 < $qr->num_rows()) : $this->properties = $row; endif; return $this; }
public function loadById($id=NULL) { return $this->loadByColumn('id'); }
public function processCreate($data_set=array()) { $this->setProcessResult(STATUS_ERROR, __METHOD__.' is not overloaded in '.static::class); return FALSE; }
public function processDelete($data_set=array()) { ci()->db ->set('updated_date', 'NOW()', FALSE) ->set('deleted_date', 'NOW()', FALSE) ->where(static::DB_TBL_PK, $this->id) ->update(static::DB_TBL_NAME);
$this->processPostDelete($data_set); $this->setProcessResult(STATUS_SUCCESS, 'Record was deleted');
return TRUE; }
protected function processPostCreate($data_set=array()) { # Overload in model; Send email, log data changes/user action, etc, etc }
protected function processPostDelete($data_set=array()) { # Overload in model; Send email, log data changes/user action, etc, etc }
protected function processPostUpdate($data_set=array()) { # Overload in model; Send email, log data changes/user action, etc, etc }
protected function processPrepareData($data_set=array()) { # Make sure all necessary fields are present return $data_set; }
protected function processPreValidate($data_set=array()) { return TRUE; }
public function processUpdate($data_set=array()) { $this->setProcessResult(STATUS_ERROR, __METHOD__.' is not overloaded in '.static::class); return FALSE; }
protected function setProcessResult($status, $message=NULL) { $this->process_results[] = array( 'status' => $status, 'message' => $message, );
return TRUE; } }
/application/libraries/model_collection.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class Model_collection implements IteratorAggregate { protected $db_table_alias = 'model.'; protected $db_table_name = NULL; protected $db_table_pk = NULL; protected $limit = NULL; protected $joins = array(); protected $model = NULL; protected $orders = array(); protected $filters = array(); protected $page_records = NULL; protected $properties = array(); protected $selects = array(); protected $total_records = NULL;
public function __construct($model_name) { if(!class_exists($model_name)) : die('Invalid model name ('.$model_name.') in '.__METHOD__); endif;
$this->model = new $model_name(); $this->db_table_name = constant(get_class($this->model).'::DB_TBL_NAME'); $this->db_table_pk = constant(get_class($this->model).'::DB_TBL_PK'); }
public function __get($key) { switch($key) : case 'id' : $key = $this->db_table_pk; break; endswitch;
if(array_key_exists($key, $this->properties)) : return $this->properties[$key]; endif;
$lazy_load_property = '__lazy_load_'.$key; if(array_key_exists($lazy_load_property, $this->properties)) : return $this->properties[$lazy_load_property]; endif;
$lazy_load_method = '__load'.str_replace(' ', '', ucwords(str_replace('_', ' ', $key))); if(method_exists($this, $lazy_load_method)) : $this->properties[$lazy_load_property] = $this->$lazy_load_method(); return $this->properties[$lazy_load_property]; endif; return NULL; }
public function __loadHasMore() { return NULL !== $this->limit && $this->total > $this->limit; }
public function __loadPageRecords() { $this->loadRecords(); return $this->page_records; }
public function __loadTotal() { $this->loadTotal(); return $this->total_records; }
public function byOrder($order) { $order = $order > 0 ? $order-1 : $order; return is_array($this->page_records) && isset($this->page_records[$order]) ? $this->page_records[$order] : NULL; }
public function filter($column, $value, $method=NULL) { $column = 'id' === $column ? $this->db_table_pk : $column; $column = !strstr($column, '.') ? $this->db_table_alias.$column : $column;
$method = empty($method) ? 'where' : $method;
$this->filters[] = array( 'column' => $column, 'value' => $value, 'method' => $method, );
return $this; }
public function filterByKey($key=NULL, $value=NULL) { switch(url_title($key, '_', TRUE)) : case 'active' : $this->filter('deleted_date', NULL); break; case 'id' : $method = is_array($value) ? 'where_in' : 'where'; $this->filter('id', $value, $method); break; endswitch;
return $this; }
public function first() { return $this->byOrder(1); }
public function getIterator() { return new ArrayIterator($this->page_records); }
public function join($table, $condition, $type='inner') { $type = trim(str_ireplace('join', '', $type)); $type = in_array(strtolower($type), array('inner', 'outer', 'left', 'right')) ? strtolower($type) : 'inner';
$this->joins[] = array( 'table' => $table, 'condition' => $condition, 'type' => $type, );
return $this; }
public function last() { return $this->byOrder($this->limit); }
public function limit($count=NULL) { if(is_numeric($count)) : $this->limit = (int) $count; endif;
return $this; }
public function loadTotal($force=FALSE) { if(FALSE === $force && NULL !== $this->total_records) : return $this; endif;
if(NULL === ($records_query = $this->readyRecordsQuery(FALSE))) : $this->total_records = 0; else : $qr = ci()->db->query('SELECT COUNT(*) AS '.ci()->db->escape_str('num_rows').' FROM ('.$records_query.') AS temp'); $this->total_records = $qr->row()->num_rows; endif;
$this->limit($this->total_records); return $this; }
public function loadRecords($force=FALSE) { $this->loadTotal($force);
if(0 === $this->total_records) : return $this; endif;
if(FALSE === $force && NULL !== $this->page_records) : return $this; endif;
$this->page_records = array(); $qr = ci()->db->query($this->readyRecordsQuery(TRUE));
if(0 < $qr->num_rows()) : foreach($qr->result() as $row) : $this->page_records[] = $row; endforeach; endif;
return $this; }
public function order($column, $direction) { $direction = in_array(strtolower($direction), array('asc', 'desc')) ? strtolower($direction) : 'asc';
$this->orders[] = array( 'column' => $column, 'direction' => $direction, );
return $this; }
public function orderByKey($key=NULL, $direction=NULL) { switch(url_title($key, '_', TRUE)) : case 'created' : $this->order('created_date', $direction); break; case 'id' : $this->order('id', $direction); break; endswitch;
return $this; }
public function readyRecordsQuery($include_limit=TRUE) { # Get creative... # Build out select with selects/joins/filters/orders/limit/etc }
public function reset() { $this->limit = NULL; $this->orders = array(); $this->filters = array(); $this->page_records = NULL; $this->properties = array(); $this->total_records = NULL;
return $this; }
public function select($column) { $column = 'id' ? $this->db_table_pk : $column; $column = !strstr($column, '.') ? $this->db_table_alias.$column : $column;
$this->selects[$column] = $column; return $this; } }
/application/collections/work_history_collection.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class Work_history_collection extends Model_collection { public function __construct() { parent::__construct('Work_history');
$this->join(Company::DB_TBL_NAME.' company', 'company.'.Company::DB_TBL_PK.' = '.$this->db_table_alias.'company_id', 'inner') ->filter('company.deleted_date', NULL); }
public function filterByKey($key=NULL, $value=NULL) { switch(url_title($key, '_', TRUE)) : case 'active' : $this->filter('status', Work_history::STATUS_ACTIVE); break; case 'hired_after' : $this->filter('hired_date >=', $value); break; case 'hired_before' : $this->filter('hired_date <=', $value); break; case 'user' : $method = is_array($value) ? 'WHERE_IN' : 'WHERE'; $this->filter('user_id', $value, $method); break; endswitch;
return parent::filterByKey($key, $value); }
public function orderByKey($key, $direction) { switch(url_title($key, '_', TRUE)) : case 'hired' : $this->order('hired_date', $direction); break; endswitch;
return parent::orderByKey($key, $direction); } }
/application/models/user.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class User extends MY_Model { const DB_TBL_NAME = 'user'; const DB_TBL_PK = 'id';
public function __loadCurrentJob() { $collection = new Work_history_collection();
if(NULL !== ($work_history_model = $collection->filterByKey('active')->filterByKey('user', $this->id)->orderByKey('hired', 'desc')->limit(1)->first())) : return TRUE === $work_history_model->is_current ? $work_history_model : new Work_history(); endif;
return NULL; }
public function __loadDetailsUrl() { return site_url(array('user-profile', $this->slug)); }
public function __loadFullName() { return TRUE === $this->is_loaded ? trim($this->first_name.' '.$this->last_name) : NULL; }
public function __loadSlug() { return TRUE === $this->is_loaded ? url_title($this->full_name.' '.$this->id, '-', TRUE) : NULL; }
public function __loadWorkHistory() { $collection = new Work_history_collection(); return $collection->filterByKey('active')->filterByKey('user', $this->id); }
public function __modifyLoadQuery() { ci()->db ->select('extras.*') ->join('user_extras extras', 'extras.user_id = '.static::DB_TBL_ALIAS.static::DB_TBL_PK, 'inner');
return parent::__modifyLoadQuery(); }
public function processCreate($data_set=array()) { if(TRUE === $this->is_loaded) : $this->setProcessResult(STATUS_ERROR, 'Record is already loaded'); return FALSE; endif;
if(!is_array($data_set) || empty($data_set)) : $this->setProcessResult(STATUS_ERROR, 'Data set cannot be empty'); return FALSE; endif;
$data_set = $this->processPrepareData($data_set);
if(TRUE !== $this->processPreValidate($data_set)) : return FALSE; endif;
ci()->db ->set('first_name', $data_set['first_name']) ->set('last_name', $data_set['last_name']) ->set('email_address', $data_set['email_address']) ->set('phone_number', $data_set['phone_number']) ->set('created_date', 'NOW()', FALSE) ->set('updated_date', 'NOW()', FALSE) ->insert(static::DB_TBL_NAME);
$this->loadById(ci()->db->insert_id());
if(FALSE === $this->is_loaded) : $this->setProcessResult(STATUS_ERROR, 'Failed to locate new record'); return FALSE; endif;
$this->processPostCreate($data_set); $this->setProcessResult(STATUS_SUCCESS, 'Record was created');
return TRUE; }
protected function processPrepareData($data_set=array()) { if(isset($data_set['full_name'])) : $names = explode(' ', trim($data_set['full_name'])); $names = array_filter($names, 'strlen');
$data_set['first_name'] = array_pop($names); $data_set['last_name'] = implode(' ', $names); endif;
if(!isset($data_set['email_address'])) : $data_set['email_address'] = TRUE === $this->is_loaded ? $this->email_address : NULL; else : $data_set['email_address'] = formatEmailAddress($data_set['email_address']); endif;
if(!isset($data_set['phone_number'])) : $data_set['phone_number'] = TRUE === $this->is_loaded ? $this->phone_number : NULL; else : $data_set['phone_number'] = formatPhoneNumber($data_set['phone_number']); endif;
return $data_set; }
protected function processPreValidate($data_set=array()) { if(TRUE !== isValidEmailAddress($data_set['email_address'])) : $this->setProcessResult(STATUS_ERROR, 'Email address is invalid'); return FALSE; endif;
if(!empty($data_set['phone_number']) && TRUE !== isValidPhoneNumber($data_set['phone_number'])) : $this->setProcessResult(STATUS_ERROR, 'Phone number appears invalid'); return FALSE; endif;
$user_model = new User(); $user_model->loadByColumn('email_address', $data_set['email_address']);
if(TRUE === $user_model->is_loaded) : $this->setProcessResult(STATUS_ERROR, 'Email address is already in use'); return FALSE; endif;
return TRUE; }
public function processUpdate($data_set=array()) { if(TRUE !== $this->is_loaded) : $this->setProcessResult(STATUS_ERROR, 'Record is not loaded'); return FALSE; endif;
if(!is_array($data_set) || empty($data_set)) : $this->setProcessResult(STATUS_ERROR, 'Data set cannot be empty'); return FALSE; endif;
$data_set = $this->processPrepareData($data_set);
if(TRUE !== $this->processPreValidate($data_set)) : return FALSE; endif;
ci()->db ->set('first_name', $data_set['first_name']) ->set('last_name', $data_set['last_name']) ->set('email_address', $data_set['email_address']) ->set('phone_number', $data_set['phone_number']) ->set('updated_date', 'NOW()', FALSE) ->where(static::DB_TBL_PK, $this->id) ->update(static::DB_TBL_NAME);
$this->loadById(ci()->db->insert_id());
if(FALSE === $this->is_loaded) : $this->setProcessResult(STATUS_ERROR, 'Failed to locate new record'); return FALSE; endif;
$this->processPostUpdate($data_set); $this->setProcessResult(STATUS_SUCCESS, 'Record was created');
return TRUE; } }
/application/models/work_history.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class Work_history extends MY_Model { const DB_TBL_NAME = 'work_history'; const DB_TBL_PK = 'id'; const STATUS_ACTIVE = 'true'; const STATUS_INACTIVE = 'false';
public function __loadCompany() { return new Company($this->company_id); }
public function __loadDateRange() { return array( 'hired' => $this->hired_date, 'terminated' => $this->terminated_date, ); }
public function __loadFormattedDateRange() { if(NULL !== $this->date_range['hired']) : if(NULL !== $this->date_range['terminated']) : return $this->date_range['hired'].' - '.$this->date_range['terminated']; endif;
return $this->date_range['hired'].' - Current'; endif;
return 'Unknown'; }
public function __loadHiredDate() { if(TRUE === $this->is_loaded && FALSE !== ($date_ts = strtotime($this->hired_date))) : return date('m/d/Y', $date_ts); endif;
return NULL; }
public function __loadIsCurrent() { return NULL !== $this->date_range['hire'] && NULL === $this->date_range['terminate']; }
public function __loadTerminatedDate() { if(TRUE === $this->is_loaded && FALSE !== ($date_ts = strtotime($this->terminated_date))) : return date('m/d/Y', $date_ts); endif;
return NULL; }
public function __modifyLoadQuery() { ci()->db ->join(Company::DB_TBL_NAME.' company', 'company.'.Company::DB_TBL_PK.' = '.static::DB_TBL_ALIAS.'company_id', 'inner') ->where('company.deleted_date', NULL);
return parent::__modifyLoadQuery(); } }
/application/models/company.php
PHP Code: <?php defined('BASEPATH') OR die('No direct script access allowed');
class Company extends MY_Model { const DB_TBL_NAME = 'company'; const DB_TBL_PK = 'id';
public function __loadDetailsUrl() { return TRUE === $this->is_loaded ? site_url(array('company-profile', $this->slug)) : NULL; }
public function __loadLegalName() { return TRUE === $this->is_loaded ? trim($this->name.' '.$this->entity_type) : NULL; }
public function __loadSlug() { return TRUE === $this->is_loaded ? url_title($this->legal_name.' '.$this->id, '-', TRUE) : NULL; }
public function __loadWebsiteUrl() { return TRUE === $this->is_loaded && !empty($this->website) ? $this->website : NULL; }
public function __modifyLoadQuery() { ci()->db ->select('extras.*') ->join('company_extras extras', 'extras.company_id = '.static::DB_TBL_ALIAS.static::DB_TBL_PK, 'inner');
return parent::__modifyLoadQuery(); } }
|