Subversion hosting, CVS hosting, Trac hosting, Bugzilla hosting and software collaboration Providing hosted Subversion, CVS, Trac and Bugzilla repositories
 

February 20, 2008

Hooking My CodeIgniter Application to MySQL

Filed under: Software Development — Tags: , , , — 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’);

When I navigate to my /codeigniter/user/count_users/ URL, I see:

There are 2 users defined

Great! Next up, we’ll take a look at creating an actual output page with views.

Bookmark and Share

Keep in touch,
Greg
SourceHosting.net, LLC


No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.


Call me - Greg Larkin: error

Powered by WordPress