Sorting Product List pages by order popularity

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

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:
> 
> ```html
$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.

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

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.)

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! :D

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. :)

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

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

http://pastebin.com/KAh97z60

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. :)

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

Btw, if I updated it, what will happen to modified files outside the template folder, is there any section I should refer to on this? :huh:

mc12345678:

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?

Hi, how would I go about checking this? Thanks!

Cozmoz:

Btw, if I updated it, what will happen to modified files outside the template folder, is there any section I should refer to on this? :huh:

Ot sure I follow this question. Updating the one file associated with the SID issue described is a core file issue, not a template driven issue.

To answer the other question posed above, the change is/would be made in the includes/functions/html_output.php file. If you search for defined('SID') the line returned includes a zen_not_null(SID) command and the next line has $sid = SID. In both cases replace SID with constant('SID') (don't modify the defined statement).

But this is something that has been identified as only necessary for PHP 5.4.x, so not sure that it will resolve your issue. :/

Looking into the specific erro esponse (should have done the first time around) the ero is related to a timeout of the mysql server (query taking too long) online suggestions indicate that indexing of some of the return results would help reduce the query time. Or increasing a timeout variable on the host may help. I haven't seen exactly which timeout variable/where it is in the setup in oder to help. :/

Hi, McNumbers, I've had a chance to look again. The timeout was caused by me restarting mySQL, the server had a high level of traffic at the time and this problem has caused the server to crash in the past. I have tried running this script again, it never finished loading, the page just hangs. I'm at a loss at what might be causing this.

I've tried looking everywhere on the site where I can find $listing_sql but cannot find any conflicts and like I said previously, the code runs just fine on a fresh install. Short of moving modifications onto a fresh install I'm unsure as to how to go about further troubleshooting this.

When you say that. The page just hangs, is that to mean that basically get a white screen? A white. Screen generally will generate an error message in the logs folder (zc >= 1.5.1)

mc12345678:

When you say that. The page just hangs, is that to mean that basically get a white screen? A white. Screen generally will generate an error message in the logs folder (zc >= 1.5.1)

Yes, it's a white screen that doesn't seem to finish loading. It never generated an error but I'll try to give it a chance to time out. Thanks.

Will get back asap.