Creating Table while Installing module in OpenCart |
X

Congrats, You are Subscribed to Receive Updates.

Creating Table while Installing module in OpenCart


While developing a custom module to your opencart theme you need to have a separate table to manage your custom data’s and module information’s. So getting a custom table is necessary at the moment. Here i will give you a simple way of creating table while installing your module. Here i create an example table for coming soon section.

1). Create your custom module files like as shown below.

admin/controller/module/comingsoon.php

admin/language/module/comingsoon.php

admin/model/kvc/comingsoon.php

admin/view/template/module/comingsoon.tpl

 Now you are set to ready to write coding for your custom module. First open your controller class under admin directory and write your class file as like the following code.

<?php
class ControllerModuleComingsoon extends Controller {
	private $error = array();

	public function install() {
		$this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "comingsoon` (
		  `comingsoon_id` int(11) NOT NULL AUTO_INCREMENT,
		  `date_added` datetime NOT NULL,
		  `status` tinyint(1) NOT NULL,
		  PRIMARY KEY (`comingsoon_id`)
		)");

		$this->db->query("CREATE TABLE IF NOT EXISTS `" . DB_PREFIX . "comingsoon_description` (
		  `comingsoon_description_id` int(11) NOT NULL AUTO_INCREMENT,
		  `comingsoon_id` int(11) NOT NULL,
		  `language_id` int(11) NOT NULL,
		  `title` varchar(255) COLLATE utf8_bin NOT NULL,
		  `description` text COLLATE utf8_bin NOT NULL,
		  PRIMARY KEY (`comingsoon_description_id`)
		)");
	}

	public function index() {
		$this->install();
		$this->load->language('module/comingsoon');
		$this->load->model('module/comingsoon');

		$this->document->setTitle($this->language->get('heading_title'));

		$this->data['breadcrumbs'] = array();

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('text_home'),
			'href'      => $this->url->link('common/home', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => false
   		);

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('heading_title'),
			'href'      => $this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => ' :: '
   		);

		if (isset($this->session->data['success'])) {
			$this->data['success'] = $this->session->data['success'];

			unset($this->session->data['success']);
		} else {
			$this->data['success'] = '';
		}

		if (isset($this->session->data['warning'])) {
			$this->data['error'] = $this->session->data['warning'];

			unset($this->session->data['warning']);
		} else {
			$this->data['error'] = '';
		}

		$url = '';

		if (isset($this->request->get['page'])) {
			$page = $this->request->get['page'];
			$url .= '&page=' . $this->request->get['page'];
		} else {
			$page = 1;
		}

		$data = array(
			'page' => $page,
			'limit' => $this->config->get('config_admin_limit'),
			'start' => $this->config->get('config_admin_limit') * ($page - 1),
		);

		$total = $this->model_module_comingsoon->countComingsoon();

		$pagination = new Pagination();
		$pagination->total = $total;
		$pagination->page = $page;
		$pagination->limit = $this->config->get('config_admin_limit');
		$pagination->text = $this->language->get('text_pagination');
		$pagination->url = $this->url->link('module/comingsoon', 'token=' . $this->session->data['token'] . $url . '&page={page}', 'SSL');

		$this->data['pagination'] = $pagination->render();

		$this->data['heading_title'] = $this->language->get('heading_title');

		$this->data['text_title'] = $this->language->get('text_title');
		$this->data['text_date'] = $this->language->get('text_date');
		$this->data['text_action'] = $this->language->get('text_action');
		$this->data['text_edit'] = $this->language->get('text_edit');

		$this->data['button_insert'] = $this->language->get('button_insert');
		$this->data['button_delete'] = $this->language->get('button_delete');

		$this->data['insert'] = $this->url->link('module/comingsoon/insert', '&token=' . $this->session->data['token'], 'SSL');
		$this->data['delete'] = $this->url->link('module/comingsoon/delete', 'token=' . $this->session->data['token'], 'SSL');

		$this->data['allcomingsoon'] = array();

		$allcomingsoon = $this->model_module_comingsoon->getAllComingsoon($data);

		foreach ($allcomingsoon as $comingsoon) {
			$this->data['allcomingsoon'][] = array (
				'comingsoon_id' => $comingsoon['comingsoon_id'],
				'title' => $comingsoon['title'],
				'date_added' => date('d M Y', strtotime($comingsoon['date_added'])),
				'edit' => $this->url->link('module/comingsoon/edit', '&comingsoon_id=' . $comingsoon['comingsoon_id'] . '&token=' . $this->session->data['token'], 'SSL')
			);
		}

		$this->template = 'module/comingsoon_list.tpl';
		$this->children = array(
			'common/header',
			'common/footer'
		);

		$this->response->setOutput($this->render());
	}

	public function edit() {
		$this->load->language('module/comingsoon');
		$this->load->model('module/comingsoon');

		$this->document->setTitle($this->language->get('heading_title'));

		if (isset($this->session->data['warning'])) {
			$this->data['error'] = $this->session->data['warning'];

			unset($this->session->data['warning']);
		} else {
			$this->data['error'] = '';
		}

		if (!isset($this->request->get['comingsoon_id'])) {
			$this->redirect($this->url->link('module/comingsoon', '&token=' . $this->session->data['token'], 'SSL'));
		}

		if (($this->request->server['REQUEST_METHOD'] == 'POST') && $this->validate()) {
			$this->model_module_comingsoon->editComingsoon($this->request->get['comingsoon_id'], $this->request->post);

			$this->session->data['success'] = $this->language->get('text_success');

			$this->redirect($this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'));
		}

		$this->data['breadcrumbs'] = array();

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('text_home'),
			'href'      => $this->url->link('common/home', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => false
   		);

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('heading_title'),
			'href'      => $this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => ' :: '
   		);

		$this->data['action'] = $this->url->link('module/comingsoon/edit', '&comingsoon_id=' . $this->request->get['comingsoon_id'] . '&token=' . $this->session->data['token'], 'SSL');
		$this->data['cancel'] = $this->url->link('module/comingsoon', '&token=' . $this->session->data['token'], 'SSL');
		$this->data['token'] = $this->session->data['token'];

		$this->form();
	}

	public function insert() {
		$this->load->language('module/comingsoon');
		$this->load->model('module/comingsoon');

		$this->document->setTitle($this->language->get('heading_title'));

		if (isset($this->session->data['warning'])) {
			$this->data['error'] = $this->session->data['warning'];

			unset($this->session->data['warning']);
		} else {
			$this->data['error'] = '';
		}

		if (($this->request->server['REQUEST_METHOD'] == 'POST') && $this->validate()) {
			$this->model_module_comingsoon->addComingsoon($this->request->post);

			$this->session->data['success'] = $this->language->get('text_success');

			$this->redirect($this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'));
		}

		$this->data['breadcrumbs'] = array();

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('text_home'),
			'href'      => $this->url->link('common/home', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => false
   		);

   		$this->data['breadcrumbs'][] = array(
       		'text'      => $this->language->get('heading_title'),
			'href'      => $this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'),
      		'separator' => ' :: '
   		);

		$this->data['action'] = $this->url->link('module/comingsoon/insert', '&token=' . $this->session->data['token'], 'SSL');
		$this->data['cancel'] = $this->url->link('module/comingsoon', '&token=' . $this->session->data['token'], 'SSL');
		$this->data['token'] = $this->session->data['token'];

		$this->form();
	}

	private function form() {
		$this->load->language('module/comingsoon');
		$this->load->model('module/comingsoon');
		$this->load->model('localisation/language');

		$this->data['heading_title'] = $this->language->get('heading_title');

		$this->data['text_title'] = $this->language->get('text_title');
		$this->data['text_description'] = $this->language->get('text_description');
		$this->data['text_status'] = $this->language->get('text_status');
		$this->data['text_keyword'] = $this->language->get('text_keyword');
		$this->data['text_enabled'] = $this->language->get('text_enabled');
		$this->data['text_disabled'] = $this->language->get('text_disabled');

		$this->data['button_submit'] = $this->language->get('button_submit');
		$this->data['button_cancel'] = $this->language->get('button_cancel');

		$this->data['languages'] = $this->model_localisation_language->getLanguages();

		if (isset($this->request->get['comingsoon_id'])) {
			$comingsoon = $this->model_module_comingsoon->getComingsoon($this->request->get['comingsoon_id']);
		} else {
			$comingsoon = '';
		}

		if (isset($this->request->post['comingsoon'])) {
			$this->data['comingsoon'] = $this->request->post['comingsoon'];
		} elseif (!empty($comingsoon)) {
			$this->data['comingsoon'] = $this->model_module_comingsoon->getComingsoonDescription($this->request->get['comingsoon_id']);
		} else {
			$this->data['comingsoon'] = '';
		}

		if (isset($this->request->post['keyword'])) {
			$this->data['keyword'] = $this->request->post['keyword'];
		} elseif (!empty($comingsoon)) {
			$this->data['keyword'] = $comingsoon['keyword'];
		} else {
			$this->data['keyword'] = '';
		}

		if (isset($this->request->post['status'])) {
			$this->data['status'] = $this->request->post['status'];
		} elseif (!empty($comingsoon)) {
			$this->data['status'] = $comingsoon['status'];
		} else {
			$this->data['status'] = '';
		}

		$this->template = 'module/comingsoon_form.tpl';
		$this->children = array(
			'common/header',
			'common/footer'
		);

		$this->response->setOutput($this->render());
	}

	public function delete() {
		$this->load->language('module/comingsoon');
		$this->load->model('module/comingsoon');

		$this->document->setTitle($this->language->get('heading_title'));

		if (isset($this->request->post['selected']) && $this->validateDelete()) {
			foreach ($this->request->post['selected'] as $id) {
				$this->model_module_comingsoon->deleteComingsoon($id);
			}

			$this->session->data['success'] = $this->language->get('text_success');
		}

		$this->redirect($this->url->link('module/comingsoon', 'token=' . $this->session->data['token'], 'SSL'));
	}

	private function validateDelete() {
		if (!$this->user->hasPermission('modify', 'module/comingsoon')) {
			$this->error['warning'] = $this->language->get('error_permission');

			$this->session->data['warning'] = $this->language->get('error_permission');
		}

		if (!$this->error) {
			return true;
		} else {
			return false;
		}
	}

	private function validate() {
		if (!$this->user->hasPermission('modify', 'module/comingsoon')) {
			$this->error['warning'] = $this->language->get('error_permission');
			$this->session->data['warning'] = $this->language->get('error_permission');
		}

		if (!$this->error) {
			return true;
		} else {
			return false;
		}
	}
}
?>

open your language file and paste the following code.

<?php
// Heading
$_['heading_title']     = 'Coming Soon';

// Text
$_['text_title']	    = 'Title';
$_['text_description']	    = 'Description';
$_['text_date']		    = 'Date Added';
$_['text_action']	    = 'Action';
$_['text_status']	    = 'Status';
$_['text_keyword']	    = 'SEO Keyword';

// Success
$_['text_success']          = 'You have successfully modified news!';

// Error
$_['error_permission']      = 'Warning: You do not have permission to modify news!';
?>

and here is the model class codes.

<?php
class ModelModuleComingsoon extends Model {
	public function addComingsoon($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "comingsoon SET date_added = NOW(), status = '" . (int)$data['status'] . "'");

		$comingsoon_id = $this->db->getLastId();

		foreach ($data['comingsoon'] as $key => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX ."comingsoon_description SET comingsoon_id = '" . (int)$comingsoon_id . "', language_id = '" . (int)$key . "', title = '" . $this->db->escape($value['title']) . "', description = '" . $this->db->escape($value['description']) . "'");
		}

		if ($data['keyword']) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'comingsoon_id=" . (int)$comingsoon_id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
		}
	}

	public function editComingsoon($id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "comingsoon SET status = '" . (int)$data['status'] . "' WHERE comingsoon_id = '" . (int)$id . "'");

		$this->db->query("DELETE FROM " . DB_PREFIX . "comingsoon_description WHERE comingsoon_id = '" . (int)$id. "'");

		foreach ($data['comingsoon'] as $key => $value) {
			$this->db->query("INSERT INTO " . DB_PREFIX ."comingsoon_description SET comingsoon_id = '" . (int)$id . "', language_id = '" . (int)$key . "', title = '" . $this->db->escape($value['title']) . "', description = '" . $this->db->escape($value['description']) . "'");
		}

		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'comingsoon_id=" . (int)$id. "'");

		if ($data['keyword']) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'comingsoon_id=" . (int)$id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
		}
	}

	public function getComingsoon($id) {
		$query = $this->db->query("SELECT DISTINCT *, (SELECT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'comingsoon_id=" . (int)$id . "') AS keyword FROM " . DB_PREFIX . "comingsoon WHERE comingsoon_id = '" . (int)$id . "'");

		if ($query->num_rows) {
			return $query->row;
		} else {
			return false;
		}
	}

	public function getComingsoonDescription($id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "comingsoon_description WHERE comingsoon_id = '" . (int)$id . "'");

		foreach ($query->rows as $result) {
			$comingsoon_description[$result['language_id']] = array(
				'title'       => $result['title'],
				'description' => $result['description']
			);
		}

		return $comingsoon_description;
	}

	public function getAllComingsoon($data) {
		$sql = "SELECT * FROM " . DB_PREFIX . "comingsoon n LEFT JOIN " . DB_PREFIX . "comingsoon_description nd ON n.comingsoon_id = nd.comingsoon_id WHERE nd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY date_added DESC";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}
				if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function deleteComingsoon($id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "comingsoon WHERE comingsoon_id = '" . (int)$id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "comingsoon_description WHERE comingsoon_id = '" . (int)$id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'comingsoon_id=" . (int)$id. "'");
	}

	public function countComingsoon() {
		$count = $this->db->query("SELECT * FROM " . DB_PREFIX . "comingsoon");

		return $count->num_rows;
	}
}
?>

and finally admin view class file. I mean your template file just open it and paste the following code.(comingsoon.tpl).

<?php echo $header; ?>
<div id="content">
  <div class="breadcrumb">
    <?php foreach ($breadcrumbs as $breadcrumb) { ?>
    <?php echo $breadcrumb['separator']; ?><a href="<?php echo $breadcrumb['href']; ?>"><?php echo $breadcrumb['text']; ?></a>
    <?php } ?>
  </div>
  <?php if ($success) { ?>
  <div class="success"><?php echo $success; ?></div>
  <?php } ?>
  <?php if ($error) { ?>
  <div class="warning"><?php echo $error; ?></div>
  <?php } ?>
  <div class="box">
    <div class="heading">
      <h1><img src="view/image/feed.png" alt="" /> <?php echo $heading_title; ?></h1>
	  <div class="buttons"><a onclick="location = '<?php echo $insert; ?>'" class="button"><?php echo $button_insert; ?></a><a onclick="$('#form').submit();" class="button"><?php echo $button_delete; ?></a></div>
    </div>
    <div class="content">
	 <form action="<?php echo $delete; ?>" method="post" enctype="multipart/form-data" id="form">
      <table class="list">
	    <thead>
		<tr>
			<td width="1" style="text-align: center;"><input type="checkbox" onclick="$('input[name*=\'selected\']').attr('checked', this.checked);" /></td>
			<td class="left"><?php echo $text_title; ?></td>
			<td class="left"><?php echo $text_date; ?></td>
			<td class="right"><?php echo $text_action; ?></td>
		</tr>
		</thead>
		<tbody>
		<?php if ($allcomingsoon) { ?>
			<?php foreach ($allcomingsoon as $comingsoon) { ?>
				<tr>
					<td width="1" style="text-align: center;"><input type="checkbox" name="selected[]" value="<?php echo $comingsoon['comingsoon_id']; ?>" /></td>
					<td class="left"><?php echo $comingsoon['title']; ?></td>
					<td class="left"><?php echo $comingsoon['date_added']; ?></td>
					<td class="right">[ <a href="<?php echo $comingsoon['edit']; ?>"><?php echo $text_edit; ?></a> ]</td>
				</tr>
			<?php } ?>
		<?php } ?>
		</tbody>
	  </table>
	 </form>
	 <div class="pagination"><?php echo $pagination; ?></div>

    </div>
  </div>
</div>
<?php echo $footer; ?>

This how you can create a custom table while installing your module..  When you create a table you need to take care of install, insert, delete, and update functions for performing table operations. You custom module is created now you  can access it from your modules page.

table

commenter

About Varadharaj V

The founder of Kvcodes, Varadharaj V is an ERP Analyst and a Web developer specializing in WordPress(WP), WP Theme development, WP Plugin development, Frontaccounting(FA), Sales, Purchases, Inventory, Ledgers, Payroll & HRM, CRM, FA Core Customization, PHP and Data Analyst. Database Management Advance Level

6 comments

  1. commenter

    Please share comingsoon_form.tpl

    Best Regards,
    Vishal

  2. commenter

    hi ,comingsoon.tpl file just only for listing so is there any comingsoon form file..where we can add or edit data…

  3. commenter

    Change private function install() {
    }

    to public function install() {}

Comment Below

Your email address will not be published. Required fields are marked *

*

Current ye@r *

Menu

Sidebar