• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Avoid conflict between column names ?

#7
(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 
<= 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($columnstrpos($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(
$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 $order-$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(
=== $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(
$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();
    }

Reply


Messages In This Thread
Avoid conflict between column names ? - by Coool6 - 11-07-2017, 12:38 AM
RE: Avoid conflict between column names ? - by SomeGuy - 11-07-2017, 08:16 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.