Welcome Guest, Not a member yet? Register   Sign In
Avoid conflict between column names ?
#1

(This post was last modified: 11-07-2017, 12:38 AM by Coool6.)

Hello to the great CI community !

Before i used to write my columns names as follow : 

[dbprefix]_[tablename]_[columnname] for example abc_recipes_name 

It avoided any conflict with other tables in case of joining for example.
but seems nobody do that except me, so i'm back to just the column name but in case of joining it cause some issues. 


My actual way of fixing is to use aliases in the SELECT but i'm not sure if it's the best practice, it's a bit of messy.

Is there any better way using CI ? 

Thanks a lot,
Reply
#2

(This post was last modified: 11-07-2017, 01:25 AM by neuron.)

I am not expert on it but in my experience best way is to use tablename_columnname pattern for column names. this way is good when joining tables
Reply
#3

(11-07-2017, 01:25 AM)neuron Wrote: I am not expert on it but in my experience best way is to use tablename_columnname pattern for column names. this way is good when joining tables

I think so. I stopped when i started to learn CakePHP, it was out of the convention of this framework. Then i kept the CakePHP way when i went to CI. 
But maybe CI has its own way to deal with it.
Reply
#4

(This post was last modified: 11-07-2017, 02:03 AM by Coool6.)

I just saw in the documentation that CI has dbprefix()

https://www.codeigniter.com/user_guide/d...s-manually

But i don't really get how it works. So i keep my database without changing the column name, but i define a dbprefix and then when i use CI Query Builder, i write the column name including the prefix as if it's native ? Or the dbprefix only applies on the table name and not on the column names ?
Reply
#5

I think the best approach is prefix with the table name on foreign keys only, and using SQL aliases for duplicated collumn names. Ex.:

Table user
col id
col name
col age
col company_id

Table company
col id
col name
col address

Code:
SELECT
    user.*,
    company.name AS company_name
FROM user
JOIN company ON company.id = company_id;

So you can get your objects this way:

PHP Code:
$this->db->select('user.*');
$this->db->select('company.name AS company_name');
$this->db->join('company''company.id = company_id');
$user $this->db->get('user')->row();

echo 
"$user->name$user->company_name"

If you prefix your collumns in your db with the table name, even if you make a simple select, you'll have a ugly code like that:


PHP Code:
echo "$user->user_name$user->user_age"


At least for me, repeating the table name is annoying and makes my code harder to read.

https://www.w3schools.com/sql/sql_alias.asp
Reply
#6

(This post was last modified: 11-07-2017, 02:59 PM by PaulD.)

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
Reply
#7

(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 
<= 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
#8

(11-07-2017, 02:00 AM)Coool6 Wrote: I just saw in the documentation that CI has dbprefix()

https://www.codeigniter.com/user_guide/d...s-manually

But i don't really get how it works. So i keep my database without changing the column name, but i define a dbprefix and then when i use CI Query Builder, i write the column name including the prefix as if it's native ? Or the dbprefix only applies on the table name and not on the column names ?

Pretty sure it's just table name. Think multiple CI sites within a single DB container.
Reply
#9

(11-07-2017, 08:16 PM)SomeGuy Wrote: 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.... (you'll have to setup an autoloader or 2 to use this method...)

That actually is a really interesting idea. I will have a closer look at it on the next build I do, thankyou.

Paul
Reply
#10

(11-08-2017, 11:43 AM)PaulD Wrote: That actually is a really interesting idea. I will have a closer look at it on the next build I do, thankyou.

Paul

Made the example a little more robust to give you a more complete idea of how records are managed, etc.

Let me know if you end up using something similar.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB