Would like to convert a PHP snippet to mivascript

[lesliekirk]

I have this PHP snippet that was created to produce a list of products

$query = "SELECT s01_Products.code,  s01_Products.name, s01_Products.price, s01_CFM_ProdValues.value        
FROM s01_Products, s01_Categories,  s01_CategoryXProduct,  s01_CFM_ProdValues       
WHERE    s01_Products.name LIKE '%BRAKE%'      
 AND  
s01_Products.id = s01_CategoryXProduct.product_id AND   
s01_CFM_ProdValues.field_id = 1 AND    
s01_Products.code NOT LIKE '%\_%' AND     
s01_Products.id = s01_CFM_ProdValues.product_id AND      
s01_CategoryXProduct.cat_id=s01_Categories.id AND s01_Categories.parent_id = 38868     
AND s01_Products.active REGEXP '1'   
ORDER BY s01_Categories.parent_id, s01_Products.code" ;    
$result = mysql_query($query) ;
echo '<center/><table BORDER COLS=3 WIDTH="95%"/>';       
echo '<tr ALIGN=CENTER VALIGN=TOP/>';      
echo '<td/><b/>Item:</b/></td/>';
echo '<td WIDTH="20%"/><b/>Your Price:</b/></td/>';  
echo '<td WIDTH="10%"/>';  
echo '</td/>';
echo '</tr/>';
 while($row=mysql_fetch_array($result))
{       
   $code=trim($row["code"]);
   $name=$row["name"]; 
   $value=$row["value"];
   $price=$row["price"];
   echo "<tr ALIGN=CENTER VALIGN=TOP/>";      
   echo "<td/><b>";
   echo "<font color=\"#000099\"/>";
   //echo "<font size = +2/>";
   echo "$code - $name <br/>FITS: $value. </td/>";
   echo "<td/><b/> \$$price </font/></b/></td/>";
   echo "<td/> ";
   echo '<a href="http://www.domain.com/product/';
   echo "$code.html";
   echo '"><img src="http://www.domain.com/images/addcart.gif" width=80 height=30></a>';
 echo "</td/>";
   echo "</tr/>";
   };
echo "</table/>";
?>

I have a Category Alternate Page Template started using the line style layout for the products which includes custom fields

<table>
        <tr class="header-row">
            <td class="item-code">
                Code
            </td>

            <td class="item-price">
                Price
            </td>

            <td class="item-buttons">
                &nbsp;
            </td>
        </tr>

        <mvt:foreach iterator="product" array="category_listing:products">
        <mvt:if expr = "l.pos1 MOD 2">
        <tr class="alt_row">
        <mvt:else>
        <tr>
        </mvt:if>
            <td class="item-code">
                <a data-mm-linktype="product-list-link" href="&mvte:product:link;">&mvt:product:code; - &mvt:product:name;</a><br>
                <mvt:if expr="ISNULL l.settings:product:customfield_values:customfields:FITS">
                &nbsp;
                <mvt:else>
                &mvt:product:customfield_values:customfields:FITS;
                </mvt:if>

            </td>

            <td class="item-price">
                    &mvt:product:formatted_base_price;
            </td>

            <mvt:if expr="l.settings:product:inv_level NE 'out'">
            <td class="item-buttons">
                <form method="post" action="&mvte:urls:BASK:rr;">
                <input type="hidden" name="Old_Screen" value="&mvte:global:Screen;" />
                <input type="hidden" name="Old_Search" value="&mvte:global:Search;" />
                <input type="hidden" name="Action" value="ADPR" />
                <input type="hidden" name="Quantity" value="1" />
                <input type="hidden" name="Attributes" value="Yes" />
                <input type="hidden" name="Product_Code" value="&mvte:product:code;" />
                <input type="hidden" name="Current_Product_Code" value="&mvte:global:Product_Code;" />
                <input type="hidden" name="Category_Code" value="&mvte:global:Category_Code;" />
                <input type="hidden" name="Offset" value="&mvte:global:CatListingOffset;" />
                <input type="hidden" name="AllOffset" value="&mvte:global:AllOffset;" />
                <input type="hidden" name="CatListingOffset" value="&mvte:global:CatListingOffset;" />
                <input type="hidden" name="RelatedOffset" value="&mvte:global:RelatedOffset;" />
                <input type="hidden" name="SearchOffset" value="&mvte:global:SearchOffset;" />
                <mvt:foreach iterator="facet" array="facets:facets">
                    <mvt:foreach iterator="facet_value" array="facet:values">
                        <mvt:if expr="l.settings:facet_value:selected">
                            <input type="hidden" name="&mvte:facet:code;" value="&mvte:facet_value:value;" />
                        </mvt:if>
                    </mvt:foreach>
                </mvt:foreach>
                <mvt:item name="buttons" param="AddToBasketL" />
                </form>
            </td>
            </mvt:if>
        </tr>
        </mvt:foreach>
    </table>

The hard part for me is how to tell it what to load into the foreach iteration.

[Bruce - PhosphorMedia]

Not sure I follow, but you just need to add <mvt:if expr="'brake' IN tolower(l.settings:product:name)"/> right after the foreach

[lesliekirk]

Not sure I follow, but you just need to add <mvt:if expr="'brake' IN tolower(l.settings:product:name)"/> right after the foreach

Will that search for every product I'm thinking that is assigned to the parent category ID 38868? There is more involved than just finding every product with "brake" in it. This builds a list of brake part for a specific model and year.

[Bruce - PhosphorMedia]

Ah....yea...like need more coffee. To use Miva's prebuilt arrays, you'd need to lookup the catxp code for each product, better would be create your own array:

<mvt:do name="l.result" file="g.Module_Library_DB" value="Category_Load_Code( 'category_code', l.settings:current_category )" /> 
<mvt:do name="l.result" file="g.Module_Library_DB" value="Runtime_ProductList_Load_Offset_Category( l.settings:current_category:id, g.Offset, 100, g.NextOffset, l.settings:products )" /> 

<mvt:foreach iterator="product" array="products">
   &mvt:product:code;<br>
</mvt:foreach>

And then Test for "brake" in product name