Articles


Our first module, part 3: creating a database table

Difficulty: Easy

Now we have the add-on’s files created, and we can install and remove it, lets started adding the functionality.

In order to store details of who has bid for what, the module will need its own database table. This table needs to store:

  • who has made the bid
  • which item the bid was for
  • the value of the bid
  • what time the bid was placed

Remember, that we will be re-using as much built-in EE functionality as we can, so all the data about the auction item is going to be stored in a channel entry, and only logged-in members can bid.

This means we the data need to store is:

  • the member id of the currently logged in user
  • the entry id of the relevant channel entry
  • the value of the bid
  • the current time

Creating the database table

This table needs to be created when the module is installed, so we’ll add it to the install method of our upd.auction.php file.

We’ll modify the install function of upd.auction.php to create the database table:

public function install()
{
    $mod_data 
= array(
        
'module_name' => $this->module_name,
        
'module_version' => $this->version,
        
'has_cp_backend' => "y",
        
'has_publish_fields' => 'n'
    
);
    
$this->EE->db->insert('modules'$mod_data);
    
    
$this->EE->load->dbforge();
    
    
$fields = array(
        
'id' => array(
            
'type' => 'int',
            
'constraint' => '10',
            
'unsigned' => TRUE,
            
'auto_increment'=> TRUE
        
),
        
'entry_id' => array(
            
'type' => 'int',
            
'constraint' => '10',
            
'unsigned' => TRUE,
            
'null' => FALSE
        
),
        
'member_id' => array(
            
'type' => 'int',
            
'constraint' => '10',
            
'unsigned' => TRUE,
            
'null' => FALSE
        
),
        
'bid_amount' => array(
            
'type' => 'decimal',
            
'constraint' => '7,2',
            
'default' => '0.00',
            
'null' => FALSE
        
),
        
'bid_date' => array(
            
'type' => 'int',
            
'constraint' => '10',
            
'unsigned' => TRUE,
            
'default' => '0',
            
'null' => FALSE
        
)
    );
    
$this->EE->dbforge->add_field($fields);
    
$this->EE->dbforge->add_key('id'TRUE);
    
$this->EE->dbforge->create_table('auction');
    
    return 
TRUE;

This sets up an array of fields that we want to add. As well as the 4 fields we identified above, its good practise to include a primary key field to uniquely identify each record.

CodeIgniter provides a Database Forge class to make it easier to manage databases. Note, we don’t need to add the prefix that EE uses at the start of the table (by default, exp_) - although we could - the EE database functions are intelligent enough to do the right thing.

How do we know what type of field we need?

In our case three of our fields - the member id, the entry id and the time - are all common fields in the EE database. The member id and entry id are primary keys in the exp_members and exp_channel_titles tables so it makes sense to use the same definitions as those tables:

`entry_idint(10unsigned NOT NULL
`member_idint(10unsigned NOT NULL 

The bid amount will be a currency value, so we can use:

`bid_amountdecimal(7,2NOT NULL DEFAULT '0.00' 

for this field.

In EE, dates and times are stored as UTC (Universal Coordinated Time, or GMT, Greenwich Mean Time) timestamps. The timestamp is the number of seconds after midnight January 1st 1970. Again, by inspecting the existing database tables, we can find a suitable definition for this field:

`bid_dateint(10unsigned NOT NULL DEFAULT '0' 

Removing the database table

Just as we create the table when installing the module, we must remove it when we un-install the module.

We do that by modifying the uninstall function of upd.auction.php:

public function uninstall()
{
    $this
->EE->db->select('module_id');
    
$query $this->EE->db->get_where('modules'
        array( 
'module_name' => $this->module_name )
    );
    
    
$this->EE->db->where('module_id'$query->row('module_id'));
    
$this->EE->db->delete('module_member_groups');
    
    
$this->EE->db->where('module_name'$this->module_name);
    
$this->EE->db->delete('modules');
    
    
$this->EE->db->where('class'$this->module_name);
    
$this->EE->db->delete('actions');
    
    
$this->EE->db->where('class'$this->module_name.'_mcp');
    
$this->EE->db->delete('actions');
    
    
$this->EE->load->dbforge();
    
$this->EE->dbforge->drop_table('auction');
    
    return 
TRUE;

Again, we are using the CI Database Forge class to ‘drop’ the table.

Testing the module

Let’s make sure this works before we proceed by installing and removing the module a couple of times. After you have installed the module, you should check and see the table has been created. If this has worked, remove the module and check the table has been removed too.

Summary

The module now creates a database table to store our auction data when the module is installed, and cleans up after itself if the module is removed.

 

Comments

Hey there, thanks for this series. I find that installing and creating the database works okay, but when I uninstall, I get errors. I’m working with the identical code, but I’m calling the module Subsite Helper (short name: subsite_helper). With this code:

public function uninstall()
{
    $this->EE->db->select(‘module_id’);
    $query = $this->EE->db->get_where(‘modules’,
      array( ‘module_name’ => $this->module_name )
    );
   
    $this->EE->db->where(‘module_id’, $query->row(‘module_id’));
    $this->EE->db->delete(‘module_member_groups’);
   
    $this->EE->db->where(‘module_name’, $this->module_name);
    $this->EE->db->delete(‘modules’);
   
    $this->EE->db->where(‘class’, $this->module_name);
    $this->EE->db->delete(‘actions’);
   
    $this->EE->db->where(‘class’, $this->module_name.‘_mcp’);
    $this->EE->db->delete(‘actions’);
   
    $this->EE->load->dbforge();
    $this->EE->dbforge->drop_table(‘subsite_helper’);
   
    return TRUE;
}

I get these errors, and the module doesn’t uninstall:

Severity: Notice
Message: Undefined property: Subsite_helper_upd::$module_name
Filename: subsite_helper/upd.subsite_helper.php

Severity: Warning
Message: Cannot modify header information - headers already sent by (output started at /home/yoursitepath/public_html/system/codeigniter/system/core/Exceptions.php:170)
Filename: core/Common.php
Line Number: 442

Error Number: 1054
Unknown column ‘Array’ in ‘where clause’
DELETE FROM `exp_module_member_groups` WHERE `module_id` = Array
Filename: third_party/subsite_helper/upd.subsite_helper.php
Line Number: 104

Posted by Rob B on June 1, 2014

Add a comment

(Your email address will not be displayed on the site, but will be used for your gravatar)

Notify me of follow-up comments?