OpenCart – PHP Basics Part 3 (SQL QUERYS)

So here we are at part 3 of PHP Basics. In this article I will demonstrate how the OpenCart framework communicates with its database via PHP. Up to this point, if you have only connected to a Database “outside of a class” then the syntax will look a little different to you. You see, there is already a DB class that Opencart uses to control everything database so you won’t be seeing lines of code such as mysqli_connect() because it’s already taken care of. If you remember when you installed your Opencart the installer asked you for your Database credentials? It uses these not just to fill your database with the appropriate tables but also for all SELECT, UPDATE, DELETE, AND INSERT queries throughout the entire flow. Remember, this is an MVC-L framework (Model-View-Control-Langauge) where CONSTANTS are defined in a config file. These constants “which include your DB credentials are accessed constantly so there is no need to keep writing out mysqli_connect() or for that matter you don’t even have to escape input the normal way which would be mysql_real_escape_string(); Opencart already has that covered by typing: $this->db->escape();

Before we begin with a couple examples let’s just go through one possible scenario where Opencart’s MVC will make a connection to your database, gather results and display those results on a web page. Let’s pretend a customer is going to login to his/her account. At the login page they will enter their email and password. When they press LOGIN there is quite a bit that goes on, most of which no one knows about.

CONNECTING TO DB WITH PHP

In opencart you will find the login template at catalog/view/theme/default/template/account/login.tpl. Once in that file towards the bottom you will see the following JavaScript code:

<script type="text/javascript"><!--
$('#login input').keydown(function(e) {
	if (e.keyCode == 13) {
		$('#login').submit();
	}
});
//--></script>

The login, once pressed, through the work of JavaScript the form is sent via HTTP REQUEST to the location of $action:

<form action="<?php echo $action; ?>" method="post" enctype="multipart/form-data">
<input type="submit" value="<?php echo $button_login; ?>" />

The location of $action is index.php?route=account/login which targets login.php in the control. Now let’s take a look at the function login() . Open catalog/controller/account/login.php and locate the following code:

protected function validate() {
    	if (!$this->customer->login($this->request->post['email'], $this->request->post['password'])) {
      		$this->error['warning'] = $this->language->get('error_login');
    	}

		$customer_info = $this->model_account_customer->getCustomerByEmail($this->request->post['email']);

    	if ($customer_info && !$customer_info['approved']) {
      		$this->error['warning'] = $this->language->get('error_approved');
    	}		

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

And now find this line which recognizes the HTTP FORM REQUEST and calls the validate() function:

if (($this->request->server['REQUEST_METHOD'] == 'POST') && $this->validate()) {
if (!$this->customer->login($this->request->post['email'], $this->request->post['password'])) {

The line if (!$this->customer->login($this->request->post['email'], $this->request->post['password'])) { will validate the users email and password by calling the login() function located in /system/library/customer.php. Go to that file and find the following function:

public function login($email, $password, $override = false) {
		if ($override) {
			$customer_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer where LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "' AND status = '1'");
		} else {
			$customer_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "' AND (password = SHA1(CONCAT(salt, SHA1(CONCAT(salt, SHA1('" . $this->db->escape($password) . "'))))) OR password = '" . $this->db->escape(md5($password)) . "') AND status = '1' AND approved = '1'");
		}

		if ($customer_query->num_rows) {
			$this->session->data['customer_id'] = $customer_query->row['customer_id'];	

			if ($customer_query->row['cart'] && is_string($customer_query->row['cart'])) {
				$cart = unserialize($customer_query->row['cart']);

				foreach ($cart as $key => $value) {
					if (!array_key_exists($key, $this->session->data['cart'])) {
						$this->session->data['cart'][$key] = $value;
					} else {
						$this->session->data['cart'][$key] += $value;
					}
				}			
			}

			if ($customer_query->row['wishlist'] && is_string($customer_query->row['wishlist'])) {
				if (!isset($this->session->data['wishlist'])) {
					$this->session->data['wishlist'] = array();
				}

				$wishlist = unserialize($customer_query->row['wishlist']);

				foreach ($wishlist as $product_id) {
					if (!in_array($product_id, $this->session->data['wishlist'])) {
						$this->session->data['wishlist'][] = $product_id;
					}
				}			
			}

			$this->customer_id = $customer_query->row['customer_id'];
			$this->firstname = $customer_query->row['firstname'];
			$this->lastname = $customer_query->row['lastname'];
			$this->email = $customer_query->row['email'];
			$this->telephone = $customer_query->row['telephone'];
			$this->fax = $customer_query->row['fax'];
			$this->newsletter = $customer_query->row['newsletter'];
			$this->customer_group_id = $customer_query->row['customer_group_id'];
			$this->address_id = $customer_query->row['address_id'];

			$this->db->query("UPDATE " . DB_PREFIX . "customer SET ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "' WHERE customer_id = '" . (int)$this->customer_id . "'");

	  		return true;
    	} else {
      		return false;
    	}
  	}

Here is where we see an actual connection to the Database. Let’s go over the following line of code in detail:

$customer_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "' AND (password = SHA1(CONCAT(salt, SHA1(CONCAT(salt, SHA1('" . $this->db->escape($password) . "'))))) OR password = '" . $this->db->escape(md5($password)) . "') AND status = '1' AND approved = '1'");

This is a SELECT QUERY on the table `customer`.  The query is Selecting * – wildcard with the following WHERE CLAUSE as the conditional: WHERE email = “bla” AND CLAUSE password = “bla”.  The function can only return one of two values, TRUE or FALSE. It returns TRUE if it finds the posted email along with the posted password, and false if it does not. This is relevant to understand the call to login() by checking IF FALSE This line, if you remember from earlier: if (!$this->customer->login($this->request->post['email'], $this->request->post['password'])) {

which is saying IF FALSE, then throw an error at the login screen. Notice that in the customer.php we see the Database connection as $this->db->query(); This is because it is already set up in it’s own class and that is how you call it.  Precisely, the db class in opencart is located here: system/database/mysqli.php

I hope this makes it clearer in how PHP can be used to connect to a database with the Opencart framework.

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 *


8 + 6 =

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>