Post by hiddenking » Sun Jan 23, 2022 2:07 am

I do a page this page showing all category also below product which belong to this category
But it is showing if a product does not belong to a category also its status is off
So i need to add product status control with mysql query
can you help edit this category.php file

this page https://market.miuiturkiye.net/allcategory

Code: Select all

<?php
class ControllerExtensionModuleFskcatproducts extends Controller {
    public function index() {
         $this->document->setTitle("Tüm Kategoriler");
        $category_list = array(117,240,166,241,242); // Buraya hangi kategoriler gelecekse onların adlarını yazın. 
         
        $products = $this->db->query("SELECT category_id,product_id FROM ".DB_PREFIX."product_to_category WHERE category_id IN(".implode(',',$category_list).") GROUP BY product_id")->rows;
         
        $data['products'] = array();
         
        $this->load->model('tool/image');
        $this->load->model('catalog/product');
        foreach($products as $product){
            $category_name = $this->db->query("SELECT name FROM ".DB_PREFIX."category_description WHERE category_id='".$product['category_id']."' AND language_id='".(int)$this->config->get('config_language_id')."'")->row['name'];
            $result = $this->model_catalog_product->getProduct($product['product_id']);
            if ($result['image']) {
                $image = $this->model_tool_image->resize($result['image'], $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height'));
            } else {
                $image = $this->model_tool_image->resize('placeholder.png', $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height'));
            }
 
            if ($this->customer->isLogged() || !$this->config->get('config_customer_price')) {
                $price = $this->currency->format($this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
            } else {
                $price = false;
            }
 
            if ((float)$result['special']) {
                $special = $this->currency->format($this->tax->calculate($result['special'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
            } else {
                $special = false;
            }
 
            if ($this->config->get('config_tax')) {
                $tax = $this->currency->format((float)$result['special'] ? $result['special'] : $result['price'], $this->session->data['currency']);
            } else {
                $tax = false;
            }
 
            if ($this->config->get('config_review_status')) {
                $rating = (int)$result['rating'];
            } else {
                $rating = false;
            }
			
            /*    if($result['quantity'] == 0){
                    $stock_control = "no-stock";
                }else{
                    $stock_control = false;
                }
 */
            $data['products'][$category_name][] = array(
                'product_id'  => $result['product_id'],
				'stock_control' => $stock_control,
                'thumb'       => $image,
                'name'        => $result['name'],
                'description' => utf8_substr(trim(strip_tags(html_entity_decode($result['description'], ENT_QUOTES, 'UTF-8'))), 0, $this->config->get('theme_' . $this->config->get('config_theme') . '_product_description_length')) . '..',
                'price'       => $price,
                'special'     => $special,
                'tax'         => $tax,
                'minimum'     => $result['minimum'] > 0 ? $result['minimum'] : 1,
                'rating'      => $result['rating'],
                'href'        => $this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url)
            ); 
        } 
         
        $data['footer'] = $this->load->controller('common/footer');
        $data['header'] = $this->load->controller('common/header'); 
         
        $this->response->setOutput($this->load->view('extension/module/fskcatproduct', $data));
    }
}

market.miuiturkiye.net


User avatar
Newbie

Posts

Joined
Tue Apr 24, 2018 2:15 am

Post by by mona » Sun Jan 23, 2022 1:54 pm

This is an optimal construction.
You are now performing a separate category name query for each category-product combination you find when you only have 5 categories listed.

Code: Select all

get all product category combinations
foreach of those {
	get the category name (executed x number of category-product combinations)
	get the product details
	add to the array
}
Better to construct it like:

Code: Select all

foreach ($category_list as $category_id) {
	get the category name (executed x number of categories in the list)
	get the products with status 1 for the category by joining product_to_category and product tables
	foreach products {
		get the details
	}
	add  to the array
}
or, as you need to join tables anyway, you could also get the category name in the first query by joining those tables:

Code: Select all

$category_products = $this->db->query(
"SELECT cd.name as category_name, ptc.product_id 
FROM ".DB_PREFIX."product_to_category ptc LEFT JOIN ".DB_PREFIX."product p ON (ptc.product_id = p.product_id) LEFT JOIN ".DB_PREFIX."category_description cd ON (ptc.category_id = cd.category_id) 
WHERE 
ptc.category_id IN(".implode(',',$category_list).")  AND
p.status = 1 AND
cd.language_id = '".(int)$this->config->get('config_language_id')."'
GROUP BY ptc.product_id")->rows;
(will give you rows of category name in the language and product_id for those products with status 1)

then you can construct it like:

Code: Select all

foreach ($category_products as $category_product) {
	get product details using $category_product['product_id']
	$data['products'][$category_product['category_name']][] = array(with all the product details)
}

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by hiddenking » Sun Jan 23, 2022 4:20 pm

@by mona

İ tried but i take error?

Can you edit my category.php file and add here please if you have time

market.miuiturkiye.net


User avatar
Newbie

Posts

Joined
Tue Apr 24, 2018 2:15 am

Post by by mona » Mon Jan 24, 2022 2:26 am

UNTESTED

Code: Select all

<?php
class ControllerExtensionModuleFskcatproducts extends Controller {
	public function index() {
		$this->load->model('tool/image');
		$this->load->model('catalog/product');
		
		$this->document->setTitle("Tüm Kategoriler"); // ***** define this in your language file
		
		$category_list = array(117,240,166,241,242); // Buraya hangi kategoriler gelecekse onların adlarını yazın. 
		
		$category_products = $this->db->query(
		"SELECT cd.name as category_name, ptc.product_id 
		FROM 
		".DB_PREFIX."product_to_category ptc LEFT JOIN 
		".DB_PREFIX."product p ON (ptc.product_id = p.product_id) 
		LEFT JOIN ".DB_PREFIX."category_description cd ON (ptc.category_id = cd.category_id) 
		WHERE 
		ptc.category_id IN(".implode(',',$category_list).")  AND
		p.status = 1 AND
		cd.language_id = '".(int)$this->config->get('config_language_id')."'
		GROUP BY ptc.product_id")->rows;
		
		$data['products'] = array();

		foreach($category_products as $category_product){
			$result = $this->model_catalog_product->getProduct($category_product['product_id']);
			$image = ($result['image'] ? $this->model_tool_image->resize($result['image'], $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height')) : $this->model_tool_image->resize('placeholder.png', $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height')));
			$price = ($this->customer->isLogged() || !$this->config->get('config_customer_price') ? $this->currency->format($this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']) : false);
			$special = ((float)$result['special'] ? $this->currency->format($this->tax->calculate($result['special'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']) : false);
			$tax = ($this->config->get('config_tax') ? $this->currency->format((float)$result['special'] ? $result['special'] : $result['price'], $this->session->data['currency']) : false);
			$rating = ($this->config->get('config_review_status') ? (int)$result['rating'] : false);

			$data['products'][$category_product['category_name']][] = array(
				'product_id'		=> $result['product_id'],
				'stock_control'		=> $stock_control,
				'thumb'			=> $image,
				'name'			=> $result['name'],
				'description'		=> utf8_substr(trim(strip_tags(html_entity_decode($result['description'], ENT_QUOTES, 'UTF-8'))), 0, $this->config->get('theme_' . $this->config->get('config_theme') . '_product_description_length')) . '..',
				'price'			=> $price,
				'special'		=> $special,
				'tax'			=> $tax,
				'minimum'		=> $result['minimum'] > 0 ? $result['minimum'] : 1,
				'rating'			=> $result['rating'],
				'href'			=> $this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url)
				); 
		} 
		$data['footer'] = $this->load->controller('common/footer');
		$data['header'] = $this->load->controller('common/header'); 
		$this->response->setOutput($this->load->view('extension/module/fskcatproduct', $data));
	}
}
PS. no use in putting the path in the product url as you do not have a path on this page.

Code: Select all

$this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url)

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am

Post by hiddenking » Thu Jan 27, 2022 11:50 pm

by mona wrote:
Mon Jan 24, 2022 2:26 am
UNTESTED

Code: Select all

<?php
class ControllerExtensionModuleFskcatproducts extends Controller {
	public function index() {
		$this->load->model('tool/image');
		$this->load->model('catalog/product');
		
		$this->document->setTitle("Tüm Kategoriler"); // ***** define this in your language file
		
		$category_list = array(117,240,166,241,242); // Buraya hangi kategoriler gelecekse onların adlarını yazın. 
		
		$category_products = $this->db->query(
		"SELECT cd.name as category_name, ptc.product_id 
		FROM 
		".DB_PREFIX."product_to_category ptc LEFT JOIN 
		".DB_PREFIX."product p ON (ptc.product_id = p.product_id) 
		LEFT JOIN ".DB_PREFIX."category_description cd ON (ptc.category_id = cd.category_id) 
		WHERE 
		ptc.category_id IN(".implode(',',$category_list).")  AND
		p.status = 1 AND
		cd.language_id = '".(int)$this->config->get('config_language_id')."'
		GROUP BY ptc.product_id")->rows;
		
		$data['products'] = array();

		foreach($category_products as $category_product){
			$result = $this->model_catalog_product->getProduct($category_product['product_id']);
			$image = ($result['image'] ? $this->model_tool_image->resize($result['image'], $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height')) : $this->model_tool_image->resize('placeholder.png', $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_width'), $this->config->get('theme_' . $this->config->get('config_theme') . '_image_product_height')));
			$price = ($this->customer->isLogged() || !$this->config->get('config_customer_price') ? $this->currency->format($this->tax->calculate($result['price'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']) : false);
			$special = ((float)$result['special'] ? $this->currency->format($this->tax->calculate($result['special'], $result['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']) : false);
			$tax = ($this->config->get('config_tax') ? $this->currency->format((float)$result['special'] ? $result['special'] : $result['price'], $this->session->data['currency']) : false);
			$rating = ($this->config->get('config_review_status') ? (int)$result['rating'] : false);

			$data['products'][$category_product['category_name']][] = array(
				'product_id'		=> $result['product_id'],
				'stock_control'		=> $stock_control,
				'thumb'			=> $image,
				'name'			=> $result['name'],
				'description'		=> utf8_substr(trim(strip_tags(html_entity_decode($result['description'], ENT_QUOTES, 'UTF-8'))), 0, $this->config->get('theme_' . $this->config->get('config_theme') . '_product_description_length')) . '..',
				'price'			=> $price,
				'special'		=> $special,
				'tax'			=> $tax,
				'minimum'		=> $result['minimum'] > 0 ? $result['minimum'] : 1,
				'rating'			=> $result['rating'],
				'href'			=> $this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url)
				); 
		} 
		$data['footer'] = $this->load->controller('common/footer');
		$data['header'] = $this->load->controller('common/header'); 
		$this->response->setOutput($this->load->view('extension/module/fskcatproduct', $data));
	}
}
PS. no use in putting the path in the product url as you do not have a path on this page.

Code: Select all

$this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url)
Thank you very perfect now,

Last question how can i sort this category

When i use this $category_list = array(117,240,166,241,242); can't see in a specific order

market.miuiturkiye.net


User avatar
Newbie

Posts

Joined
Tue Apr 24, 2018 2:15 am
Who is online

Users browsing this forum: sidclel and 85 guests