Avoid conflict between column names ?

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