In Bengaluru, Chennai, Delhi-NCT, Hyderabad, Mumbai and Pune
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable`, IFNULL(top_brand_option_value_t2.option_id, top_brand_option_value_t1.option_id) AS `top_brand`, IFNULL(top_brand_option_value_t2.value, top_brand_option_value_t1.value) AS `top_brand_value`, IFNULL(top_brand_option_t1.sort_order, 0) AS `top_brand_order` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store4` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=4 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=4155
INNER JOIN `catalog_product_index_price_store` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1'
LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 4 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `temp_d7d20d9013c1331a7e4bcd9c5a00210a81e66e27` AS `search_result` ON `e`.`entity_id` = `search_result`.`entity_id`
LEFT JOIN `catalog_product_entity_int` AS `top_brand_t1` ON e.row_id=top_brand_t1.row_id AND top_brand_t1.attribute_id='522' AND top_brand_t1.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `top_brand_t2` ON e.row_id=top_brand_t2.row_id AND top_brand_t2.attribute_id='522' AND top_brand_t2.store_id='4'
LEFT JOIN `eav_attribute_option_value` AS `top_brand_option_value_t1` ON top_brand_option_value_t1.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) AND top_brand_option_value_t1.store_id=0
LEFT JOIN `eav_attribute_option_value` AS `top_brand_option_value_t2` ON top_brand_option_value_t2.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) AND top_brand_option_value_t2.store_id=4
LEFT JOIN `eav_attribute_option` AS `top_brand_option_t1` ON top_brand_option_t1.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) WHERE ((price_index.store_id = 4) AND (price_index.store_id = 4) AND (price_index.store_id = 4) AND (stock_status_index.stock_status = 1) AND (price_index.store_id = 4)) AND (e.created_in <= 1) AND (e.updated_in > 1) ORDER BY `top_brand_order` ASC, `e`.`entity_id` DESC, `e`.`entity_id` ASC
LIMIT 20
*Offer valid only for cities other than Bangaluru, Chennai, Delhi-NCT, Mumbai, Pune, Hyderabad
OFFERS
CART VALUE DISCOUNT ON CAR TYRES
TyresNmore loves to give you more!
Buy Tyres, Batteries or Alloys and avail discounts as below
Coupon Codes
Cart Value Rs.
Discount on cart value
Max discount
CV15000
15000 to 29999
0.75%
Rs. 225
CV30000
30000 to 49999
1%
Rs. 500
CV50000
50000 & above
1.5%
Rs. 4000
Additional discount :- Get 1% discount by placing your order and paying online now - No code required
OFFERS
ONLINE PAYMENT DISCOUNT
Pay online and get an additional 1% Discount on your order.
*Subject to a successful online payment on www.tyresnmore.com online payment partners.
OFFERS
FREE WHEEL BALANCING
We love to give you More!
With every car tyre that you buy you get wheel balancing worth Rs.150 free.
You Save upto Rs.200 on purchase of 4 car tyres.
TYRE EXCHANGE FESTIVAL!!!
On Buying 4 or More Car Tyres :
Rs 200 off per order
On Buying 1/2/3 Car Tyres :
Rs 50 off per car tyre
All you need to do is to provide the same quantity of old tyres of the same size in exchange of the new tyres that you are buying when our team comes to fit the new tyres on your vehicle.
OFFERS
BONANZA OFFER ON 17 INCH+ TYRES!
Applicable on
Code
Offer
All tyres of wheel size 17 inch & above
TNM4000
2% off upto Rs. 4000 off per order
*T&C apply. Offer valid on 17 inch+ tyres of all brands. Use the above coupon code while placing order. This offer is on per order basis and not on per tyre basis. Tyresnmore Online Pvt. Ltd. reserves the right to withdraw or amend the above offers at any time prior to installation of tyres as per the orders placed by website users.
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable`, IFNULL(top_brand_option_value_t2.option_id, top_brand_option_value_t1.option_id) AS `top_brand`, IFNULL(top_brand_option_value_t2.value, top_brand_option_value_t1.value) AS `top_brand_value`, IFNULL(top_brand_option_t1.sort_order, 0) AS `top_brand_order` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store4` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=4 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=4155
INNER JOIN `catalog_product_index_price_store` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1'
LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 4 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `temp_d7d20d9013c1331a7e4bcd9c5a00210a81e66e27` AS `search_result` ON `e`.`entity_id` = `search_result`.`entity_id`
LEFT JOIN `catalog_product_entity_int` AS `top_brand_t1` ON e.row_id=top_brand_t1.row_id AND top_brand_t1.attribute_id='522' AND top_brand_t1.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `top_brand_t2` ON e.row_id=top_brand_t2.row_id AND top_brand_t2.attribute_id='522' AND top_brand_t2.store_id='4'
LEFT JOIN `eav_attribute_option_value` AS `top_brand_option_value_t1` ON top_brand_option_value_t1.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) AND top_brand_option_value_t1.store_id=0
LEFT JOIN `eav_attribute_option_value` AS `top_brand_option_value_t2` ON top_brand_option_value_t2.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) AND top_brand_option_value_t2.store_id=4
LEFT JOIN `eav_attribute_option` AS `top_brand_option_t1` ON top_brand_option_t1.option_id=IF(top_brand_t2.value_id > 0, top_brand_t2.value, top_brand_t1.value) WHERE ((price_index.store_id = 4) AND (price_index.store_id = 4) AND (price_index.store_id = 4) AND (stock_status_index.stock_status = 1) AND (price_index.store_id = 4)) AND (e.created_in <= 1) AND (e.updated_in > 1) ORDER BY `top_brand_order` ASC, `e`.`entity_id` DESC, `e`.`entity_id` ASC
LIMIT 20
