This is what I used many times while developed magento sites. I have made Feature products , Special Producst, Hot products etc by the custom attribute. To do so I am going to make some Special Product which will show on frontend, for this I will create a custom attribute Special Product whose code name will be special. First Log in to admin panel then Catalog->Attributes -> Manage Attributes . Now click on Add new Attribute, I n the Attribute Code field write special and from Catalog Input Type for Store Owner select Yes/No from the drop down, Choose NO from the Default Value .Now click on Manage Label / Options then in the Admin field write Special Product then click on Save Attribute button to save the attribute.
Now go to Catalog-> Attributes -> Manage Attribute Sets . Click on your Attribute set (e.g : - I choose Default attribute set) then add the Special attribute from Unassigned Attributes to Groups by drag and drop. Now go to any products which have same attribute set in which the Special Attribute is assign, There you can see that Special Product Option has came with yes/no drop down. Now make some product Yes as Special product.
Now go to frontend and write the below code where you want to fetch the Special Product.
$storeId = Mage::app()->getStore()->getId();
$resource = Mage::getSingleton('core/resource');
$read = $resource->getConnection('catalog_read');
$categoryProductTable = $resource->getTableName('catalog/category_product');
$productEntityIntTable = (string)Mage::getConfig()->getTablePrefix() . 'catalog_product_entity_int';
$eavAttributeTable = $resource->getTableName('eav/attribute');
// Query database for special product
$select = $read->select()
->distinct()
->from(array('cp'=>$categoryProductTable),'cp.product_id')
->join(array('pei'=>$productEntityIntTable), 'pei.entity_id=cp.product_id', array())
->joinNatural(array('ea'=>$eavAttributeTable))
->where('pei.value=1')
->where('ea.attribute_code="special"');// Write your attribute code instead of Special
$rows = $read->fetchAll($select);
// Save all product Id which have the Special Product Yes
foreach($rows as $row)
{
$product[] =$row['product_id'];
}
Inside the $product variable you have all product Id which are Special Product . Now write the Below code to get Details of the Each product
<ul>
<?php foreach($product as $key=>$val)
{
$productobject = Mage::getModel('catalog/product');
$productmodel = $productobject->load($val);
echo "<li>";
echo "<h2>".$productmodel->getName()."</h2>";
echo "<a href=".$productmodel->getProductUrl()."><img src=".$productmodel->getImageUrl()."title=".$productmodel->getName()."/></a>";
echo "<p>".$productmodel->getDescription()."</p>";
echo "<p>".$productmodel->getFinalPrice()."</p>";
?>
<input type="button" value="<?php echo $this->__('Add to Cart') ?>" onclick="setLocation('<?php echo Mage::getUrl('checkout/cart/add', array('product'=>$val,'qty'=>1)) ?>')" />
<?php
echo "</li>";
?>
</ul>
You can see all the Special Product has come to your frontend. By this way you can make Feature Product or Hot Product . Enjoy..............