GBP £

For Sales & Advice Call Us on 0121 501 1111

Search

Menu

Settings

  • AUD A$
  • CAD CA$
  • USD $

    • SLAMM

    If you are searching for Slamm cylinder clamps, scooter bars or scooters, we have all of this and more. Here at SkateHut, we are thrilled to stock an impressive range of Slamm products for extreme sports enthusiasts that require scooters and accessories that boast performance and style. Our super cool selection of Slamm scooters boast great spec and high quality graphics that are guaranteed to please. When it comes to stunt scooters, we have products to suit every rider. Browse our Slamm scooters, shop online with us today and take advantage of our stress free service.

    Loading...
    SELECT `e`.*, `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` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` 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 = 1 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 WHERE ((stock_status_index.stock_status = 1) AND (e.entity_id IN (240676, 101477, 101579, 124570, 124572, 124575, 124576, 124577, 124579, 124581, 136511, 153034, 159995, 159996))) AND (e.created_in <= '1652691602') AND (e.updated_in > '1652691602') ORDER BY FIELD(e.entity_id,240676,101477,101579,124570,124572,124575,124576,124577,124579,124581,136511,153034,159995,159996)

    14 Items