Magento PHP SQL查询，用于从特定目录(包括子目录)中提取产品

Question

我目前正处于紧要关头，需要对我的网站上的一些现有代码进行调整。有一个PHP SQL查询需要修改以从特定类别中提取产品。然后将该列表输入到HTML产品转盘中。无论如何，目前它正在拉动所有带有图像的产品。

<?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)) {
                ?>

代码的注释行就是我想要做的，但是没有成功。我在黑暗中拍摄。我不是Magento或PHP专家。仅供参考，前端显示我想要的类别的类别ID为16。按名称拉取它会很好，但为了简单/快速起见，我可以使用ID。

谢谢你的帮助。

(更新8-27)整个HTML代码块：

<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>

Answer 1

$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 
}