Connecting Yii to a database and creating one to many relationships

This post will focus on connecting a webapp to a backend database and assumes that an initial webapp has been already created. To see how to create a web app check out this post.

step 1 – changes to the main configuration file

open in your favourite text editor /demo/protected/config/main.php and remove the commented section to enable the gii tool which is used to generate the php model, view and controller files – see later.

'modules'=>array(
		// uncomment the following to enable the Gii tool

		'gii'=>array(
			'class'=>'system.gii.GiiModule',
			'password'=>'password',
		 	// If removed, Gii defaults to localhost only. Edit carefully to taste.
			'ipFilters'=>array('127.0.0.1','::1'),
		),

	),

then comment out the section which uses the default sqlite database and uncomment the section which specifies a mysql database. Note in my case the database name is demodb and you may need to change the settings for username and password. see below:

/*
		'db'=>array(
			'connectionString' => 'sqlite:'.dirname(__FILE__).'/../data/testdrive.db',
		),*/
		// uncomment the following to use a MySQL database

		'db'=>array(
			'connectionString' => 'mysql:host=localhost;dbname=demodb',
			'emulatePrepare' => true,
			'username' => 'root',
			'password' => '',
			'charset' => 'utf8',
		),

proceed to http://localhost/david/demo/index.php?r=gii/default/login to access the gii (code generation) tool and enter the appropriate password.

Step 2 – creating the database using mysql and sql

The next step is to create the database. In this example I am going to use three tables to store information on structures, contacts and entries. The following sql code will create the database tables and the necessary relationships between them. It is assumed that you know how to establish relationships between tables using sql. The statements are at the end of the section.

DROP DATABASE IF EXISTS demodb;

CREATE DATABASE demodb;

USE demodb;

DROP TABLE IF EXISTS `tbl_structure`;

CREATE TABLE IF NOT EXISTS `tbl_structure` (
  `id` int NOT NULL AUTO_INCREMENT,
  `houseno` varchar(10) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `postcode` varchar(15) DEFAULT NULL,
  `create_time` datetime,
  `create_user_id` int,
  `update_time` datetime,
  `update_user_id` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `tbl_contact`;

CREATE TABLE IF NOT EXISTS `tbl_contact` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sal` varchar(10) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `secondname` varchar(50) DEFAULT NULL,
  `companyname` varchar(75) DEFAULT NULL,
  `houseno` varchar(10) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `postcode` varchar(15) DEFAULT NULL,
  `telno` varchar(20) DEFAULT NULL,
  `faxno` varchar(20) DEFAULT NULL,
  `mobno` varchar(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `structure_id` int NOT NULL,
  `type_id` int NOT NULL,
  `create_time` datetime,
  `create_user_id` int,
  `update_time` datetime,
  `update_user_id` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `tbl_entry`;

CREATE TABLE IF NOT EXISTS `tbl_entry` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `notes` text NOT NULL,
  `structure_id` int NOT NULL,
  `create_time` datetime,
  `create_user_id` int,
  `update_time` datetime,
  `update_user_id` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- The Relationships
ALTER TABLE `tbl_contact` ADD CONSTRAINT `FK_contact_structure` FOREIGN KEY (`structure_id`) REFERENCES `tbl_structure` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE `tbl_entry` ADD CONSTRAINT `FK_entry_structure` FOREIGN KEY (`structure_id`) REFERENCES `tbl_structure` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

Step 3 – using gii to build the code

Once the database has been established the gii code generation tool can be used to generate the necessary code.
goto http://localhost/david/demo/index.php/?r=gii and enter the password which you have set. Select the “Model Generator” from the left menu and fill in the appropriate fields as shown in the following screen shot.

Carry out a similar approach for each of the three tables – structure, entry and contact. You should now have three new model files created within the models folder. Note that you may have to change permissions on this folder to allow the server to write these new files.

If you inspect these model files you will note that because the table relationships were setup using the sql code this has transferred into the php code and you will note that structure “has many” entries and contacts and that the contacts and entries objects have a section labelled “belongs to:”

Step 4 – Extending to provide CRUD functionality

In a similar way to creating the model in step 3 above we can create all the functionality for Create, Read, Update and Delete (CRUD) functions. Goto the gii tool again and select the CRUD generator. Select the Model class for which the code is to be generated, preview and generate. This will create a controller file for each model and a number of view files.

At this stage the gii tool will have generated the skeleton code for the MVC. A single file for the Model, a single file for the Controller and a number of view files. These are used to construct the appropriate view depending on the selection for each of the CRUD operations.

At this stage however, the relationships between entities would require the manual insertion of the appropriate integer to establish the link. See the following post which describes how to amend the standard files to offer the selection of other related objects.

About these ads
Posted in yii
3 comments on “Connecting Yii to a database and creating one to many relationships
  1. Mike Salway says:

    Great post – I enjoyed this. Will you be doing a followup to include more functions?

  2. Peder says:

    It would be great to see an example of Many-to-many relationships

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: