We will show you the way to create the admin form and the list page, after this we will move forward to make the frontend pages where visitors can submit their feedback and lists of the feedback. As always, we will start with analyzing our requirements and seeing which part of OpenCart resembles them, so that we can clone the pages, making it easy to work with the code.
Database tables for feedback
We start by making tables at the database. As OpenCart is multistore, multilanguage support and can be shown at many layouts, we need to take care of those as well. For these, we have to make approximately four tables: feedback, feedback_ description, feedback_to_layout, and feedback_to_store.
In the following screenshot, oc_ is the database prefix we use while installing Opencart. If you are not sure about the database prefix, you can see the config. php file at the root folder of the OpenCart, open it, and find the line "define('DB_ PREFIX'". You will see define('DB_PREFIX', 'oc_'); and as per this the database prefix is oc_. The oc_feedback table stores the status, sort order, date added, and date modified with the feedback ID. The oc_feedback_description table stores the author name, feedback given, and language ID for multiple languages. The oc_feedback_to_ store table saves the store ID and feedback for the particular store of OpenCart whose feedback needs to be shown as OpenCart are multistores, and the oc_feedback_to_ layout table stores to whichever layout the feedback module is to be shown.
The following screenshot shows the database schema:
The following are the queries that need to run in the database to create the feedback table, feedback description table, feedback to layout table, and feedback to store table. If you have used a prefix other than the oc_, change oc_ to that prefix on the following query; only then it will be ready to run.
CREATE TABLE IF NOT EXISTS `oc_feedback` (
`feedback_id` int(11) NOT NULL AUTO_INCREMENT,
`sort_order` int(3) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL,
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`feedback_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
CREATE TABLE IF NOT EXISTS `oc_feedback_description` (
`feedback_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`feedback_author` varchar(255) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`feedback_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `oc_feedback_to_layout` (
`feedback_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL,
`layout_id` int(11) NOT NULL,
PRIMARY KEY (`feedback_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `oc_feedback_to_store` (
`feedback_id` int(11) NOT NULL,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`feedback_id`,`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
After running the preceding query on database, we now will start to make the custom page to list out all the feedback with pagination and a form to edit and insert the feedback at the admin section. Then, we will move to the frontend pages. As you know, OpenCart follows the MVC framework, so you need to manage the files likewise. For the feedback, you need to create files as shown in the following screenshot:
Creating files at the admin section for feedback
At the admin section, we will create files that will create a list of feedback and also a form to insert or edit the feedback and save it into the database. For this, we will start with the language file, which is the easiest one.
Creating the language file at the admin section
Create a file at admin/language/english/catalog/feedback.php, and paste the following lines of code:
<?php
$_['heading_feedback']= 'Feedback';
$_['heading_feedback_author']= 'Feedback';
$_['text_success'] = 'Success: You have modified feedback!';
$_['text_default'] = 'Default';
$_['column_feedback_author']= 'Feedback Author';
$_['column_sort_order'] = 'Sort Order';
$_['column_action'] = 'Action';
$_['entry_feedback_author']= 'Feedback Author:';
$_['entry_description'] = 'Feedback Description:';
$_['entry_store'] = 'Stores:';
$_['entry_status'] = 'Status:';
$_['entry_sort_order'] = 'Sort Order:';
$_['entry_layout'] = 'Layout Override:';
$_['error_warning'] = 'Warning: Please check the form carefully for errors!';
$_['error_permission'] = 'Warning: You do not have permission to modify feedback!';
$_['error_description'] = 'Description must be more than 3 characters!';
$_['error_store'] = 'Warning: This feedback page cannot be deleted as its currently used by %s stores!'; ?>
The preceding lines of code are written to describe the text that is set to variable for the language, which can be accessed and used in the controller files.
Creating the model file at the admin section
To create a model file, you need to make a model folder, and in this folder, it will be called at the controller as $this->load->model(FOLDER_NAME/FILE_NAME_ WITHOUT_EXTENSION'). For the feedback, you have to create a file named feedback.
php at admin/model/catalog/feedback.php. Thus, you can load this file at controller as this->load->model('catalog/feedback').
After creating the file, you need to make a unique class name starting with the word Model, followed by the folder name, and then file name without extensions. So, for our feedback, the class name will beModelCatalogFeedback, which extends the parent Model class.
<?php
class ModelCatalogFeedback extends Model { public function addfeedback($data) {
$this->db->query("INSERT INTO " . DB_PREFIX . "feedback SET sort_order = '" . (int)$data['sort_order'] . "', status = '"
. (int)$data['status'] . "'");
$feedback_id = $this->db->getLastId();
foreach ($data['feedback_description'] as $language_id => $value) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_description SET feedback_id = '" .
(int)$feedback_id . "', language_id = '" .
(int)$language_id . "', feedback_author= '" . $this->db
->escape($value['feedback_author']) . "', description = '"
. $this->db->escape($value['description']) . "'");
}
if (isset($data['feedback_store'])) { foreach ($data['feedback_store'] as $store_id) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_to_store SET feedback_id = '" .
(int)$feedback_id . "', store_id = '" . (int)$store_id .
"'");
}
}
if (isset($data['feedback_layout'])) {
foreach ($data['feedback_layout'] as $store_id => $layout) { if ($layout) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_to_layout SET feedback_id = '" .
(int)$feedback_id . "', store_id = '" . (int)$store_id . "', layout_id = '" . (int)$layout['layout_id'] .
"'");
}
}
}
$this->cache->delete('feedback');
}
}
The preceding code shows how we can query the database. We have to start with $this->db->query() and inside the braces we write the SQL query that we have already seen in the global methods inChapter 2, Describing The Code of Extensions. As per the preceding code, $this->db->query() inserts the feedback ID, sort order, and status on the feedback table and retrieves the feedback ID that was inserted last and assigns it to $feedback_id. Also, $data['feedback_description'] is looped as you can have multiple descriptions because it can contain many languages. Thus, it inserts the feedback ID, language ID, author, and feedback description into the description table. As OpenCart supports the multistore and multiple layouts, you must take care of them. After the insertion of the description, we have to run the store query to insert the store followed by the layout insertion. Then a cache is deleted if it was already created.
public function editfeedback($feedback_id, $data) {
$this->db->query("UPDATE " . DB_PREFIX . "feedback SET sort_order = '" . (int)$data['sort_order'] . "', status = '" .
(int)$data['status'] . "' WHERE feedback_id = '" .
(int)$feedback_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX .
"feedback_description WHERE feedback_id = '" .
(int)$feedback_id . "'");
foreach($data['feedback_description'] as $language_id => $value) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_description SET feedback_id = '" .
(int)$feedback_id . "', language_id = '" . (int)$language_id
. "', feedback_author= '" . $this->db ->escape($value['feedback_author']) . "', description = '" .
$this->db->escape($value['description']) . "'");
}
$this->db->query("DELETE FROM " . DB_PREFIX . "feedback_to_store
WHERE feedback_id = '" . (int)$feedback_id . "'"); if (isset($data['feedback_store'])) { foreach ($data['feedback_store'] as $store_id) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_to_store SET feedback_id = '" .
(int)$feedback_id . "', store_id = '" . (int)$store_id .
"'");
}
}
$this->db->query("DELETE FROM " . DB_PREFIX .
"feedback_to_layout WHERE feedback_id = '" . (int)$feedback_id
. "'");
if (isset($data['feedback_layout'])) {
foreach ($data['feedback_layout'] as $store_id => $layout) { if ($layout['layout_id']) {
$this->db->query("INSERT INTO " . DB_PREFIX .
"feedback_to_layout SET feedback_id = '" .
(int)$feedback_id . "', store_id = '" . (int)$store_id .
"', layout_id = '" . (int)$layout['layout_id'] . "'");
}
}
}
$this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'feedback_id=" . (int)$feedback_id. "'");
$this->cache->delete('feedback');
}
The queries update the database table row of feedback, feedback description, feedback store, and feedback layout. The first query shown in the code will update the feedback table row, but for other tables of feedback description, feedback store, and feedback layout, it first deletes all the related feedback as per the feedback ID and then inserts them again. When the feedback table is updated, it deletes all the related feedback description in the feedback_description table and then inserts the updated data; although no changes are made, it takes them as the new value and inserts this in the loop. The same is done for feedback_to_layout and feedback_ to_store. Then it deletes the cache if it is already created.
public function deletefeedback($feedback_id) {
$this->db->query("DELETE FROM " . DB_PREFIX . "feedback WHERE feedback_id = '" . (int)$feedback_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX .
"feedback_description WHERE feedback_id = '" .
(int)$feedback_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "feedback_to_store
WHERE feedback_id = '" . (int)$feedback_id . "'");
$this->db->query("DELETE FROM " . DB_PREFIX .
"feedback_to_layout WHERE feedback_id = '" . (int)$feedback_id
. "'");
$this->cache->delete('feedback');
}
The preceding code is used to delete the feedback; you have to take care to delete data from all the tables whenever you use the delete operation. As per our feedback, you have to delete data from thefeedback, feedback_description, feedback_to_ store, and feedback_to_layout tables as well as the cache file.
public function getfeedback($feedback_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX .
"feedback WHERE feedback_id = '" . (int)$feedback_id . "'"); return $query->row; }
The preceding code snippet is used to retrieve a row; to run a select query, you have to run the query with $this->db->query(), and then assign to some variable and run with $Variable_Name->row;. To retrieve a single column and to retrieve multiple rows, we have to write $Variable_Name->rows;, which returns an array. As per our SQL query, we just need a single row of the specified feedback ID so we have performed $query->row;.
public function getfeedbackDescriptions($feedback_id) { $feedback_description_data = array();
$query = $this->db->query("SELECT * FROM ".DB_PREFIX .
"feedback_description WHERE feedback_id ='". (int)$feedback_id
."'");
foreach ($query->rows as $result) {
$feedback_description_data[$result['language_id']] = array(
'feedback_author' => $result['feedback_author'],
'description' => $result['description']);
}return $feedback_description_data;
}
The preceding code retrieves the description of the respective feedback ID passed and will return all the languages' description as well as return the description in an array.
public function getTotalFeedbacks() {
$query =$this->db->query("SELECT COUNT(*) AS total FROM
".DB_PREFIX."feedback"); return $query->row['total']; }
The preceding lines of code return the total number of feedback.
public function getfeedbacks($data = array()) { if ($data) {
$sql = "SELECT * FROM " . DB_PREFIX . "feedback f LEFT JOIN " . DB_PREFIX . "feedback_description fd ON (f.feedback_id = fd.feedback_id) WHERE fd.language_id = '" . (int)$this
->config->get('config_language_id') . "'";
$sort_data = array('fd.feedback_author','f.sort_order'); if (isset($data['sort']) &&in_array($data['sort'],
$sort_data)) {
$sql .= " ORDER BY " . $data['sort'];
} else {$sql .= " ORDER BY fd.feedback_author";
}
if (isset($data['order']) && ($data['order'] == 'DESC')) {
$sql .= " DESC";
} else {$sql .= " ASC";
}
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;
} else {
$feedback_data = $this->cache->get('feedback.' . (int)$this-
>config->get('config_language_id')); if (!$feedback_data) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX .
"feedback f LEFT JOIN " . DB_PREFIX . "feedback_description fd ON (f.feedback_id = fd.feedback_id) WHERE fd.language_id = '" . (int)$this->config->get('config_language_id') . "'
ORDER BY fd.feedback_id.");
$feedback_data = $query->rows;
$this->cache->set('feedback.' . (int)$this->config
->get('config_language_id'), $feedback_data);
}
return $feedback_data;
}
}
For retrieving all the feedback from the database we use the preceding code. The $data array, which is passed in the function, holds the sort order, order by, limit of rows, and helps in filtering, sorting, and limiting the rows from the whole data. If $data is set, it retrieves data from the SQL query and retrieves the required rows by filtering as per $data; else it tries to retrieve from the cache files if it is already set. If it does not find the cache, it again runs the query and retrieves the rows of feedback and sets the cache and returns the array of feedback. It will retrieve the data from the feedback andfeedback_description table and return as an array. It is sorted by passed data as name or so on, else by default, it is sorted by $feedback_id.
public function getfeedbackStores($feedback_id) { $feedback_store_data = array();
$query = $this->db->query("SELECT * FROM " . DB_PREFIX .
"feedback_to_store WHERE feedback_id = '" . (int)$feedback_id
. "'");
foreach ($query->rows as $result) {
$feedback_store_data[] = $result['store_id'];
}
return $feedback_store_data; }
The preceding code returns all stores that the specified feedback ID passed.
public function getfeedbackLayouts($feedback_id) { $feedback_layout_data = array();
$query = $this->db->query("SELECT * FROM " . DB_PREFIX .
"feedback_to_layout WHERE feedback_id = '" . (int)$feedback_id
. "'");
foreach ($query->rows as $result) {
$feedback_layout_data[$result['store_id']] =
$result['layout_id'];
}
return $feedback_layout_data; }
The preceding code returns all the layouts of the specified feedback ID passed.
public function getTotalfeedbacksByLayoutId($layout_id) {
$query = $this->db->query("SELECT COUNT(*) AS total FROM " .
DB_PREFIX . "feedback_to_layout WHERE layout_id = '" .
(int)$layout_id . "'"); return $query->row['total'];
}
?>
The getTotalfeedbacksByLayout function will return the number of feedback counts that the layout_id has passed and closes the main model class. In this way, you can create the model file and make any kinds of data retrieval, insertion, and deletion work and these will be used on the controller files by loading the model file.
No comments:
Post a Comment