Tuesday, July 31, 2012

Get distinct value, show multiple row values in one column


If there is a table called userList

userid       listid
----------      -------------
1               2
2               2
1               2
1               1
2               1
2 3
2 1

Result I expected was:
userid       listid
----------      -------------
1               1,2,2
2               1,1,2,3

SELECT userid, GROUP_CONCAT(DISTINCT listid ORDER BY listid ASC SEPARATOR ',') FROM mahesh GROUP BY userid;


Table Used :

CREATE TABLE IF NOT EXISTS `mahesh` (
  `userid` int(11) NOT NULL,
  `listid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `mahesh` (`userid`, `listid`) VALUES
(1, 2),
(2, 2),
(1, 2),
(1, 1),
(2, 1),
(2, 3),
(2, 1);

SQL objects


SQL objects are

1) schemas,

2) data dictionaries, 

3) journals,

4) catalogs, 

5) tables, 

6) aliases, 

7) views, 

8) indexes, 

9) constraints, 

10) triggers, 

11) sequences, 

12) stored procedures, 

14) user-defined functions, 

15) user-defined types, 

16) and SQL packages. SQL creates and maintains these objects as system objects. 

A brief description of these objects follows:

Get Attribute Name And Value Magento


In magento you can create as many custom attributes for your products as you want.Suppose you want to add or display brand color for every product on home, category or product page. So this is very easy to do with custom attributes.
If we were to create a new custom attribute called “brand_color” and the attribute was of a “Text Field” type, we could do something like the following at a product level to obtain its value.
1
echo $_product->getBrandColor(); ?>
Get attribute collection
1
$attribute = $_product->getResource()->getAttribute('my_attribute'); ?>
Get attribute type
1
$attribute = $_product->getResource()->getAttribute('my_attribute')->getAttributeType(); ?>
Get attribute Label
1
$attribute = $_product->getResource()->getAttribute('my_attribute')->getFrontendLabel(); ?>
Attribute is visible or not
1
$attribute = $_product->getResource()->getAttribute('my_attribute')->getIsVisible(); ?>
Attribute is required
1
$attribute = $_product->getResource()->getAttribute('my_attribute')->getIsRequired(); ?>
Get attribute value
1
$attributeValue = Mage::getModel('catalog/product')->load($_product->getId())->getMyAttribute();?>
Get the multi-select attribute values
1
2
3
4
5
$attributeId = Mage::getResourceModel('eav/entity_attribute')->getIdByCode('catalog_product','attribute_code_here');
$attribute = Mage::getModel('catalog/resource_eav_attribute')->load($attributeId);
$attributeOptions = $attribute ->getSource()->getAllOptions();
?>
Get specific attribute value
1
2
3
4
5
$attributeValue = Mage::getModel('catalog/product')
                            ->load($_product->getId())
                            ->getAttributeText('my_attribute');
?>