Sorting Product List pages by order popularity

[Cozmoz]

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:

*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:

$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

[mc12345678]

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:

*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:

$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

Not sure I fully follow, but in the second example, towards the end is $alpha_sort.... That to me makes me think that there is already an ORDER BY command captured in that variable and the next thing is to group by and then order by again.. There are a few things that you could do to try to resolve this. First of all, when it "doesn't" work, there should be an error log generated. Seeing that I don't know what version of ZC you are using, in ZC 1.5.1 and above, the folder is the logs folder. In ZC Version 1.5.0 and below the error log folder is the cache folder.

lat9 has developed a mydebug tool that may help with identifying factors of the SQL; however, seeing that you know where you are inserting it, then basically you need to know what the SQL is that is actually being "run" at that point. So may want to find a way to export the SQL itself before it is sent to be processed and then see if that statement makes sense.

[Cozmoz]

Hi, Sorry for taling so long to get back to you, I had been made to work on other tasks, this is what's shown in the logs:

[14-Nov-2014 12:04:02 UTC] PHP Fatal error:  1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by p.products_sort_order, pd.products_name' at line 7 :: select p.products_image, pd.products_name, p.products_quantity,  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 products_description pd left join orders_products opc on pd.products_id = opc.products_id, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 = '1'
         and p2c.categories_id = '10' GROUP BY p.products_id ORDER BY COUNT(opc.products_id) DESC order by p.products_sort_order, pd.products_name ==> (as called by) /var/www/example1/includes/templates/template_default/templates/tpl_index_product_list.php on line 39 <== in /var/www/example1/includes/classes/db/mysql/query_factory.php on line 155

[mc12345678]

So looks like the current result is different than the previous SQL statement ($alpha_sort being moved to the end). The $alpha_sort variable should not include order by and should begin with a comma, that would resolve the current error message (have two order by clauses in series and not in any way encapsulated with/for another query. Therefore should only have one order by statement with the items on whichto sort in the desired order of sorting. Following the above, the sort order would be by the count first then by the information captured in $sort_order.)

[Cozmoz]

Thanks, the problem wasn't in the $alpha_sorter it was cased by $listing_sql found below the code block in the original post. Those log files are very helpful! Much like yourself!

[mc12345678]

Well, thank you. Though the information captured in those logs is a result of lat9's hard work and support of the ZC community. She identified the support code to give us the extra information to track down such issues. :)

In the end, glad it got sorted. :)

[Cozmoz]

Hi, Sorry to bother you again, I am having a problem I can't pin point when trying to integrate it to a live version of the the site, please find the log file below:

[14-Nov-2014 17:00:45 UTC] PHP Fatal error:  1317:Query execution was interrupted :: select  p.products_id, COUNT(opc.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 orders_status_history osh left join orders_total ot on osh.orders_id = ot.orders_id, products_description pd left join orders_products opc on pd.products_id = opc.products_id, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 = '1'
         and p2c.categories_id = '146' GROUP BY p.products_id  in /var/www/vhosts/justminiatures.net/httpdocs/includes/classes/db/mysql/query_factory.php on line 120
[14-Nov-2014 17:00:45 UTC] PHP Fatal error:  : :: select count(*) as total
            from sessions
            where sesskey = 'q5dh1itph6h0s75bpitniifkv1' in /var/www/vhosts/justminiatures.net/httpdocs/includes/classes/db/mysql/query_factory.php on line 120

The live version which I'm trying to copy the code to is using version 1.5.1 and the test platform which it is working on runs 1.5.3.

Here's a pastebin of the code I am using:

http://pastebin.com/DrkqVKhV

Regards,

Costa

[Cozmoz]

I've updated my comments in the file a little here:

http://pastebin.com/KAh97z60

[mc12345678]

Php versions on the two systems, has the "live" version been patched to work with/better with PHP 5.4 to remove the memory leak problem associated with the constant SID?

There isn't concern about which version of those/that queries is running is there? Lat9 has suggested code that would help pinpoint where in the code the query was at rather than where the query factory executes queries. Try looking for the plugin mydebug. I can't remember the rest of the filename. :)

[Cozmoz]

It's running PHP version 5.3.15 on the live server, I will look into that. Thanks very much.