In your configurable product, there is an attribute with name Visibility. You need to set it to Catalog, Search. This would make your product visible in search. You may probably forgot to do it.
So go to Catalog > Manage Products.
Select the configurable product. Under General tab, set Visibility to Catalog, Search.
Make sure product is assigned with current store and website.(Go to websites and categories tabs to do this).
Clear all cache and
Reload the page and then search.
If you are using an older version of 1.9.x you may need to upgrade to the latest as there was some confirmed bug with sorting on configurable products on 1.9.2.1 up to 1.9.2.4 (rough guess)
Catalog fixes Configurable products are now sorted by attribute, not
by product ID.
There are lots of examples of how to fix the sorting issue like with work arounds if you are unable to upgrade:
Local override file:
app/code/local/Mage/Catalog/Model/Resource/Product/Type/Configurable/Attribute/Collection.php
<?php
/**
* Magento 1.9.1 has a problem Sorting Configurable Product Attribute Options and Dropdowns
* [solved] File Patched by Jonathon Byrd
* http://magentosupport.help/knowledgebase/solved-sort-configurable-product-attribute-options-and-dropdowns/
*
* Magento
*
* NOTICE OF LICENSE
*
* This source file is subject to the Open Software License (OSL 3.0)
* that is bundled with this package in the file LICENSE.txt.
* It is also available through the world-wide-web at this URL:
* http://opensource.org/licenses/osl-3.0.php
* If you did not receive a copy of the license and are unable to
* obtain it through the world-wide-web, please send an email
* to license@magentocommerce.com so we can send you a copy immediately.
*
* DISCLAIMER
*
* Do not edit or add to this file if you wish to upgrade Magento to newer
* versions in the future. If you wish to customize Magento for your
* needs please refer to http://www.magentocommerce.com for more information.
*
* @category Mage
* @package Mage_Catalog
* @copyright Copyright (c) 2014 Magento Inc. (http://www.magentocommerce.com)
* @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
*/
/**
* Catalog Configurable Product Attribute Collection
*
* @category Mage
* @package Mage_Catalog
* @author Magento Core Team <core@magentocommerce.com>
*/
class Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
/**
* Configurable attributes label table name
*
* @var string
*/
protected $_labelTable;
/**
* Configurable attributes price table name
*
* @var string
*/
protected $_priceTable;
/**
* Product instance
*
* @var Mage_Catalog_Model_Product
*/
protected $_product;
/**
* Initialize connection and define table names
*
*/
protected function _construct()
{
$this->_init('catalog/product_type_configurable_attribute');
$this->_labelTable = $this->getTable('catalog/product_super_attribute_label');
$this->_priceTable = $this->getTable('catalog/product_super_attribute_pricing');
}
/**
* Retrieve catalog helper
*
* @return Mage_Catalog_Helper_Data
*/
public function getHelper()
{
return Mage::helper('catalog');
}
/**
* Set Product filter (Configurable)
*
* @param Mage_Catalog_Model_Product $product
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
public function setProductFilter($product)
{
$this->_product = $product;
return $this->addFieldToFilter('product_id', $product->getId());
}
/**
* Set order collection by Position
*
* @param string $dir
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
public function orderByPosition($dir = self::SORT_ORDER_ASC)
{
$this->setOrder('position ', $dir);
return $this;
}
/**
* Retrieve Store Id
*
* @return int
*/
public function getStoreId()
{
return (int)$this->_product->getStoreId();
}
/**
* After load collection process
*
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
protected function _afterLoad()
{
parent::_afterLoad();
Varien_Profiler::start('TTT1:'.__METHOD__);
$this->_addProductAttributes();
Varien_Profiler::stop('TTT1:'.__METHOD__);
Varien_Profiler::start('TTT2:'.__METHOD__);
$this->_addAssociatedProductFilters();
Varien_Profiler::stop('TTT2:'.__METHOD__);
Varien_Profiler::start('TTT3:'.__METHOD__);
$this->_loadLabels();
Varien_Profiler::stop('TTT3:'.__METHOD__);
Varien_Profiler::start('TTT4:'.__METHOD__);
$this->_loadPrices();
Varien_Profiler::stop('TTT4:'.__METHOD__);
return $this;
}
/**
* Add product attributes to collection items
*
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
protected function _addProductAttributes()
{
foreach ($this->_items as $item) {
$productAttribute = $this->getProduct()->getTypeInstance(true)
->getAttributeById($item->getAttributeId(), $this->getProduct());
$item->setProductAttribute($productAttribute);
}
return $this;
}
/**
* Add Associated Product Filters (From Product Type Instance)
*
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
public function _addAssociatedProductFilters()
{
$this->getProduct()->getTypeInstance(true)
->getUsedProducts($this->getColumnValues('attribute_id'), $this->getProduct()); // Filter associated products
return $this;
}
/**
* Load attribute labels
*
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
protected function _loadLabels()
{
if ($this->count()) {
$useDefaultCheck = $this->getConnection()->getCheckSql(
'store.use_default IS NULL',
'def.use_default',
'store.use_default'
);
$labelCheck = $this->getConnection()->getCheckSql(
'store.value IS NULL',
'def.value',
'store.value'
);
$select = $this->getConnection()->select()
->from(array('def' => $this->_labelTable))
->joinLeft(
array('store' => $this->_labelTable),
$this->getConnection()->quoteInto('store.product_super_attribute_id = def.product_super_attribute_id AND store.store_id = ?', $this->getStoreId()),
array(
'use_default' => $useDefaultCheck,
'label' => $labelCheck
))
->where('def.product_super_attribute_id IN (?)', array_keys($this->_items))
->where('def.store_id = ?', 0);
$result = $this->getConnection()->fetchAll($select);
foreach ($result as $data) {
$this->getItemById($data['product_super_attribute_id'])->setLabel($data['label']);
$this->getItemById($data['product_super_attribute_id'])->setUseDefault($data['use_default']);
}
}
return $this;
}
/**
* Load attribute prices information
*
* @return Mage_Catalog_Model_Resource_Product_Type_Configurable_Attribute_Collection
*/
protected function _loadPrices()
{
if ($this->count()) {
$pricings = array(
0 => array()
);
if ($this->getHelper()->isPriceGlobal()) {
$websiteId = 0;
} else {
$websiteId = (int)Mage::app()->getStore($this->getStoreId())->getWebsiteId();
$pricing[$websiteId] = array();
}
$select = $this->getConnection()->select()
->from(array('price' => $this->_priceTable))
->where('price.product_super_attribute_id IN (?)', array_keys($this->_items));
if ($websiteId > 0) {
$select->where('price.website_id IN(?)', array(0, $websiteId));
} else {
$select->where('price.website_id = ?', 0);
}
$query = $this->getConnection()->query($select);
while ($row = $query->fetch()) {
$pricings[(int)$row['website_id']][] = $row;
}
$values = array();
foreach ($this->_items as $item) {
$productAttribute = $item->getProductAttribute();
if (!($productAttribute instanceof Mage_Eav_Model_Entity_Attribute_Abstract)) {
continue;
}
$options = $productAttribute->getFrontend()->getSelectOptions();
foreach ($options as $option) {
foreach ($this->getProduct()->getTypeInstance(true)->getUsedProducts(null, $this->getProduct()) as $associatedProduct) {
if (!empty($option['value'])
&& $option['value'] == $associatedProduct->getData(
$productAttribute->getAttributeCode())) {
// If option available in associated product
if (!isset($values[$item->getId() . ':' . $option['value']])) {
// If option not added, we will add it.
$values[$item->getId() . ':' . $option['value']] = array(
'product_super_attribute_id' => $item->getId(),
'value_index' => $option['value'],
'label' => $option['label'],
'default_label' => $option['label'],
'store_label' => $option['label'],
'is_percent' => 0,
'pricing_value' => null,
'use_default_value' => true
);
}
}
}
}
}
foreach ($pricings[0] as $pricing) {
// Addding pricing to options
$valueKey = $pricing['product_super_attribute_id'] . ':' . $pricing['value_index'];
if (isset($values[$valueKey])) {
$values[$valueKey]['pricing_value'] = $pricing['pricing_value'];
$values[$valueKey]['is_percent'] = $pricing['is_percent'];
$values[$valueKey]['value_id'] = $pricing['value_id'];
$values[$valueKey]['use_default_value'] = true;
}
}
if ($websiteId && isset($pricings[$websiteId])) {
foreach ($pricings[$websiteId] as $pricing) {
$valueKey = $pricing['product_super_attribute_id'] . ':' . $pricing['value_index'];
if (isset($values[$valueKey])) {
$values[$valueKey]['pricing_value'] = $pricing['pricing_value'];
$values[$valueKey]['is_percent'] = $pricing['is_percent'];
$values[$valueKey]['value_id'] = $pricing['value_id'];
$values[$valueKey]['use_default_value'] = false;
}
}
}
/**
* Mage 1.9+ fix for configurable attribute options not sorting to position
* @author Harshit <support@cubixws.co.uk>
*/
$sortOrder = 1;
foreach ($this->_items as $item) {
$productAttribute = $item->getProductAttribute();
if (!($productAttribute instanceof Mage_Eav_Model_Entity_Attribute_Abstract)) {
continue;
}
$options = $productAttribute->getFrontend()->getSelectOptions();
foreach ($options as $option) {
if (!$option['value']) continue;
if (isset($values[$item->getId() . ':' . $option['value']])) {
$values[$item->getId() . ':' . $option['value']]['order'] = $sortOrder++;
}
}
}
usort($values, function($a, $b) {
return $a['order'] - $b['order'];
});
foreach ($values as $data) {
$this->getItemById($data['product_super_attribute_id'])->addPrice($data);
}
}
return $this;
}
/**
* Retrive product instance
*
* @return Mage_Catalog_Model_Product
*/
public function getProduct()
{
return $this->_product;
}
}
Sauce:
However with that said, if you are running the latest Simple Configurable Product (SCP) may be an option you need.
Related:
Best Answer
If you want to do it for all attributes of all configurable products, then:
If you want to do it for particular product or particular attribute you have to
JOINwithcatalog_product_super_attributetable usingproduct_super_attribute_idfield of both tabels.