CodeIgniter Forums
Question about Database Prefixes - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Question about Database Prefixes (/showthread.php?tid=63042)

Question about Database Prefixes - jLinux - 09-18-2015

Im using CI3, and the group im working with has decided to implement the ability to optionally add a database prefix, so whoever downloads the app were making, can specify a prefix or not.

I had a question about how the prefixes work.

If I am specifying the table in the selects, for example..
PHP Code:
$this->db->select('table.col1,table.col2'); 
, will the prefix be prepended correctly? Or will I need to use the db_prefix?

Also, if I use the db_prefix feature to manually add the prefix to a table, will CI see that and be smart enough to stop itself from adding it again?

Thanks

RE: Question about Database Prefixes - wolfgang1983 - 09-19-2015

(09-18-2015, 07:51 PM)jLinux Wrote: Im using CI3, and the group im working with has decided to implement the ability to optionally add a database prefix, so whoever downloads the app were making, can specify a prefix or not.

I had a question about how the prefixes work.

If I am specifying the table in the selects, for example..



PHP Code:
$this->db->select('table.col1,table.col2'); 
, will the prefix be prepended correctly? Or will I need to use the db_prefix?

Also, if I use the db_prefix feature to manually add the prefix to a table, will CI see that and be smart enough to stop itself from adding it again?

Thanks

I think what you may be after is example

$this->db->select('one, two'); // These would be column names
$this->db->from($this->db->dbprefix .  'table');
$query = $this->db->get();
return $query->results(); or return $query->results_array();

RE: Question about Database Prefixes - jLinux - 09-19-2015

(09-19-2015, 01:37 AM)riwakawd Wrote:
(09-18-2015, 07:51 PM)jLinux Wrote: Im using CI3, and the group im working with has decided to implement the ability to optionally add a database prefix, so whoever downloads the app were making, can specify a prefix or not.

I had a question about how the prefixes work.

If I am specifying the table in the selects, for example..




PHP Code:
$this->db->select('table.col1,table.col2'); 
, will the prefix be prepended correctly? Or will I need to use the db_prefix?

Also, if I use the db_prefix feature to manually add the prefix to a table, will CI see that and be smart enough to stop itself from adding it again?

Thanks

I think what you may be after is example

$this->db->select('one, two'); // These would be column names
$this->db->from($this->db->dbprefix .  'table');
$query = $this->db->get();
return $query->results(); or return $query->results_array();

What im asking though, is does CI ever automatically append the dbprefix to any of the tables? I was told it does, and i think I saw that in the documentation...

Heres an example query..

PHP Code:
$email_select = self::$db
    ->select("contacts.contact")
    ->where("contacts.type","email")
    ->where("contacts.primary","1")
    ->where("contacts_assoc.account_id","this_id")
    ->like("contacts.contact","@%.")
    ->from('contacts')
    ->join("contacts_assoc", "contacts.contact_id = contacts_assoc.contact_id")
    ->get_compiled_select();

$groups_select = self::$db
    ->select("group_concat(distinct groups.name SEPARATOR ',')")
    ->from('groups')
    ->join("accounts_group_assoc", "accounts_group_assoc.group_id = groups.group_id")
    ->join("accounts", "accounts.account_id = accounts_group_assoc.account_id")
    ->where("accounts_group_assoc.account_id","this_id")
    ->get_compiled_select();

$groups_select = self::$db
    ->select("group_concat(distinct groups.group_id SEPARATOR ',')")
    ->join("accounts_group_assoc", "accounts_group_assoc.group_id = groups.group_id")
    ->join("accounts", "accounts.account_id = accounts_group_assoc.account_id")
    ->where("accounts_group_assoc.account_id","this_id")
    ->from('groups')
    ->get_compiled_select();

$query = self::$db
    ->select("accounts.username, accounts.account_id, accounts.account_id as this_id, accounts.status, accounts.language, accounts.auth_library, accounts.homepage, accounts.activation_date, accounts.first_name, accounts.last_name, accounts.avatar_source")
    ->select("($email_select) as email, ({$groups_select}) as groups, ({$groups_select}) as group_ids")
    ->where('accounts.account_id', $account_id)
    ->get('accounts'); 

So what im asking, is for all of the selects that do table.column, will I need to do something like
PHP Code:
$accounts_table = $this->db->dbprefix('accounts');

$query = self::$db
    ->select("{$accounts_table}.username, {$accounts_table}.account_id, {$accounts_table}.account_id as this_id, {$accounts_table}.status, {$accounts_table}.language, {$accounts_table}.auth_library, {$accounts_table}.homepage, {$accounts_table}.activation_date, {$accounts_table}.first_name, {$accounts_table}.last_name, {$accounts_table}.avatar_source")
    ->select("($email_select) as email, ({$groups_select}) as groups, ({$groups_select}) as group_ids")
    ->where("{$accounts_table}.account_id", $account_id)
    ->get('accounts'); 

Also, in the db::get(), will I need to supply the prefix?

The reason why im asking, is theres a lot of times where I join tables and select different columns that may have the same name, from other tables, for example..
PHP Code:
self::$db
    ->select('table1.a, table2.a, table3.a')
    ->join('table1', 'table1.a = table2.a')
    ->join('table2', 'table2.a = table3.a')
    ->where('table1.a', 'test1')
    ->where('table3.a', 'test2')
    ->get('table3'); 


So im asking is when do I need to manually append the prefix? And if I manually append it, will CI see that its on there, and NOT do it itself? That would obviously cause errors.

Thanks!

RE: Question about Database Prefixes - jLinux - 09-19-2015

Nevermind, I was able to get it to work.

There were some issues with doing things like trying to CONCAT two columns in two different tables when joining the tables, it wouldnt prepend the suffix to one of the tables, but after a bit of tweaking, I got it to work!

Thanks!