Hooking My CodeIgniter Application to MySQL

February 20, 2008

Hooking My CodeIgniter Application to MySQL

Filed under: Software Development — Tags: codeigniter, framework, mvc, php — User Image

Greg Larkin @ 7:19 pm

Hi everyone,

When I last posted about my sample application development with CodeIgniter, I had created a simple Hello, World application. The next thing I want to explore is how easy it is to connect to a MySQL database and retrieve data.

As a quick side note, the CodeIgniter User Guide is well-written and very clear with plenty of examples. That’s always nice to see and instills some confidence in the quality of the underlying code.

According to the guide, all of the database connections should be defined in the application/config/database.php file. After setting up a new database named “ci” and a database user, I opened database.php and set up my connection:

$db[‘default’][‘hostname’] = "localhost";
$db[‘default’][‘username’] = "ci";
$db[‘default’][‘password’] = "ci";
$db[‘default’][‘database’] = "ci";
$db[‘default’][‘dbdriver’] = "mysql";
$db[‘default’][‘dbprefix’] = "";
$db[‘default’][‘active_r’] = TRUE;
$db[‘default’][‘pconnect’] = TRUE;
$db[‘default’][‘db_debug’] = TRUE;
$db[‘default’][‘cache_on’] = FALSE;
$db[‘default’][‘cachedir’] = "";

That was easy enough. Now I’ve got the skeleton of a model defined in application/models/user_model.php:

class Group_model extends Model {
function Group_model()
{
parent::Model();
$this->load->database();
}
}

The $this->load->database() statement automatically connects to MySQL using the default connection I defined previously. Now I can use the $this->db object to run queries and process results elsewhere in my model file.

I’ll replace my original hard-coded count_users function with something that runs an actual database query:

function count_users()
{
$query = $this->db->query(‘SELECT COUNT(*) AS num_users FROM user’);
if ($query->num_rows() > 0)
{
return $query->row()->num_users;
}
}

Of course, I have to leave a newbie coding error in there for someone to notice. Anyone? Anyone? Bueller?

Here’s my simple MySQL database schema:

–
– Table structure for table `user`
–

CREATE TABLE IF NOT EXISTS `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(128) NOT NULL,
`full_name` VARCHAR(128) NOT NULL,
`email` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

–
– Dumping data for table `user`
–

INSERT INTO `user` (`id`, `user_name`, `full_name`, `email`) VALUES
(1, ‘glarkin’, ‘Greg Larkin’, ‘glarkin@sourcehosting.net’),
(2, ‘morsel’, ‘Morsel The Cat’, ‘morsel@meow.com’);