Sort category list

Archived from the Web Auction Discussion forum.

bobede — Wed Oct 31, 2007 10:39 am

Is there a way to sort the category list in the left sidebar by category name rather than category id?

Thanks

Bob

---

shannah — Thu Nov 01, 2007 8:26 am

In the conf/ApplicationDelegate.php file, you’ll find a method:

Code: Select all

function getCategoriesMenuOptions(){         $sql = "select p.product_id, pc.category_id, pc.category_name, count(*) as num from products p inner join product_categories pc on p.product_categories rlike concat('[[:<:]]',pc.category_id,'[[:>:]]') group by pc.category_id";         $res = mysql_query($sql, df_db());         $out = array();         while ( $row = mysql_fetch_assoc($res) ) $out[] = $row;         return $out;         }

This is where all the dirty work is done for this list. There are a couple things you can try:

1. Add an ‘order by’ clause so that the query becomes:

Code: Select all

select p.product_id, pc.category_id, pc.category_name, count(*) as num from products p inner join product_categories pc on p.product_categories rlike concat('[[:<:]]',pc.category_id,'[[:>:]]') order by pc.category_name  group by pc.category_id

or

1. Change the ‘group by’ clause to use the category_name column.

Code: Select all

select p.product_id, pc.category_id, pc.category_name, count(*) as num from products p inner join product_categories pc on p.product_categories rlike concat('[[:<:]]',pc.category_id,'[[:>:]]') group by pc.category_name

-Steve

---

bobede — Thu Nov 01, 2007 12:07 pm

Thanks Steve!

I was trying to use “order by” earlier, but I was looking in the wrong place. Not even close, as a matter of fact.

I tried the order by clause where you suggested, but it returned a mySql error.

Your second suggestion worked perfectly.

Bob