X-Cart forums

[jillsybte]

Originally Posted by mltriebe

Sorry, I should have been more clear. I changed the 30 to 60 and 90 but the results never changed. I also have changed XC_TIME to time() with no luck as well.

I have also just noticed it is not getting the items correctly either. I know I sold 50 pieces of 3mm carbon in one order and 24 pieces of 1mm carbon but 1mm is listed first and 3mm 4th on the list. and some items don't show at all that should be.

It is getting late and tomorrow is another day.

Thanks, Mike

I wish I could be more help, but I really don't know what else could be different between your version and 4.4.3. I did check and I don't think XC_TIME was used in older versions. So you would want to change that to time(). Some servers require you to define a time zone in order to use PHP time functions. So that's something else you should look into.

As far as order of products, the final order is alphabetical, not in order of number ordered. The func_search_products function that is used to actually retrieve the bestsellers does not recognize the orders or order_details tables. The $salesprod_data query does retrieve items based on their number ordered, date of order, etc., so the resulting array of product IDs will contain the correctly sorted IDs. However, the final bestsellers are simply products whose IDs that are in that array, but they are sorted alphabetically by product name. As far as products not showing up, make sure they truly were ordered within the timeframe you've specified and you have designated a high enough number of bestsellers to select.

Otherwise, I would say there must be other differences between the current existing X-Cart functions and queries and those in your version.

[mltriebe]

I did change XC_Time to time() and it didn't seem to help but it may have. I was looking for the items to be sorted by number sold, not alphabetical.

So if I understand correctly now I need to select more items for the best sellers to get them to show on the category pages as well?

Please go to my site and let me know if this is working correctly.

http://www.bmkdesigns.net

I have 5 items on the home page which is what I want. On the category pages the only ones that show are the same 5 items in their respective category. The 4 carbon items in carbonfiber and the servo in servos.

I was under the assumption that 5 items would show in all categories if 5 items had been sold in the selected time frame. So I may be chasing my own tail trying to fix what is not broke.

Thanks, Mike

[jillsybte]

Originally Posted by mltriebe

I did change XC_Time to time() and it didn't seem to help but it may have. I was looking for the items to be sorted by number sold, not alphabetical.

So if I understand correctly now I need to select more items for the best sellers to get them to show on the category pages as well?

Please go to my site and let me know if this is working correctly.

http://www.bmkdesigns.net

I have 5 items on the home page which is what I want. On the category pages the only ones that show are the same 5 items in their respective category. The 4 carbon items in carbonfiber and the servo in servos.

I was under the assumption that 5 items would show in all categories if 5 items had been sold in the selected time frame. So I may be chasing my own tail trying to fix what is not broke.

Thanks, Mike

Your assumption is correct, but I have had it working that way for 4.4.3. Not knowing what your actual sales are, I can't tell if the module is working correctly on your site. I'm thinking that the change in the categories table between older versions and the 4.4.x branch is causing problems for you.

The number of bestsellers chosen by you under admin will be the LIMIT in the MySQL query used to retrieve product IDs from the order data tables, which means it will select up to that limit. So, yes, you would need at least that many items sold in each category during your chosen timeframe in order for that number of bestsellers to display on every category page. If you have only sold 2 items from a certain category during your timeframe, then only 2 items will appear for that category.

This mod does not take product views into account as the original Bestsellers mod does. It is based purely on products actually sold during a chosen timeframe.

[isaels@i-artsupplies.co.u]

I get this error...Nit sure how to solve it though

INVALID SQL: 1054 : Unknown column 'lpos' in 'field list'
SQL QUERY FAILURE:SELECT categoryid, lpos FROM xcart_categories USE INDEX (PRIMARY) WHERE categoryid = '3692'

So i removed lpos and then I got

INVALID SQL: 1054 : Unknown column 'rpos' in 'field list'
SQL QUERY FAILURE:SELECT categoryid, rpos FROM xcart_categories USE INDEX (PRIMARY) WHERE categoryid = '3692'

Not sure how to solve that one

[jillsybte]

Originally Posted by isaels@i-artsupplies.co.u

I get this error...Nit sure how to solve it though

INVALID SQL: 1054 : Unknown column 'lpos' in 'field list'
SQL QUERY FAILURE:SELECT categoryid, lpos FROM xcart_categories USE INDEX (PRIMARY) WHERE categoryid = '3692'

So i removed lpos and then I got

INVALID SQL: 1054 : Unknown column 'rpos' in 'field list'
SQL QUERY FAILURE:SELECT categoryid, rpos FROM xcart_categories USE INDEX (PRIMARY) WHERE categoryid = '3692'

Not sure how to solve that one

I assume you are using 4.3.2 as your signature indicates. lpos and rpos didn't exist in the products table for that version. I'm not sure exactly how to make this mod work in earlier versions, and I would have no way of testing in those versions. See my reply above (http://forum.x-cart.com/showpost.php?p=326357&postcount=7) for information. I don't think it's perfect, but it's a start.

[isaels@i-artsupplies.co.u]

Originally Posted by mltriebe

Seems to work very well for version 4.3.2 as well.

Thanks, Mike

Yes I thought that the version Im using may have something to do with it but just thought I would try it as Mike seemed to have got it to work for 4.3.2

I have a look at that link though

[mltriebe]

I have it working OK, here is my bestsellers.php file.

Mike

<?php
if (!defined('XCART_SESSION_START')) { header("Location: ../../"); die("Access denied"); }
x_load('product');
if (!is_numeric($config["Bestsellers"]["number_of_bestsellers"]) || $config["Bestsellers"]["number_of_bestsellers"] < 0)
    $config["Bestsellers"]["number_of_bestsellers"] = 0;
$bs_prod_ids = array();
/**
 * Get products data for current category and store it into $products array
 */
$cat = isset($cat) ? intval($cat) : 0;
$search_query = '';
$bs_now = time();
$bs_sale_cutoff = $bs_now - (86400 * 30); //Plan to create and set $config['Bestsellers']['bestsellers_sales_days']
if ($cat) {
    $category_data = func_query_first("SELECT categoryid_path, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (PRIMARY) WHERE categoryid = '$cat'");
    if ($category_data)
        $result = func_query_hash("SELECT categoryid, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (pa) WHERE categoryid_path LIKE '$category_data[categoryid_path]' AND avail = 'Y'", "categoryid", false, true);
    else
        $result = '';
    $cat_ids = array();
    if (is_array($result) && !empty($result)) {
        $cat_ids = array_keys($result);
    } else {
        $cat_ids[] = $cat;
    }
    $search_query = " AND $sql_tbl[products_categories].categoryid IN ('".implode("','", $cat_ids)."')";
    unset($result);
}
/**
 * Get product sales data from order details and orders tables and store in $products array
 */
$salesprod_data = func_query_hash("SELECT $sql_tbl[order_details].productid, SUM($sql_tbl[order_details].amount) AS number FROM $sql_tbl[products] INNER JOIN ($sql_tbl[orders] INNER JOIN $sql_tbl[order_details] ON $sql_tbl[orders].orderid=$sql_tbl[order_details].orderid) ON $sql_tbl[products].productid=$sql_tbl[order_details].productid WHERE ((($sql_tbl[orders].status) IN ('C', 'P')) AND (($sql_tbl[orders].date)>= '" . $bs_sale_cutoff . "') AND (($sql_tbl[products].forsale)='Y')) GROUP BY $sql_tbl[order_details].productid ORDER BY number DESC, $sql_tbl[orders].date DESC, $sql_tbl[products].add_date DESC LIMIT " . $config['Bestsellers']['number_of_bestsellers']);
$salesprod_ids = array();
if (
    is_array($salesprod_data)
    && !empty($salesprod_data)
) {
    $salesprod_ids = array_keys($salesprod_data);
}
$search_query .= " AND $sql_tbl[products].productid IN ('" . implode("','", $salesprod_ids) . "')";
unset($salesprod_data);
/**
 * Search the bestsellers
 */
$bestsellers = func_search_products(
	$search_query,
	@$user_account["membershipid"],
	"$sql_tbl[products].product"
);
if (is_array($bestsellers) && !empty($bestsellers)) {
  foreach ($bestsellers as $k => $v) {
      if (!empty($active_modules['Customer_Reviews']) && $config['Customer_Reviews']['customer_voting'] == 'Y') {
        $bestsellers[$k]['rating_data'] = func_get_product_rating($bestsellers[$k]['productid']);
      }
      if (count($bestsellers) >= 6) {  
        $_limit_width = $config['Appearance']['thumbnail_width'];
        $_limit_height = $config['Appearance']['thumbnail_height'];
      }
      $bs_prod_ids[] = $bestsellers[$k]['productid']; 
  }
}

$smarty->assign_by_ref('bestsellers', $bestsellers);
?>

[isaels@i-artsupplies.co.u]

Originally Posted by jillsybte

I assume you are using 4.3.2 as your signature indicates. lpos and rpos didn't exist in the products table for that version. I'm not sure exactly how to make this mod work in earlier versions, and I would have no way of testing in those versions. See my reply above (http://forum.x-cart.com/showpost.php?p=326357&postcount=7) for information. I don't think it's perfect, but it's a start.

Thanks for the thats stopped the errors, so hopefully this will work now for me

thanks

[isaels@i-artsupplies.co.u]

Originally Posted by mltriebe

I have it working OK, here is my bestsellers.php file.

Mike

<?php
if (!defined('XCART_SESSION_START')) { header("Location: ../../"); die("Access denied"); }
x_load('product');
if (!is_numeric($config["Bestsellers"]["number_of_bestsellers"]) || $config["Bestsellers"]["number_of_bestsellers"] < 0)
    $config["Bestsellers"]["number_of_bestsellers"] = 0;
$bs_prod_ids = array();
/**
 * Get products data for current category and store it into $products array
 */
$cat = isset($cat) ? intval($cat) : 0;
$search_query = '';
$bs_now = time();
$bs_sale_cutoff = $bs_now - (86400 * 30); //Plan to create and set $config['Bestsellers']['bestsellers_sales_days']
if ($cat) {
    $category_data = func_query_first("SELECT categoryid_path, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (PRIMARY) WHERE categoryid = '$cat'");
    if ($category_data)
        $result = func_query_hash("SELECT categoryid, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (pa) WHERE categoryid_path LIKE '$category_data[categoryid_path]' AND avail = 'Y'", "categoryid", false, true);
    else
        $result = '';
    $cat_ids = array();
    if (is_array($result) && !empty($result)) {
        $cat_ids = array_keys($result);
    } else {
        $cat_ids[] = $cat;
    }
    $search_query = " AND $sql_tbl[products_categories].categoryid IN ('".implode("','", $cat_ids)."')";
    unset($result);
}
/**
 * Get product sales data from order details and orders tables and store in $products array
 */
$salesprod_data = func_query_hash("SELECT $sql_tbl[order_details].productid, SUM($sql_tbl[order_details].amount) AS number FROM $sql_tbl[products] INNER JOIN ($sql_tbl[orders] INNER JOIN $sql_tbl[order_details] ON $sql_tbl[orders].orderid=$sql_tbl[order_details].orderid) ON $sql_tbl[products].productid=$sql_tbl[order_details].productid WHERE ((($sql_tbl[orders].status) IN ('C', 'P')) AND (($sql_tbl[orders].date)>= '" . $bs_sale_cutoff . "') AND (($sql_tbl[products].forsale)='Y')) GROUP BY $sql_tbl[order_details].productid ORDER BY number DESC, $sql_tbl[orders].date DESC, $sql_tbl[products].add_date DESC LIMIT " . $config['Bestsellers']['number_of_bestsellers']);
$salesprod_ids = array();
if (
    is_array($salesprod_data)
    && !empty($salesprod_data)
) {
    $salesprod_ids = array_keys($salesprod_data);
}
$search_query .= " AND $sql_tbl[products].productid IN ('" . implode("','", $salesprod_ids) . "')";
unset($salesprod_data);
/**
 * Search the bestsellers
 */
$bestsellers = func_search_products(
    $search_query,
    @$user_account["membershipid"],
    "$sql_tbl[products].product"
);
if (is_array($bestsellers) && !empty($bestsellers)) {
  foreach ($bestsellers as $k => $v) {
      if (!empty($active_modules['Customer_Reviews']) && $config['Customer_Reviews']['customer_voting'] == 'Y') {
        $bestsellers[$k]['rating_data'] = func_get_product_rating($bestsellers[$k]['productid']);
      }
      if (count($bestsellers) >= 6) {  
        $_limit_width = $config['Appearance']['thumbnail_width'];
        $_limit_height = $config['Appearance']['thumbnail_height'];
      }
      $bs_prod_ids[] = $bestsellers[$k]['productid']; 
  }
}

$smarty->assign_by_ref('bestsellers', $bestsellers);
?>

Working Now thanks!