• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Question about Database Prefixes

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

#2
(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();
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply

#3
(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!
Reply

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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