X-Cart forums - MySQL query in smarty template

X-Cart forums (https://forum.x-cart.com/index.php)

- Changing design (https://forum.x-cart.com/forumdisplay.php?f=51)

- - MySQL query in smarty template (https://forum.x-cart.com/showthread.php?t=64220)

MySQL query in smarty template

Is it possible to run a MySQL query and assign the value to a variable directly within a smarty template file? I cannot seem to find any info on this.

Re: MySQL query in smarty template

You can use {php} php code here / assign variable to smarty {/php} but I wouldn't recommend this. Do it directly in the php file instead

Re: MySQL query in smarty template

Thanks.

If that is the case, how would I do so for template(s) I created?

What I am trying to accomplish:

- when a user clicks on a main category link in the top horizontal bar, the left bar menu will display the sub categories for that category. I have been able to do this using {if $categories} in left_bar.tpl

- then when the user clicks a sub-menu link from the left bar, I need to have the sub categories listed again, but with the products for that category listed below it. This is where I thought I would use the MySQL query below to retrieve the sub categories and $cat_products to list the products below the current sub category.

Code:

SELECT categoryid FROM xcart_categories WHERE parentid = {$current_category.parentid} ORDER BY categoryid ASC

- lastly when a product is clicked on the same menu structure needs be displayed as when the sub category is clicked

## Left bar ##

Code:

{if $categories}

    {include file="customer/main/category_pages_nav_create.tpl"}

{elseif $current_category}

    {include file="customer/main/subcategory_pages_nav_create.tpl" products=$cat_products}

{elseif $cat_products}

    {include file="customer/main/product_pages_nav_create.tpl" products=$products}

{/if}

preview so far: http://64.64.19.63/~sklz/newsite/store/Baseball/

Re: MySQL query in smarty template

Quote:

- lastly when a product is clicked on the same menu structure needs be displayed as when the sub category is clicked

I was able to figure out this last requirement by going to the product.php file in the xcart root folder and adding the code below. I discovered I had to add this before the $smarty->assign of $product_info to be able to use its value.

PHP Code:


		
			
$smarty->assign("sub_categories_nav", func_query("SELECT categoryid, category FROM xcart_categories WHERE parentid = (SELECT parentid FROM xcart_categories WHERE categoryid = ".$product_info['categoryid'].") ORDER BY categoryid ASC"));
$smarty->assign("sub_category_products", func_query("SELECT ple.productid, ple.product FROM xcart_products_lng_en AS ple INNER JOIN xcart_products_categories AS pc ON ple.productid = pc.productid WHERE pc.categoryid = ".$product_info['categoryid']." ORDER BY ple.productid ASC"));

$smarty->assign('product', $product_info);

Then in my custom product_pages_nav_create.tpl my code is:

Code:

        {foreach from=$sub_categories_nav item=subcat name=subcategories_nav}

          <div class="subcategories_nav"{interline name=subcategories_nav}>

            &raquo; <a href="home.php?cat={$subcat.categoryid}">{$subcat.category|escape}</a>

          </div>

          {if $product.categoryid eq $subcat.categoryid}

             <ul class="subcategories_prod">

            {foreach from=$sub_category_products item=subcat_prod name=subcategories_prod}

                  <li{interline name=subcategories_prod}>

                    &raquo; <a href="home.php?cat={$subcat_prod.productid}">{$subcat_prod.product|escape}</a>

                  </li>

            {/foreach}

              </ul>

          {/if}

        {/foreach}