Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How do you edit the Bestsellers in the Database?


mthierfelder

Recommended Posts

:?:

I've been pouring through my db trying to find the table that contains the data that the Bestsellers list is pulling from. CANT FIND IT ANYWHERE!

 

Problem is: during testing I made a whole bunch of test purchases, like a whole truck load of this item and a boat load of that item. Now that the site is live and selling things, the bestsellers list is forever more innaccurate because of this.

 

Any help appreciated.

 

Mike

Installed Modules:

Dynamenu, InfoBox Admin, Master Products v.1.2, Header Tags Controller, Multiple Products Manager, Quick Edit in Admin, Secure Admin, Ultimate SEO URL's, EZ Secure Order, Easy Populate v.2.76d MS2, AuthorizeNet_AIM, ChangeFinal Breadcrumb Title, FedEx Labels, Fedex Direct 2.06, How Did you Hear 1.5, Login a la Amazon, UPS XML 1.2.4, USPS Labels, USPS Methods API MS2

Link to comment
Share on other sites

Disclaimer: The following is an educated guess! :D

 

The code to query the database for the bestseller's list is in /include/boxes/best_sellers.php. The following block of code is the query itself:

 

<?php

 if ($cPath) {

   $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name, p.products_ordered from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (c.categories_id = '" . $current_category_id . "' OR c.parent_id = '" . $current_category_id . "') order by p.products_ordered DESC, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);

 } else {

   $best_sellers_query = tep_db_query("select p.products_id, pd.products_name, p.products_ordered from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' order by p.products_ordered DESC, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);

 }



 if (tep_db_num_rows($best_sellers_query) >= MIN_DISPLAY_BESTSELLERS) {

?>

 

There appears to be a products_ordered field in the products table which keeps track of how many of the respective product is being ordered. I would imagine that using your favorite sql tool to set the products_ordered field to appropriate counts, that would solve your problem.

 

Again, this is purely speculation...I haven't tried it and if you do this you do it at your own risk.

 

Hope this helps. If someone who actually knows what they're doing wants to comment or correct me, by all means feel free. 8)

 

-Jim[/code]

Link to comment
Share on other sites

If only it were that simple. I am aware of the call to the products_ordered field, I looked in the database for it but couldn't find it. I'll look again though, because if it calls it and my shopping cart works it must be there somewhere, no?

Installed Modules:

Dynamenu, InfoBox Admin, Master Products v.1.2, Header Tags Controller, Multiple Products Manager, Quick Edit in Admin, Secure Admin, Ultimate SEO URL's, EZ Secure Order, Easy Populate v.2.76d MS2, AuthorizeNet_AIM, ChangeFinal Breadcrumb Title, FedEx Labels, Fedex Direct 2.06, How Did you Hear 1.5, Login a la Amazon, UPS XML 1.2.4, USPS Labels, USPS Methods API MS2

Link to comment
Share on other sites

If only it were that simple.  I am aware of the call to the products_ordered field, I looked in the database for it but couldn't find it.  I'll look again though, because if it calls it and my shopping cart works it must be there somewhere, no?

 

Products_ordered is a field in the products table. The p. is an assigned identifier. So, if you look at the query above, TABLE_PRODUCTS . " p. " . means table_products fields will be identified by p.<fieldname>

 

Hope that helps...

 

Aodhan

Link to comment
Share on other sites

Disclaimer: The following is an educated guess!

 

Only temp this if you know how to find your way through phpmyadmin other wise ignore this reply.... :D

 

this is what I did to start over again...

 

I am using phpmyadmin version 2.3.2

 

Inside the table Products you'll see products_ordered

 

edit the products_ordered ( the products that you ordered will have a number of how many times it was ordered ) edit it back to 0

 

REPEAT: Only temp this if you know how to find your way through phpmyadmin other wise ignore this reply.... :D

Link to comment
Share on other sites

Thanks for your help. You were correct in your educated but accurate guess, the products_ordered field in the products table held the critical data. Problem solved.

 

:)

Installed Modules:

Dynamenu, InfoBox Admin, Master Products v.1.2, Header Tags Controller, Multiple Products Manager, Quick Edit in Admin, Secure Admin, Ultimate SEO URL's, EZ Secure Order, Easy Populate v.2.76d MS2, AuthorizeNet_AIM, ChangeFinal Breadcrumb Title, FedEx Labels, Fedex Direct 2.06, How Did you Hear 1.5, Login a la Amazon, UPS XML 1.2.4, USPS Labels, USPS Methods API MS2

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...