Welcome Guest, Not a member yet? Register   Sign In
CI 3.x - How to set database connection collation?
#1

Hello
I'm a bit confused as to how to set the database connection collation from within CodeIgniter 3.x. There is a dbcollat configuration parameter in the config, but it is not used for this purpose if you read the comment about it in the code and grep where it's used.
Since the default connection collation is utf8mb4_general_ci when used with the utf8mb4 character set in MariaDB 10.5, and I wanted to use utf8mb4_unicode_ci or utf8mb4_unicode_520_ci for my database, how do I set the connection collation from within CodeIgniter?
Thanks
Jamie
Reply
#2

(This post was last modified: 08-31-2022, 05:33 AM by EdwardOkduduMari.)

I would personally just run a PDO query in the /application/config/database.php file like this:

<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the 'Database Connection'
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
|  ['hostname'] The hostname of your database server.
|  ['username'] The username used to connect to the database
|  ['password'] The password used to connect to the database
|  ['database'] The name of the database you want to connect to
|  ['dbdriver'] The database type. ie: mysql.  Currently supported:
                mysql, mysqli, postgre, odbc, mssql, sqlite, oci8
|  ['dbprefix'] You can add an optional prefix, which will be added
|                to the table name when using the  Active Record class
|  ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
|  ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
|  ['cache_on'] TRUE/FALSE - Enables/disables query caching
|  ['cachedir'] The path to the folder where cache files should be stored
|  ['char_set'] The character set used in communicating with the database
|  ['dbcollat'] The character collation used in communicating with the database
|                NOTE: For MySQL and MySQLi databases, this setting is only used
|                as a backup if your server is running PHP < 5.2.3 or MySQL < 5.0.7
|                (and in table creation queries made with DB Forge).
|                There is an incompatibility in PHP with mysql_real_escape_string() which
|                can make your site vulnerable to SQL injection if you are using a
|                multi-byte character set and are running versions lower than these.
|                Sites using Latin-1 or UTF-8 database character set and collation are unaffected.
|  ['swap_pre'] A default table prefix that should be swapped with the dbprefix
|  ['autoinit'] Whether or not to automatically initialize the database.
|  ['stricton'] TRUE/FALSE - forces 'Strict Mode' connections
|                          - good for ensuring strict SQL while developing
|
| The $active_group variable lets you choose which connection group to
| make active.  By default there is only one group (the 'default' group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = 'default';
$active_record = TRUE;

$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$dbh = new PDO(YourPDOConnDetails);

$sql = "SELECT DbUsername,DbName,DbPassword FROM abc WHERE efg=?";

$sth = $dbh->prepare($sql);
$sth->execute(array($efg));
$d_result= $sth->fetchAll(PDO::FETCH_ASSOC);

// We are done with PDO for this purpose so free up some resources!
$dbh = null;
unset($dbh);

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = $d_result[0]["DbUsername"];
$db['default']['password'] = $d_result[0]["DbPassword"];
$db['default']['database'] = $d_result[0]["DbName"];
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = FALSE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;


/* End of file database.php */
/* Location: ./application/config/database.php */

Approach #2
$efg="";
$data = explode('.',$_SERVER['SERVER_NAME']);
if (!empty($data[0])) {
    $efg = $data[0];
}

$active_group = $efg; // this will choose from subdomain1/subdomain2 settings below. Add as many as you need
$active_record echatspin echatrandom = TRUE;

$db['subdomain1']['hostname'] = 'localhost';
$db['subdomain1']['username'] = 'subd1User';
$db['subdomain1']['password'] = 'subd1Pass';
$db['subdomain1']['database'] = 'subd1DBName';
$db['subdomain1']['dbdriver'] = 'mysql';
$db['subdomain1']['dbprefix'] = '';
$db['subdomain1']['pconnect'] = FALSE;
$db['subdomain1']['db_debug'] = TRUE;
$db['subdomain1']['cache_on'] = FALSE;
$db['subdomain1']['cachedir'] = '';
$db['subdomain1']['char_set'] = 'utf8';
$db['subdomain1']['dbcollat'] = 'utf8_general_ci';
$db['subdomain1']['swap_pre'] = '';
$db['subdomain1']['autoinit'] = TRUE;
$db['subdomain1']['stricton'] = FALSE;

$db['subdomain2']['hostname'] = 'localhost';
$db['subdomain2']['username'] = 'subd2User';
$db['subdomain2']['password'] = 'subd2Pass';
$db['subdomain2']['database'] = 'subd2DBName';
$db['subdomain2']['dbdriver'] = 'mysql';
$db['subdomain2']['dbprefix'] = '';
$db['subdomain2']['pconnect'] = FALSE;
$db['subdomain2']['db_debug'] = TRUE;
$db['subdomain2']['cache_on'] = FALSE;
$db['subdomain2']['cachedir'] = '';
$db['subdomain2']['char_set'] = 'utf8';
$db['subdomain2']['dbcollat'] = 'utf8_general_ci';
$db['subdomain2']['swap_pre'] = '';
$db['subdomain2']['autoinit'] = TRUE;
$db['subdomain2']['stricton'] = FALSE;
Reply
#3

PHP Code:
// application/config/database.php
$db['default']['char_set'] = 'utf8mb4';
$db['default']['dbcollat'] = 'utf8mb4_unicode_ci'

Also make sure that you set it on the datbase also.

Code:
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB