php - zen cart SQL - Select products and order by price -

php - zen cart SQL - Select products and order by price -

solved

see reply below.

background

anyone familiar zen cart know comes finish worlds pointless product sort alternative aka alpha drop downwards sorter.

essentially allow select products letter/number start with. well, starting letter of item useful criteria when shopping i'm trying create more useful product sorter sort on date added, cost , product name. , filter based on attributes category.

problem

so have managed coerce sorter own query, working on except price.

here sql produced current set cost drop down:

select distinct 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) specials_new_products_price ,if(s.status =1, s.specials_new_products_price, p.products_price) final_price ,p.products_sort_order ,p.product_is_call ,p.product_is_always_free_shipping ,p.products_qty_box_status products p left bring together specials s on p.products_id = s.products_id left bring together products_description pd on p.products_id = pd.products_id bring together products_to_categories p2c on p.products_id = p2c.products_id p.products_status = 1 , pd.language_id = '1' , p2c.categories_id = '1' grouping p.products_id order final_price asc

as can see trying sort based on final cost alias either normal cost or special cost (if set). works fine through phpmyadmin. unfortunately isn't working through zen cart, php error:

php fatal error: 1054:unknown column 'final_price' in 'order clause' :: select p.products_id, p.products_price_sorter, p.master_categories_id, p.manufacturers_id products p left bring together specials s on p.products_id = s.products_id left bring together products_description pd on p.products_id = pd.products_id bring together products_to_categories p2c on p.products_id = p2c.products_id p.products_status = 1\r\n , pd.language_id = '1'\r\n , p2c.categories_id = '1' grouping p.products_id order final_price asc in /var/www/includes/classes/db/mysql/query_factory.php on line 101

so looked @ line 101 , refers error handling method in database abstraction class. have ideas going wrong here , how might go solving it?

'final_price' not field in database; it's part of each line item entry in shopping cart object. sort base of operations cost (which final cost products not priced attributes , don't have additional priced attributes) using database.

php sql database zen-cart