Show Magento Products by Attribute

You may remember Rob’s recent post about Smart Product Lists which detailed how to bypass Magento’s usual database interactions, and get straight in there yourself with your own MySQL code. Putting this to good use the other day I developed this script which does something so simple, yet can be quite difficult if you don’t know where to start. Trust me, I checked the Internet and there’s nothing out there which helps you do this.

What I needed to was print out all the products in the store which contained the words ‘Full Colour Printing’ in the attribute ‘pen_feature’. So, if anywhere in the string the words ‘Full Colour Printing’ appear, my script will tell the product name along with the product link to appear on the CMS page that I implemented it on. I’ll go through the code step by step:

1. First we need to inlcude the Magento Database Connection.

require_once 'app/Mage.php';
Mage::app('default');

2. Next we need to create an SQL statement and run a query which would effective grab every product ID contained in the store and store it in $result. In my case that was over 300 products. The ORDER BY bit isn’t necessary as it will probably put them in order anyway!

$write =  Mage::getSingleton('core/resource')->getConnection('core_write');
$result  = $write->query("SELECT product_id FROM catalog_category_product  ORDER BY product_id");

3. Now I’m going to write a while loop which will only execute the rest of the script while the $result can retrieve database data. Each row is now deliverers as a string, which means you’ll have a long list of numbers in one variable… not very helpful. So, I’m going to create a new variable, $prodid, which will become an array of all the product IDs by simply exploding the numbers at each blank space. Bingo – we now have a beautiful array of product IDs.

while ($row = $result->fetch() ) {

$prodid = explode(" ",  $row['product_id']);

4. Now for a bit of Magento code. I’m gonna start with a foreach statement which will convert $prodid into $id, then I’m going to load the Mage product data into each loop, each time the product being loaded by its ID.

foreach ($prodid as $id) {

$_product = new  Mage_Catalog_Model_Product();
$_product->load($id);

5. Now, I need to narrow down all these products so that it only brings out the ones with ‘Full Colour Printing’ within the pen_features attribute. So I load the attribute as $feature, and I create a variable for ‘Full Colour Printing’. Now I can use strpos with $feature as the haystack and $fullcol as the needle. With that check in place, one more if statement around the echoed script will check if the $fullcolcheck is true or not.

$fullcol =  "Full Colour Printing";
$feature =   $_product->getResource()->getAttribute('pen_features')->getFrontend()->getValue($_product);

$fullcolcheck = strpos($feature, $fullcol);

if ($fullcolcheck !==  false) { ?>

6. Now it’s simply a case of bringing out the product data, remember we’re still inside that foreach and while loop! You could bring out much more such as the price or image too.

<li><a href="<?php echo $_product->getProductUrl()  ?>"><?php echo $_product->getName();  ?></a></li>

That’s really all there is too it – not bad eh? You can probably simplify this script even more as it’s unlikely you’ll need to use the strpos if you’re checking attributes. I’ve put the entire script below for you to use as you wish. Keep checking back at the Magento Blog at Ecommerce Web Design for more of our Magento tutorials.

<ul><?php

require_once 'app/Mage.php';
Mage::app('default');

$write =   Mage::getSingleton('core/resource')->getConnection('core_write');
$result  = $write->query("SELECT product_id FROM  catalog_category_product  ORDER BY product_id");

while ($row = $result->fetch() ) {

$prodid = explode(" ", $row['product_id']);

foreach ($prodid as $id) {

$_product = new Mage_Catalog_Model_Product();
$_product->load($id);
$fullcol = "Full Colour Printing";
$feature = $_product->getResource()->getAttribute('pen_features')->getFrontend()->getValue($_product);

$fullcolcheck = strpos($feature, $fullcol);

if ($fullcolcheck !== false) { ?>
<li><a href="<?php echo $_product->getProductUrl() ?>"><?php echo $_product->getName(); ?></a></li>
<?php } } } ?></ul>

Adam Moss Author Page

• Using Attribute Set ID
• Non Salable Products with Attribute Sets
• Get Magento Product Attribute Label