Building a basic PHP E-Commerce Application using CodeIgniter Framework – Part 2

Part 2 -Creating the application database model.

Now that we are done the CI installation. We are ready to start building our e-commerce application. What we need to do first is design our database structure. You can of course freely design your own structure, but for this tutorial I made it simple.



Now, Let say,
I want to categorize my products. So we must have a table for category.
Product category can have a child category.
Category can be deactivated and activated.
This is our table for categories looks like.

view plaincopy to clipboardprint?

1. CREATE TABLE `categories` (  
2.   `cat_id` int(11) NOT NULL auto_increment,  
3.   `cat_name` varchar(255) NOT NULL,  
4.   `cat_shortdesc` varchar(255) NOT NULL,  
5.   `cat_longdesc` text NOT NULL,  
6.   `cat_parentid` int(11) NOT NULL,  
7.   `cat_status` tinyint(1) NOT NULL,  
8.   PRIMARY KEY  (`cat_id`)  
9. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  

CREATE TABLE `categories` (
  `cat_id` int(11) NOT NULL auto_increment,
  `cat_name` varchar(255) NOT NULL,
  `cat_shortdesc` varchar(255) NOT NULL,
  `cat_longdesc` text NOT NULL,
  `cat_parentid` int(11) NOT NULL,
  `cat_status` tinyint(1) NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Then our products,
Can only belong to one category at a time
Our products has a name, image, thumbnail, short description, long description
And can be activated and deactivated.
This is what I came up with our products table.

view plaincopy to clipboardprint?

1. CREATE TABLE `products` (  
2.   `prod_id` int(11) NOT NULL auto_increment,  
3.   `prod_name` varchar(255) NOT NULL,  
4.   `prod_shortdesc` varchar(255) NOT NULL,  
5.   `prod_longdesc` text NOT NULL,  
6.   `prod_thumbnail` varchar(255) NOT NULL,  
7.   `prod_image` varchar(255) NOT NULL,  
8.   `prod_status` tinyint(1) NOT NULL,  
9.   `prod_category_id` int(11) NOT NULL,  
10.   `prod_featured` tinyint(1) NOT NULL,  
11.   `prod_price` float(4,2) NOT NULL,  
12.   PRIMARY KEY  (`prod_id`)  
13. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;  

CREATE TABLE `products` (
  `prod_id` int(11) NOT NULL auto_increment,
  `prod_name` varchar(255) NOT NULL,
  `prod_shortdesc` varchar(255) NOT NULL,
  `prod_longdesc` text NOT NULL,
  `prod_thumbnail` varchar(255) NOT NULL,
  `prod_image` varchar(255) NOT NULL,
  `prod_status` tinyint(1) NOT NULL,
  `prod_category_id` int(11) NOT NULL,
  `prod_featured` tinyint(1) NOT NULL,
  `prod_price` float(4,2) NOT NULL,
  PRIMARY KEY  (`prod_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Insure you have a database installation on your local webserver. Using any mySQL client available in your end just throw this SQL to create the tables.

Creating the Categories Model

In creating a model in CodeIgniter you can name it anything you want as long as it don’t have any conflicts of names in other model or to your controller. It is a good practice to put a prefix or suffixes to your model’s name. My style in naming a model is chosen name suffixed by ‘Model’. So I have to name our model to ‘categoryModel’.
More details about creating model can be read in CI userguide. Now let’s create our basic model called categoryModel and our constructor.

view plaincopy to clipboardprint?

1. <?php  
2.   
3. class categoryModel extends Model{  
4.   
5.     function categoryModel(){  
6.         parent::Model();  
7.     }  
8.   
9. }  

<?php

class categoryModel extends Model{

 function categoryModel(){
  parent::Model();
 }

}

Save the file as categorymodel.php in your system/application/models/ directory.
Then add a function that will retrieve a category from the database.

view plaincopy to clipboardprint?

1. function getCategory($id){  
2.   
3.     $data = array();  
4.   
5.     $where = array('cat_id' => $id);  
6.   
7.     $quer = $this->db->getwhere('categories',$where,1);  
8.   
9.     if ($query-> num_rows() > 0){  
10.         $data = $query->row_array();  
11.     }  
12.   
13.     $query->free_result();  
14.     return $data;  
15. }  

function getCategory($id){

  $data = array();

  $where = array('cat_id' => $id);

  $quer = $this->db->getwhere('categories',$where,1);

  if ($query-> num_rows() > 0){
   $data = $query->row_array();
  }

  $query->free_result();
  return $data;
 }

This function passes in an $id variable that is used to select one row matching that ID from the categories table. It then returns the data as an array for future use.
Now we can select our category one by one with the function we just made. Next we have to create a function that returns all the categories in our categories table.

view plaincopy to clipboardprint?

1. function getAllCategories(){  
2.     $data = array();  
3.     $query = $this->db->get('categories');  
4.   
5.     if ($query->num_rows() > 0){  
6.         foreach($query->result_array() as $row){  
7.         $data[] = $row;  
8.         }  
9.     }  
10.     $query-> free_result();  
11.     return $data;  
12. }  

function getAllCategories(){
  $data = array();
  $query = $this->db->get('categories');

  if ($query->num_rows() > 0){
   foreach($query->result_array() as $row){
   $data[] = $row;
   }
  }
  $query-> free_result();
  return $data;
 }

For more details about CI database class check the userguide.
Your categorymodel.php should look like this now.

view plaincopy to clipboardprint?

1. <?php  
2.   
3. class categoryModel extends Model{  
4.   
5.     function categoryModel(){  
6.         parent::Model();  
7.     }  
8.   
9.     function getCategory($id){  
10.   
11.         $data = array();  
12.   
13.         $where = array('cat_id' => $id);  
14.   
15.         $query = $this->db->getwhere('categories',$where,1);  
16.   
17.         if ($query-> num_rows() > 0){  
18.             $data = $query->row_array();  
19.         }  
20.   
21.         $query->free_result();  
22.         return $data;  
23.     }  
24.   
25.     function getAllCategories(){  
26.         $data = array();  
27.         $query = $this->db->get('categories');  
28.   
29.         if ($query->num_rows() > 0){  
30.             foreach($query->result_array() as $row){  
31.             $data[] = $row;  
32.             }  
33.         }  
34.         $query-> free_result();  
35.         return $data;  
36.     }  
37. }  

<?php

class categoryModel extends Model{

 function categoryModel(){
  parent::Model();
 }

 function getCategory($id){

  $data = array();

  $where = array('cat_id' => $id);

  $query = $this->db->getwhere('categories',$where,1);

  if ($query-> num_rows() > 0){
   $data = $query->row_array();
  }

  $query->free_result();
  return $data;
 }

 function getAllCategories(){
  $data = array();
  $query = $this->db->get('categories');

  if ($query->num_rows() > 0){
   foreach($query->result_array() as $row){
   $data[] = $row;
   }
  }
  $query-> free_result();
  return $data;
 }
}

We will be adding another functions in our category model later as we go on to the tutorial.

Creating the Products Model

Now create a new file name it productsmodel.php in your /system/application/models/ folder. And copy the code below.

view plaincopy to clipboardprint?

1. <?php  
2.   
3. class productsModel extends Model{  
4.   
5.     function productsModel(){  
6.   
7.         parent::Model();  
8.   
9.     }  
10.   
11.     function getProduct($id){  
12.   
13.         $data = array();  
14.   
15.         $where = array('prod_id' => $id);  
16.   
17.         $query = $this->db->getwhere('products',$where,1);  
18.   
19.             if ($query-> num_rows() > 0){  
20.                 $data = $query-> row_array();  
21.             }  
22.   
23.         $query->free_result();  
24.   
25.         return $data;  
26.   
27.     }  
28.   
29.     function getAllProducts(){  
30.   
31.         $data = array();  
32.   
33.         $query = $this-> db-> get('products');  
34.   
35.         if ($query-> num_rows() > 0){  
36.             foreach ($query-> result_array() as $row){  
37.             $data[] = $row;  
38.             }  
39.         }  
40.   
41.         $query-> free_result();  
42.   
43.         return $data;  
44.     }  
45. }  

<?php

class productsModel extends Model{

 function productsModel(){

  parent::Model();

 }

 function getProduct($id){

  $data = array();

  $where = array('prod_id' => $id);

  $query = $this->db->getwhere('products',$where,1);

   if ($query-> num_rows() > 0){
    $data = $query-> row_array();
   }

  $query->free_result();

  return $data;

 }

 function getAllProducts(){

  $data = array();

  $query = $this-> db-> get('products');

  if ($query-> num_rows() > 0){
   foreach ($query-> result_array() as $row){
   $data[] = $row;
   }
  }

  $query-> free_result();

  return $data;
 }
}

As you can see our product model is very similar to our category model. The only difference between the two in the model name and the name of the database table.
As we go ahead later on this series maybe we need to add more functions to this models like for example a function that will retrieved all the products by a category id. But that’s it for now.
Now we need to auto load our models so that it will be available globally and saves us time in loading it locally in our controller. To do so, open the file /system/application/config/autload.php. Find $autoload option and add the following line.

view plaincopy to clipboardprint?

1. $autoload['model'] = array('categoryModel', 'productsModel');  

$autoload['model'] = array('categoryModel', 'productsModel');

Also we need to autoload the database library.

view plaincopy to clipboardprint?

1. $autoload['libraries'] = array('database');  

$autoload['libraries'] = array('database');

Insert a dummy data to your table. You can use my dummy data if you want.
For the categories table.

view plaincopy to clipboardprint?

1. INSERT INTO `ci_tutorial`.`categories` (  
2. `cat_id` ,  
3. `cat_name` ,  
4. `cat_shortdesc` ,  
5. `cat_longdesc` ,  
6. `cat_parentid`  
7. )  
8. VALUES  
9. ('1', 'Photo Books', 'This is a photo books', 'This is a long description of the photo book category.', '0'),  
10. ('2', 'Photo Calendar', 'This is a photo calendar', 'This is a long description of the Photo Calendar category.', '0'),  
11. ('3', 'Photo Tumblers', 'This is a photo Tumbler', 'This is a long description of the photo tumbler category.', '0'),  
12. ('4', 'Photo Magnets', 'This is a photo magnets', 'This is a long description of the Photo magenets category.', '0');  

INSERT INTO `ci_tutorial`.`categories` (
`cat_id` ,
`cat_name` ,
`cat_shortdesc` ,
`cat_longdesc` ,
`cat_parentid`
)
VALUES
('1', 'Photo Books', 'This is a photo books', 'This is a long description of the photo book category.', '0'),
('2', 'Photo Calendar', 'This is a photo calendar', 'This is a long description of the Photo Calendar category.', '0'),
('3', 'Photo Tumblers', 'This is a photo Tumbler', 'This is a long description of the photo tumbler category.', '0'),
('4', 'Photo Magnets', 'This is a photo magnets', 'This is a long description of the Photo magenets category.', '0');

For the products table.

view plaincopy to clipboardprint?

1. INSERT INTO `products` (`prod_id`, `prod_name`, `prod_shortdesc`, `prod_longdesc`, `prod_thumbnail`, `prod_image`, `prod_status`, `prod_category_id`, `prod_featured`, `prod_price`) VALUES  
2. (1, 'Large Hard Cover Photo Book', 'Short Description of Large Hard Cover Photo Book', 'Long Description of Large Hard Cover Photo Book', '/uploads/products/thumb/lhc-photo-books.jpg', '/uploads/products/lhc-photo-books.jpg', 1, 1, 1, 20.50),  
3. (2, 'Desktop Tumbler', 'Short Description  Desktop Tumbler', 'Long Description Desktop Tumbler', '/uploads/products/thumb/d-tumbler.jpg', '/uploads/products/d-tumbler.jpg', 1, 3, 0, 15.23);  

INSERT INTO `products` (`prod_id`, `prod_name`, `prod_shortdesc`, `prod_longdesc`, `prod_thumbnail`, `prod_image`, `prod_status`, `prod_category_id`, `prod_featured`, `prod_price`) VALUES
(1, 'Large Hard Cover Photo Book', 'Short Description of Large Hard Cover Photo Book', 'Long Description of Large Hard Cover Photo Book', '/uploads/products/thumb/lhc-photo-books.jpg', '/uploads/products/lhc-photo-books.jpg', 1, 1, 1, 20.50),
(2, 'Desktop Tumbler', 'Short Description  Desktop Tumbler', 'Long Description Desktop Tumbler', '/uploads/products/thumb/d-tumbler.jpg', '/uploads/products/d-tumbler.jpg', 1, 3, 0, 15.23);

To test our models if it’s working, open your test controller you just made in the first part of the tutorial. And populate the following code below.

view plaincopy to clipboardprint?

1. <?php  
2.   
3. class test extends Controller {  
4.   
5.     function test()  
6.     {  
7.         parent::Controller();  
8.         $this->load->library('parser');  
9.     }  
10.   
11.     function index()  
12.     {  
13.         $data['title']  = 'Building an e-commerce application using CodeIgniter ';  
14.         $data['header'] = 'Header of the Test Page';  
15.         $data['body']   = 'Test content here. Another content gooes here.';  
16.   
17.         $id = '1'; //sample value of cat_id  
18.   
19.         //test our category model  
20.         $myCat = $this->categoryModel->getCategory($id);  
21.   
22.         $data['myCat'] = $myCat['cat_name'];  
23.         $data['allCategory'] = $this->categoryModel->getAllCategories();  
24.   
25.         //test our product model  
26.         $myProduct = $this->productsModel->getProduct($id);  
27.   
28.         $data['myProduct'] = $myProduct['prod_name'];  
29.         $data['allProducts'] = $this->productsModel->getAllProducts();  
30.   
31.         $this->parser->parse('test_page', $data);  
32.     }  
33.   
34. }  

<?php

class test extends Controller {

 function test()
 {
  parent::Controller();
  $this->load->library('parser');
 }

 function index()
 {
  $data['title']  = 'Building an e-commerce application using CodeIgniter ';
        $data['header'] = 'Header of the Test Page';
        $data['body']   = 'Test content here. Another content gooes here.';

        $id = '1'; //sample value of cat_id

        //test our category model
        $myCat = $this->categoryModel->getCategory($id);

        $data['myCat'] = $myCat['cat_name'];
        $data['allCategory'] = $this->categoryModel->getAllCategories();

        //test our product model
        $myProduct = $this->productsModel->getProduct($id);

        $data['myProduct'] = $myProduct['prod_name'];
        $data['allProducts'] = $this->productsModel->getAllProducts();

        $this->parser->parse('test_page', $data);
    }

}

Open your test view created in part 1 of this tutorial and populate the code below.

view plaincopy to clipboardprint?

1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
2. <html xmlns="http://www.w3.org/1999/xhtml">  
3. <head>  
4.   
5. <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />  
6. <title>Untitled Document</title>  
7.   
8. </head>  
9. <body>  
10.   
11. <h4>A Category With an ID of 1</h4>  
12.   
13. <p>{myCat}</p>  
14.   
15. <h4>All Category List</h4>  
16.     <ul>  
17.         <?php foreach($allCategory as $cat){?>  
18.             <li>  
19.             <?= $cat['cat_name']; ?>  
20.             </li>  
21.         <?php } ?>  
22.     </ul>  
23.   
24. <hr />  
25.   
26. <h4>A Product With an ID of 1</h4>  
27.   
28. <p>{myProduct}</p>  
29.   
30. <h4>All Product List</h4>  
31.   
32.     <ul>  
33.         <?php foreach($allProducts as $product){?>  
34.             <li>  
35.             <?= $product['prod_name']; ?>  
36.             </li>  
37.         <?php } ?>  
38.     </ul>  
39.   
40. </body>  
41. </html>  

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>

</head>
<body>

<h4>A Category With an ID of 1</h4>

<p>{myCat}</p>

<h4>All Category List</h4>
 <ul>
  <?php foreach($allCategory as $cat){?>
   <li>
   <?= $cat['cat_name']; ?>
   </li>
  <?php } ?>
 </ul>

<hr />

<h4>A Product With an ID of 1</h4>

<p>{myProduct}</p>

<h4>All Product List</h4>

 <ul>
  <?php foreach($allProducts as $product){?>
   <li>
   <?= $product['prod_name']; ?>
   </li>
  <?php } ?>
 </ul>

</body>
</html>

Now open your browser and go to the address of your application installation, in this tutorial our address is http://localhost/tutorial/. Since we have to view our test controller, type this url http://localhost/tutorial/test/
You must now see a list of categories and products we just have inserted lately to our database.