In a web app I’m developing at the moment, the primary database in use is an instance of Microsoft SQL Server 2005 Express. This works OK with the CodeIgniter/PHP front-end. Obviously, if I had the choice, I would be using MySQL but in the real world sometimes this isn’t possible. And MSSQL is working great with simultaneous connections from the PHP app and MS Access.
I hit a point where I also wanted to access a MySQL database as well. This is easier than you think to configure, and worked first time with me without any stress or hair pulling.
To save you having to read through all the following detailed examples, the basic concept is as follows:
- Add a new config array to the $db array in
config/database.php. - Name this array whatever you like, and configure it as normal with the usual CI elements/options. E.g.
$db['other_db']. - Create a member variable in your models/wherever you want to access the new setup. Name it something like
var $other_db;. - Load the database into the member variable, using
$this->other_db = $this->load->database('other_db', TRUE);. - Reference the new member variable just like you would with CodeIgniter’s own
$this->dbobject.
Configure database.php
First thing you’ll need to do is let CodeIgniter know of your multiple database setup in the config/database.php file. Mine looks a bit like this.
// $db['default'] config array will hold the default db settings - in my case, for the MSSQL server $db['default']['hostname'] = "SQLSERVERADDRESS\SQLEXPRESS"; $db['default']['username'] = "sa"; // or a different account $db['default']['password'] = "secr3tsqlpassw0rd"; $db['default']['database'] = "yourdbname"; $db['default']['dbdriver'] = "mssql"; // obviously this is platform dependent $db['default']['dbprefix'] = ""; $db['default']['pconnect'] = TRUE; $db['default']['db_debug'] = TRUE; $db['default']['cache_on'] = FALSE; $db['default']['cachedir'] = ""; $db['default']['char_set'] = "utf8"; $db['default']['dbcollat'] = "utf8_general_ci"; // for the secondary db server, $db['mysql'] will hold the config. you could name this anything you like, e.g. $db['secondary_db'], or however you see fit $db['mysql']['hostname'] = "localhost"; $db['mysql']['username'] = "root"; $db['mysql']['password'] = "secr3tmysqlpassw0rd"; $db['mysql']['database'] = "other"; $db['mysql']['dbdriver'] = "mysqli"; // in this case, i'm using the MySQL Improved class to access the MySQL server $db['mysql']['dbprefix'] = ""; $db['mysql']['pconnect'] = TRUE; $db['mysql']['db_debug'] = TRUE; $db['mysql']['cache_on'] = FALSE; $db['mysql']['cachedir'] = ""; $db['mysql']['char_set'] = "utf8"; $db['mysql']['dbcollat'] = "utf8_general_ci";
Load the non-default database config in your models
So if you followed the above, you have two database configurations. You have default as per the first array, and mysql, as per the second (or whatever you chose to name it).
The next bit is simple. In your models, you can access the default database server just by using ordinary ActiveRecord calls such as $this->db->get('users');, but how do you access your secondary database?
Simple, just use the $this->load->database(); method, and plug in the name of your additional database config from the database.php config file. This will load the driver to this database, so you’ll want to do this in your models, and assign it to a member variable.
Working through an example below, I created a brand new model to do this, with a member variable. This model was then used just for connecting to the MySQL database. So here’s an example that accesses a table of UK postcodes.
<?php
class Mysql_model extends Model {
// We'll access the MySQL db from this app which is primarily MSSQL based. Create the member variable.
var $mysql;
function Mysql_model() {
parent::Model();
$this->mysql = $this->load->database('mysql', TRUE); // Load the db, and assign to the member var.
}
/**
* Accepts an outfield/regional British postcode, and returns the data associated with it.
*
* @param string $postcode The outfield UK postcode. E.g. SY21, NW1, HR6, RG19, CF37, KT1, etc...
* @return array Of postcode data, bool FALSE otherwise
* @author George Edwards
*/
function get($postcode) {
// Now we can refer to this database server with $this->mysql.
$this->mysql->select('town, county')->where('postcode', $postcode)->from('uk_postcodes')->limit(1);
$query = $this->mysql->get();
if ($query->num_rows() === 1) {
// Do we have 1 result, as we expected? Return it as an array.
return $query->row_array();
}
// Otherwise, we'll return FALSE.
return FALSE;
}
}
/* End of file mysql_model.php */
/* Location: ./application/models/mysql_model.php */
Have fun with CodeIgniter! Any questions? Leave me a comment.
good article, thanks
I want to use mssql server 2005 in my codeigniter,
here is my code in my database.php
$db['default']['hostname'] = “eric\sqlexpress”;
$db['default']['username'] = “eric\ericson”;
$db['default']['password'] = “”;
$db['default']['database'] = “student”;
$db['default']['dbdriver'] = “mssql”;
. .
eric\sqlexpress is my server name and
eric\ericson is my username in my mssql server. . it doesn’t work. .. is there any problem with my code?
how will i use it in my model
Hello, I would suggest that in order to troubleshoot this, to try connecting using mssql_connect() to start off with, which may provide you with more verbose error messages. It could be, in fact, that your server is not configured with PHP to support MSSQL connections. Try using raw PHP first and then post back if you need more help.
Hi
How did you connect to mssql using coz for me changing the config/datatbase.php file to reflect the sql server settings, a blank page is displayed when i load the project in a browser.
Thanks
Hi Brian. If you are getting a blank page, I would check your PHP settings. I’m pretty certain that an error is being thrown, but your environment settings are configured to supress errors. Check php.ini and ensure that
display_errors = Onanderror_reporting = E_ALL & ~E_NOTICE. Obviously change these back for a production environment! Hope that helps.If you are using IIS 6 + PHP + MS SQL, then this will help …
Direct Connection to MSSQL without using
odbc connection. The following requirements
must be met for this PHP function to run
properly:
MSSQL functions
PHP has a set of native functions to access Microsoft SQL Server. While the manual officially recommends that you install the extensions on the Windows platform,
it is possible to make it function in Linux by using the FreeTDS libraries. Here is the entire list of properties and methods for this function.
Before you can use the mssql functions, you must enable the extensions. You can accomplish this in a few simple steps:
+ Install the Microsoft SQL Client Tools on the same server as your Windows PHP installation. The tools are readily available on the SQL Server CD.
+ Edit your php.ini file and uncomment (remove the semicolon from) the following line of code. If this line is missing in your php.ini, you must manually add it in:
extension=php?_mssql.dll
+ The php_mssql.dll file can be obtained in the PHP downloads for Win32. You must make sure that you place the dll in both the /extensions and /WINNT/system32 folder.
+ Restart the Web server.
Thanks for your input
I’m sure it will prove useful to readers!
Hi will this method work if I have two MSSQL instance insteady onf mssql and one mysql?
I can’t see why not! Just amend the hostname to be
HOSTNAME\INSTANCEas this is how MSSQL operates. E.g.127.0.0.1\SQLEXPRESS.