Post by chongshengdz » Fri Jul 15, 2016 5:19 pm

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by imdevlper18 » Fri Jul 15, 2016 6:10 pm

If you are on version 2.1.0.2 or lowere then

Go to admin - system - settings - store - edit - Default Items Per Page (Catalog)
Here you can set the number.


If you are on version 2.2.0.0 or higher then


Go to admin - extension - themes - store - edit - Default Items Per Page (Catalog)
Here you can set the number.

Opencart Extensions | Professional opencart support | Support Ticket | support@cartbinder.com


User avatar
Active Member

Posts

Joined
Sun May 11, 2014 2:04 pm


Post by chongshengdz » Sat Jul 16, 2016 11:17 am

imdevlper18 wrote:If you are on version 2.1.0.2 or lowere then

Go to admin - system - settings - store - edit - Default Items Per Page (Catalog)
Here you can set the number.


If you are on version 2.2.0.0 or higher then


Go to admin - extension - themes - store - edit - Default Items Per Page (Catalog)
Here you can set the number.

please read my question carefully,your answer is not to my question.
It is not about per page,it is about select limit number of products from database.

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by imdevlper18 » Sat Jul 16, 2016 12:42 pm

I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down
As i understand, but i said above based on this line.

See when query is made all products are not called from database, but only part of it are called. So slowing things down is never going to happen. If you are showing limited number of products per page to your customers.

Opencart Extensions | Professional opencart support | Support Ticket | support@cartbinder.com


User avatar
Active Member

Posts

Joined
Sun May 11, 2014 2:04 pm


Post by chongshengdz » Sat Jul 16, 2016 3:41 pm

imdevlper18 wrote:
I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down
As i understand, but i said above based on this line.

See when query is made all products are not called from database, but only part of it are called. So slowing things down is never going to happen. If you are showing limited number of products per page to your customers.
do you know how to do it?

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by imdevlper18 » Sun Jul 17, 2016 12:14 am

If you show limited products per page, then page will load fast.
You can follow instructions mentioned in first post.

Opencart Extensions | Professional opencart support | Support Ticket | support@cartbinder.com


User avatar
Active Member

Posts

Joined
Sun May 11, 2014 2:04 pm


Post by chongshengdz » Sun Jul 17, 2016 1:20 am

imdevlper18 wrote:If you show limited products per page, then page will load fast.
You can follow instructions mentioned in first post.
please don't answer to my question again,your English is too bad to understand my question.
if it is as what you said,i will not post a question here.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by chongshengdz » Sun Jul 17, 2016 1:22 am

it is not about products per page.
it is about not to select all the products from database per category.

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by chongshengdz » Mon Jul 18, 2016 6:39 pm

any one gives a solution?

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by pprmkr » Mon Jul 18, 2016 10:23 pm

Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];

User avatar
Active Member

Posts

Joined
Sat Jan 08, 2011 11:05 pm
Location - Netherlands

Post by labeshops » Tue Jul 19, 2016 1:59 am

chongshengdz wrote:it is not about products per page.
it is about not to select all the products from database per category.
Just wondering why you would limit your sales this way?

If you don't want to show more than 100 products per category, maybe you should rethink your categories creating more of them or perhaps divide your products by different niches across multiple stores using multistores. I mean why add more products if you aren't going to let a customer see and buy them??

Running Opencart v2.2 with multi-stores from http://www.labeshops.com which has links to all my stores.

Image


User avatar
Expert Member

Posts

Joined
Thu Aug 04, 2011 4:41 am
Location - Florida, USA

Post by chongshengdz » Wed Jul 20, 2016 2:14 am

pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];


please read my question twice before answering my question.
it is really not about setting products per page.
if it is so,i won't post a question here,it is really simple.
please read my question again below.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by chongshengdz » Wed Jul 20, 2016 2:18 am

labeshops wrote:
chongshengdz wrote:it is not about products per page.
it is about not to select all the products from database per category.
Just wondering why you would limit your sales this way?

If you don't want to show more than 100 products per category, maybe you should rethink your categories creating more of them or perhaps divide your products by different niches across multiple stores using multistores. I mean why add more products if you aren't going to let a customer see and buy them??


because if you have 50000 products in a category,the load time will be very slow.
so need to limit it to speed up the category load time and only show part of the products.

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by straightlight » Wed Jul 20, 2016 2:18 am

chongshengdz wrote:
pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];


please read my question twice before answering my question.
it is really not about setting products per page.
if it is so,i won't post a question here,it is really simple.
please read my question again below.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?
What you might be referring to is the Pagination range that splits the product results into separate pages based on the over-limited quantity of products . The codes you are looking for by each categories would be located in the catalog/controller/product/category.php file where:

Code: Select all

$pagination = new Pagination();
			$pagination->total = $product_total;
			$pagination->page = $page;
			$pagination->limit = $limit;
			$pagination->url = $this->url->link('product/category', 'path=' . $this->request->get['path'] . $url . '&page={page}');

			$data['pagination'] = $pagination->render();

			$data['results'] = sprintf($this->language->get('text_pagination'), ($product_total) ? (($page - 1) * $limit) + 1 : 0, ((($page - 1) * $limit) > ($product_total - $limit)) ? $product_total : ((($page - 1) * $limit) + $limit), $product_total, ceil($product_total / $limit));

			// http://googlewebmastercentral.blogspot.com/2011/09/pagination-with-relnext-and-relprev.html
			if ($page == 1) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'canonical');
			} elseif ($page == 2) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'prev');
			} else {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page - 1), true), 'prev');
			}

			if ($limit && ceil($product_total / $limit) > $page) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page + 1), true), 'next');
			}
is already handled and re-worked into the Opencart v2.2.0.0 release with the previous and next page sessions.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.

F. Rules:

- viewtopic.php?f=176&t=200480
- viewtopic.php?f=176&t=200804


Regards,
Straightlight


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by chongshengdz » Wed Jul 20, 2016 3:13 am

straightlight wrote:
chongshengdz wrote:
pprmkr wrote:Solution is already given twice!

Perhaps you learn to read and check the queries in product-model.

Products / page set limit in query. There are no more products loaded than the value set in limit ( limit is product / page )

Code: Select all

$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];


please read my question twice before answering my question.
it is really not about setting products per page.
if it is so,i won't post a question here,it is really simple.
please read my question again below.

For example, I have 1000 products in category N, but I want to limit the amount of product that are shown to 100 and I want other products to be hidden. (I mean there is no way that someone would browse 1000 products therefore large amount of records only slowing things down)

Where is the query located?
What you might be referring to is the Pagination range that splits the product results into separate pages based on the over-limited quantity of products . The codes you are looking for by each categories would be located in the catalog/controller/product/category.php file where:

Code: Select all

$pagination = new Pagination();
			$pagination->total = $product_total;
			$pagination->page = $page;
			$pagination->limit = $limit;
			$pagination->url = $this->url->link('product/category', 'path=' . $this->request->get['path'] . $url . '&page={page}');

			$data['pagination'] = $pagination->render();

			$data['results'] = sprintf($this->language->get('text_pagination'), ($product_total) ? (($page - 1) * $limit) + 1 : 0, ((($page - 1) * $limit) > ($product_total - $limit)) ? $product_total : ((($page - 1) * $limit) + $limit), $product_total, ceil($product_total / $limit));

			// http://googlewebmastercentral.blogspot.com/2011/09/pagination-with-relnext-and-relprev.html
			if ($page == 1) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'canonical');
			} elseif ($page == 2) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'], true), 'prev');
			} else {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page - 1), true), 'prev');
			}

			if ($limit && ceil($product_total / $limit) > $page) {
			    $this->document->addLink($this->url->link('product/category', 'path=' . $category_info['category_id'] . '&page='. ($page + 1), true), 'next');
			}
is already handled and re-worked into the Opencart v2.2.0.0 release with the previous and next page sessions.

You guy is trying to misunderstand my question.
$pagination->total = $product_total = 1000; # is what i need,but the category page still loads slow.
i need a better solution.
i need something like this select * from table order by product_id desc limit 1000;

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by straightlight » Wed Jul 20, 2016 5:37 am

You can look for an extension on the site that would match your needs. If you are unable to find any results, you can always post a new topic in the commercial support forum to get this done as a custom job.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.

F. Rules:

- viewtopic.php?f=176&t=200480
- viewtopic.php?f=176&t=200804


Regards,
Straightlight


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by chongshengdz » Wed Jul 20, 2016 11:01 am

straightlight wrote:You can look for an extension on the site that would match your needs. If you are unable to find any results, you can always post a new topic in the commercial support forum to get this done as a custom job.
please get out of here since you are not willing to help.
if i can find an extension,i will not post a question here.

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by pprmkr » Wed Jul 20, 2016 2:20 pm

chongshengdz wrote: $pagination->total = $product_total = 1000; # is what i need,but the category page still loads slow.
1 quick query counts total records in table with given filter ( category, searchstring or tag ):

Code: Select all

$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";
Returns 1 value
chongshengdz wrote: i need a better solution.
i need something like this select * from table order by product_id desc limit 1000;
Next query gets restricted amount of records with the same filter but with start and limit when needed.
For example:
- table holds 175000 records
- 5000 products in category
- limit / page is set to 15 ( default )
- start = 0 because page = 1

Code: Select all

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS tot ....
....
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
Returns 15 records

Queries can be found in catalog/model/catalog/product.php.

Try it yourself in phpMyAdmin:
- select database
- select tab SQL
- enter "SELECT COUNT(DISTINCT product_id) AS total FROM oc_product"
- study result
- Then enter SQL "SELECT * FROM oc_product LIMIT 0,15"
- study result

What did you see?

There are may tips out there regarding speeding up database. From MyISAM to InnoDB, adding indexes and more ...

User avatar
Active Member

Posts

Joined
Sat Jan 08, 2011 11:05 pm
Location - Netherlands

Post by chongshengdz » Wed Jul 20, 2016 4:12 pm

pprmkr wrote:
chongshengdz wrote: $pagination->total = $product_total = 1000; # is what i need,but the category page still loads slow.
1 quick query counts total records in table with given filter ( category, searchstring or tag ):

Code: Select all

$sql = "SELECT COUNT(DISTINCT p.product_id) AS total";
Returns 1 value
chongshengdz wrote: i need a better solution.
i need something like this select * from table order by product_id desc limit 1000;
Next query gets restricted amount of records with the same filter but with start and limit when needed.
For example:
- table holds 175000 records
- 5000 products in category
- limit / page is set to 15 ( default )
- start = 0 because page = 1

Code: Select all

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS tot ....
....
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
Returns 15 records

Queries can be found in catalog/model/catalog/product.php.

Try it yourself in phpMyAdmin:
- select database
- select tab SQL
- enter "SELECT COUNT(DISTINCT product_id) AS total FROM oc_product"
- study result
- Then enter SQL "SELECT * FROM oc_product LIMIT 0,15"
- study result

What did you see?

There are may tips out there regarding speeding up database. From MyISAM to InnoDB, adding indexes and more ...
so how to do it?
where to change the code?

https://www.transistorfet.com/
https://www.transistormosfet.com/


New member

Posts

Joined
Sat Apr 12, 2014 10:18 pm


Post by pprmkr » Wed Jul 20, 2016 4:45 pm

:crazy: :drunk:
I am very sorry if my post is not clear.
Perhaps you should read it again!

- I told you where to find the queries.
- I asked you to do 2 little tests and post your findings.
- I added comment about speeding up database.

User avatar
Active Member

Posts

Joined
Sat Jan 08, 2011 11:05 pm
Location - Netherlands
Who is online

Users browsing this forum: masterross and 56 guests