SELECT TP.*, TB.brand_name, (SELECT image_name FROM tbl_product_images WHERE product_id = TP.product_id ORDER BY set_default DESC LIMIT 1 ) as default_image, IFNULL((TP.stock - (SELECT IFNULL(SUM(quantity), 0) FROM tbl_cart WHERE product_id = TP.product_id AND erase_time > NOW() ) ), 0) AS available_stock, ( SELECT IFNULL(ROUND(AVG(rating),1), 0) FROM tbl_product_rating WHERE product_id = TP.product_id ) as rating , CASE WHEN IFNULL((TP.stock - (SELECT IFNULL(SUM(quantity), 0) FROM tbl_cart WHERE product_id = TP.product_id AND erase_time > NOW() ) ), 0) = 0 THEN 0 ELSE 1 END as stock_sort FROM tbl_product AS TP LEFT JOIN tbl_category AS TC ON TC.category_id = TP.category_id LEFT JOIN tbl_subcategory AS TSC ON TSC.subcategory_id = TP.subcategory_id LEFT JOIN tbl_brand AS TB ON TB.brand_id = TP.brand_id WHERE TP.`status` = 'ACTIVE' AND TC.status = 'ACTIVE' AND TB.status = 'ACTIVE' AND CONCAT( IFNULL(TP.product_name, ''), ' ', IFNULL(TP.product_code, ''), ' ', IFNULL(TP.product_model, ''), ' ', IFNULL(TP.product_overview, ''), ' ', IFNULL(TP.product_specification, ''), ' ', IFNULL(TP.product_tags, ''), ' ', IFNULL(TC.category_name, '') , ' ', IFNULL(TSC.subcategory_name, '') , ' ', IFNULL(TB.brand_name,'') ) LIKE :keyword ORDER BY stock_sort DESC , TP.product_name ASC
BROWSE CATEGORIES
BROWSE BRANDS

Search results

Filter By

Price Range

Home Alpha 7r IV
Sort by
View by

Login

Please fill your login details to rate.

E-Mail:


Password:

Forgot Password?



No Product Found