When developing an online store on OpenCart, sooner or later you need to work directly with the database. This could be adding new fields, optimizing queries, or creating custom order processing logic.
Database Structure
OpenCart uses a relational database (usually MySQL), where each entity is stored in a separate table.
Main tables:
Orders:
oc_order
oc_order_product
oc_order_option
oc_order_history
Products:
oc_product
oc_product_description
oc_product_option
Users:
oc_user
oc_customer
How to Work Correctly with the Database
OpenCart has a built-in class for working with the database — $this->db.
Getting data
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order");
foreach ($query->rows as $row) {
// data processing
}
Updating data
$this->db->query("
UPDATE " . DB_PREFIX . "order
SET ex_f_date = '" . $this->db->escape($date) . "'
WHERE order_id = '" . (int)$order_id . "'
");
Adding a custom field
if (!$this->db->query("SHOW COLUMNS FROM `" . DB_PREFIX . "order` LIKE 'ex_f_date'")->num_rows) {
$this->db->query("
ALTER TABLE `" . DB_PREFIX . "order`
ADD `ex_f_date` DATETIME NULL
");
}
Security
Use (int) for numbers
Use $this->db->escape() for strings
Optimization through JOIN
$query = $this->db->query("
SELECT o.order_id, op.name, op.quantity
FROM " . DB_PREFIX . "order o
LEFT JOIN " . DB_PREFIX . "order_product op
ON o.order_id = op.order_id
");
Indexes
ALTER TABLE oc_order ADD INDEX (ex_f_date);
Architecture
model — database operations
controller — logic
view — display
Conclusion
Working with the database in OpenCart is the foundation for creating custom functionality, optimization, and scaling your store.
