My site displays a set of divs, each of which represents a collection which holds five different types of content. Each div shows how much items of each type of content is in that collection.
I currently get the number for each type from the database like so:
{section name=res loop=$results}
{assign var='wn' value=$db->num_rows($db->query("select * from content where type='1' and collection_id='{$results[res].collection_id}'"))}
{assign var='nn' value=$db->num_rows($db->query("select * from content where type='2' and collection_id='{$results[res].collection_id}'"))}
etc
The problem is that we're doing five database queries for each div, and we'd like to try to combine those into one. With php we would simply do
$w=$db->query("select type, count(*) as number from content where collection_id='".$val['collection_id']."' GROUP by type");
And then use a "while" loop with "mysql_fetch_array" to assign the type amounts to variables.
Is it possible to do something similar in Smarty? Is there a mysql_fetch_array alternative for Smarty to access the result set?
Thanks in advance!
EDIT: It's been suggested that I should do this from the php, butI'm not clear on how that would work. In my php file, I have the following:
<?php
$c=$db->query("select * from ".USERS_PIN."");
$cdata = array();
while ($row = mysql_fetch_array($c))
{
$cdata[] = $row;
}
$smarty->assign("results", $cdata);
$smarty->display('stack.tpl');
?>
Then in stack.tpl I have the following:
<!DOCTYPE html>
<html lang="en">
<head>
</head>
<body >
<div id="ColumnContainer">
{if count($results) > 0}
{section name=res loop=$results}
{assign var='wn' value=$db->num_rows($db->query("select * from pinrest_users_pin where type='4' and board_id='{$results[res].board_id}'"))}
{assign var='nn' value=$db->num_rows($db->query("select * from pinrest_users_pin where type='5' and board_id='{$results[res].board_id}'"))}
{assign var='in' value=$db->num_rows($db->query("select * from pinrest_users_pin where (type='1' or type='2') and board_id='{$results[res].board_id}'"))}
{assign var='vn' value=$db->num_rows($db->query("select * from pinrest_users_pin where type='3' and board_id='{$results[res].board_id}'"))}
<div class='item' style="height:70px;width:350px;float:left;border:2px solid aqua" data-id="{$results[res].id}">
<div class="datadiv" >
<div style="margin-top:3px;width:40%;float:left;margin-left:15px">{$nn} news {$vn} videos {$in} images {$wn} webpages</div>
</div>
</div>
{/section}
{/if}
</div>
</body>
</html>
I figured that being that I'm building the divs one at a time from the Smarty loop, I have no choice but to get the mysql data for each div one at a time in the loop. Is there a way for me to get that data for each div in the php file and to then assign it and use it in Smarty during the loop?
Doing SQL in your Smarty template defeats the purpose of using a templating system in the first place. Handle this in your controller file.
I'm a bit rusty on PHP's sql methods so adjust this with the correct methods for your implementation. Solution 1:
$smarty->display('beginning_of_view.tpl');
$q = $db->query('your query for all the divs');
while($row = mysql_fetch_assoc($q))
{
$smarty->assign('row', $row);
$smarty->display('my_div.tpl');
}
$smarty->display('end_of_view.tpl');
Solution 2 (probably the better way to go about it):
$rows = array();
$q = $db->query('your query for all the divs');
while($row = mysql_fetch_assoc($q))
{
$rows[] = $row;
}
$smarty->assign('rows', $rows);
$smarty->display('template.tpl');
//In your template
{foreach $rows as $row}
{$row}
{/foreach}
Hopefully this gets the idea across.
You assign the array returned by mysql_fetch_array to a smarty variable, then you loop with smarty
Here is an example:
you php file:
<?php
$arr = array(1000, 1001, 1002);
$smarty->assign('myArray', $arr);
?>
your smarty template
{foreach from=$myArray item=foo}
{$foo}
{/foreach}
EDIT:
For your requirement, you should use multimentional / nested arrays, have a look to this question: php smarty loop multidimensional array
In your php, do the 4 queries ( those from your tpl ) inside the main query loop and add the results as fields on $row :
<?php
$c=$db->query("select * from ".USERS_PIN."");
$cdata = array();
while ($row = mysql_fetch_array($c))
{
$d = $db->query("select * from pinrest_users_pin where type='4' and board_id=".$row['board_id']);
$row['wn'] = mysql_fetch_array($d); // repeat 4X
$cdata[] = $row;
}
$smarty->assign("results", $cdata);
$smarty->display('stack.tpl');
?>
Then in tpl use $results[res].wn in your div.