OpenCart – MYSQL Basics

I think that it is important to point out some important facts about how Opencart uses MYSQL in it’s eCommerce application. Some download the Opencart and are not really sure where things are stored or how. Perhaps, the more knowledge you have about this then the better off you will be when it comes time to customizing in the future. Virtually all eCommerce stores are run on a Database because there is a necessity to store information where it can later be accessed.

What is MYSQL?

  • MySQL is a database system used on the web
  • MySQL is a database system that runs on a server
  • MySQL is ideal for both small and large applications
  • MySQL is very fast, reliable, and easy to use
  • MySQL supports standard SQL
  • MySQL compiles on a number of platforms
  • MySQL is free to download and use
  • MySQL is developed, distributed, and supported by Oracle Corporation
  • MySQL is named after co-founder Monty Widenius’s daughter: My

Basically you can look at it like a storage container on the web for Data, both sensitive and non-sensitive data. Opencart, just like any other eCommerce platform needs a place to store data such as order, products, settings etc. These are items that have nothing to do with the browser session or cookies in terms of the user experience. Rather, they are permanent until changed. A database like MYSQL allows you to keep these things in one particular area for as long as you need them. When you are ready, you can access these items whenever want with what is known as a QUERY.

WHAT IS A QUERY?

A query is a question or a request.

We can query a database for specific information and have a record set returned.

Types of query’s used in basic SQL:

  1. SELECT (ex: SELECT lastname FROM table)
  2. INSERT (ex: INSERT INTO table SET())
  3. UPDATE (ex: UPDATE table SET())
  4. DELETE (ex: DELETE FROM table)

Within the Opencart MVC-L framework there are many Querys that can be found written in PHP. PHP is a server side scripting language that works hand in hand with MYSQL. Typically, you run query’s based on your PHP procedures. Here is an example of a SELECT query used by the Opencart:

public function getProductRelated($product_id) {
        $product_data = array();

        $query = $this->db->query(“SELECT * FROM ” . DB_PREFIX . “product_related pr LEFT JOIN ” . DB_PREFIX . “product p ON (pr.related_id = p.product_id) LEFT JOIN ” . DB_PREFIX . “product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = ‘” . (int)$product_id . “‘ AND p.status = ‘1’ AND p.date_available <= NOW() AND p2s.store_id = ‘” . (int)$this->config->get(‘config_store_id’) . “‘”);
        
        foreach ($query->rows as $result) {
            $product_data[$result['related_id']] = $this->getProduct($result['related_id']);
        }
        
        return $product_data;
    }

Yes, this looks a little different than basic SQL SYNTAX that you would find in the database console because it is PHP.

OTHER IMPORTANT MYSQL DETAILS

Most of the tables that are found within the Opencart Database Schema have a Primary Key. This KEY is normally a field that is set to AUTO INCREMENT. This allows for each record(ROW) of data in that table to have a unique identifier because since it is auto incrementing it will NEVER BE THE SAME NUMBER, EVER. This Primary Key can be used in other tables as well to set up special relationships. Opencart is FULL of special relationships where more than one table share an identifier. This is better known as RDBMS.

What is RDBMS?

RDBMS (relational database management system) is a logic that is based on relationships specified by identifying ID’s.

Moving on, when you install your Opencart for the first time you will go through a series of questions that have to do with setting up your Database. Once you fill in the information correctly and run the Install, what is really happening is you are QUERYING your MYSQL DATABASE with a .sql file that comes in the install folder. In short, you are filling in your Database Tables. You will have already set up a database from your web server level in order to do this. I hope this helps in understanding how MYSQL plays a major role in the Opencart system.

Looking for quality OpenCart Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , | Posted under OpenCart | RSS 2.0

Author Spotlight

Joe Stenhouse

Joe Stenhouse

I am a web application developer that specializes in PHP, JAVASCRIPT, MYSQL, HTML, and CSS. We manifest exciting potentials within the world wide web through means of innovation.

Leave a Reply

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


5 − 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>