Hi guys,
Not sure if this is the right place for this but I would like to sort product listings by order popularity.
I have written the following code that works in when placed in the header:
HTML Code:
*global $db;
$sql = "
select p.products_id, COUNT(opc.products_id)
from "
. TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_ORDERS_PRODUCTS . " opc on pd.products_id = opc.products_id, "
. TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
where p.products_status = 1
GROUP BY p.products_id ORDER BY COUNT(opc.products_id) DESC
";
$result = $db->Execute($sql);
if ($result->RecordCount() > 0) {
echo '<p>Selected Products<br />';
while (!$result->EOF) {
echo 'Product ID = ' . $result->fields['products_id'] .'<br />';
$result->MoveNext();
}
echo '</p>';
} else {
echo 'Sorry, no record found for product number ' . $theProductId;
}
But when I place the following into index_filters.php it shows an error, unfortunately when I place the query into PHP My Admin, it runs just fine:
HTML Code:
$listing_sql = "select " . $select_column_list . " p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
from "
. TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_ORDERS_PRODUCTS . " opc on pd.products_id = opc.products_id, "
. TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p2c.products_id = s.products_id
where p.products_status = 1
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p2c.categories_id = '" . (int)$current_category_id . "'" .
$alpha_sort
. " GROUP BY p.products_id ORDER BY COUNT(opc.products_id) DESC";
Any help or advice would be appreciated, I'm really stumped on this one!
Thanks,
Costa
Bookmarks