CODEWITHSUNDEEP
phpmysqlmagento
GisMofx
GisMofx

Reputation: 1014

Magento PHP SQL Query to pull out products from a specific catergory(including children)

I am currently in a pinch and need a tweak to some existing code on my site. There is a PHP SQL query that needs to be modified to pull products from a specific category. This list then fed into an HTML product carousel. Anyway, currently it's pulling in all products with an image.

<?php
 $query = mysql_query("SELECT * FROM `catalog_product_flat_1` WHERE `small_image` != 'no_selection'");
 //$query = mysql_query("SELECT * FROM `catalog_product_flat_1` WHERE `category_ID` = 16");
 while($fins = mysql_fetch_array($query)) {
                ?>

The commented line of code is what I was trying to do, but had no luck. I'm shooting in the dark here. I'm no Magento or PHP expert by any standards. FYI, The front end shows the category ID being 16 for the category that I want. It would be nice to pull it by name, but I'm ok with using ID for simplicity/speed sake.

Thanks for any assistance.

(UPDATE 8-27) Entire HTML Code Block:

<div class="whiteGradientLarge span-10">
    <div class="content">
        <h2>Latest Products</h2>
        <div class="span-10">
            <a href="#" class="prev left"><img src="assets/arrowPrev.png" /></a>
            <ul class="productSlider push-1">

                <?php
                //$query = mysql_query("SELECT * FROM `catalog_product_flat_1` WHERE `small_image` != 'no_selection'");
                //while($products = mysql_fetch_array($query)) {
                $category = new Mage_Catalog_Model_Category();
                $category->load('16'); //your category id here, 16 $catid
                $prodCollection = $category->getProductCollection();
                foreach ($prodCollection as $product) {
                //get all the product information here... //}
                    ?>
                    <li class="roundabout-moveable-item">
                    <h3><a href="http://www.mywebsite.com/store/products/<?php echo $product['url_path']; ?>"><?php echo $product['name']; ?></a></h3> 
                    <img src="https://www.mywebsite.com/store/media/catalog/product<?php echo $product['small_image']; ?>" />
                    <span class="productTitle span-3"><?php  ?></span>
                    </li>
                <?php 
                } ?>
            </ul>
            <a href="#" class="next right"><img src="assets/arrowNext.png" /></a>
        </div>
    </div>

</div>

Upvotes: 0

Views: 1433

Answers (1)

Kalpesh
Kalpesh

Reputation: 5685

$category = new Mage_Catalog_Model_Category();
$category->load($catid); //your category id here, 16
$prodCollection = $category->getProductCollection();
foreach ($prodCollection as $product) {
    //get all the product information here...

    ?>
    <li class="roundabout-moveable-item">
         <h3><a href="http://www.mywebsite.com/store/products/<?php echo $product->getUrlPath(); ?>"><?php echo $product->getName(); ?></a></h3> 
         <img src="https://www.mywebsite.com/store/media/catalog/product<?php echo $product->getSmallImage(); ?>" />
         <span class="productTitle span-3"><?php  ?></span>
    </li>
 <?php 
}

Upvotes: 1

Related Questions