Objectives of the blog
After reading this blog you will be able to fetch products from Opencart database on the basis of parameter which is passed from URL.
Requirement
We have to display latest products on the basis of passed parameter in the URL. Using this parameter we have to decide that whether we have to display special products or not.
Code Implementation
To implement this we need to make change in our controller and model. In controller we added condition to pass parameter and in model we will make changes in the query according to the passed parameter.
To handle condition we are using show_special parameter in the code.
Below is the code which we have to add in controller (catalog/controller/extension/module/latest.php):
/*Checking for show_special key in get parameter
* This key ($show_special) will be used to conditionally fetch data from the database.
* i.e. if $show_special = 1 then display products which special
* $show_special = 0 then display products which are not having special
* else
* display all products irrespective of special
*/
if (isset($this->request->get['show_special'])) {
//passing the value in variable if set in URL
$show_special = $this->request->get['show_special'];
} else {
//Otherwise empty string will be passed
$show_special = '';
}
//Creating a variable to be passed into model
$filter_data = array(
'start' => ($page - 1) * $limit,
'limit' => $limit,
'show_special' => $show_special
);
//Calling model function to fetch data from database according to the data passed in the filter_data array.
$results = $this->model_catalog_product->getProductLatest($filter_data);
In this code first we have checked that if ‘show_special’ parameter is set in the URL or not. If yes then set value of parameter in a variable $show_special and if no then set variable as blank.
Then we have created an array ($default_data) with the values on behalf of which we have to fetch products from database.
Then we have called model function getProductLatest() to fetch latest products according to the variable passed ($filter_data) in the function.
Here is the function which we have to add in model (catalog/model/catalog/product):
public function getProductLatest($data = array()) {
//Setting $join and $condition parameters to fetch data of special products
if($data['show_special'] == '1'){
$join = "LEFT JOIN " . DB_PREFIX . "product_special ps ON (ps.product_id = p.product_id) ";
$condition = "AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "'
AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW() OR ps.date_end = '0001-01-01')) ";
}
//Setting $join and $condition parameters to fetch data of products which are not specil
elseif($data['show_special'] == '0'){
$join = "LEFT JOIN " . DB_PREFIX . "product_special ps ON (ps.product_id = p.product_id) ";
$condition = "AND ps.product_id IS NULL";
}else{
//Setting $join and $condition parameters to fetch data of all products irrespective of special
$join= '';
$condition = '';
}
//Query to fetch data of products from database. Added $join and $condition parameter in the query to fetch data accordingly
$sql = "SELECT DISTINCT p.product_id
FROM " . DB_PREFIX . "product p
LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)
$join
WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
$condition
GROUP BY p.product_id ORDER BY p.product_id DESC";
//Setting limit in query if passed LIMIT from controller.
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'];
}
$product_data = array();
//Execute query
$query = $this->db->query($sql);
//Loop to set fetched data in the variable to pass in controller.
foreach ($query->rows as $result) {
$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
}
//Return products data to controller.
return $product_data;
}
In model we have set variables $join and $condition on the basis of following three conditions:
- If show_special is 1: In this case we have displayed only products those are special. As in opencart the data of special products is stored in oc_product_special table so we have added join of oc_product table with the oc_product_secial table to fetch data of special products only.
- If show_special is 0: In this case we have displayed products which are not set as special. So to find products which are not special we have to fetch products which are in special table but on in oc_product_special table. For this we have added LEFT join of oc_product table with oc_product_secial table and added condition “ps.product_id IS NULL”.
- If show_special is other than 0 and 1: In this case we have to fetch all products whether they are special or not. So for this we have directly fetched data from oc_product table.
After adding all three possible condition we have written query and executed it. Then we have set all fetched data in $product_data array and return the same to controller.
Note: here to fetch latest products we have just used ‘Order by product_id DESC’ in query. If you want to fetch data of products which were added in last few days then you can add one more condition in the WHERE clause i.e. date_added > ‘any date’.
Summary
After following the above procedure, you will be able to fetch latest special products on the basis of passed parameter for special products in the URL.
