using sql helpers and db connection in tpl files

[leemason]

im trying to populate a menu list with product groups from the db.

 

problem is its not working???

 

this is what i have in my header.tpl:

 

{php}
					$links = select_query('tblproductgroups', 'id,name,order', '');
					$linksarray = array();
					while($link = mysql_fetch_assoc($links)){
						$linksarray[$link['order']] = $link;
					}
					foreach($linksarray as $link){
						echo '<li><a href="cart.php?gid='.$link['id'].'">'.$link['name'].'</a>';	
					}
					{/php}

 

seems simple enough?

 

can anyone tell me why this doesnt work?

 

ive also tried a generic mysql query without the helper function with no luck.

 

i know i can port this into a hook.php file and assign a var, but would like to keep it in the template folder, its for a client so having files all over the place isnt ideal

[laszlof]

{php}
$data = select_querry('tblproductgroups', 'id, name, order', array());
$linksarray = array();
while ($res = mysql_fetch_assoc($data)) {
 $id = $res['id'];
 $linksarray[$id] = $res;
}

foreach ($linksarray as $gid => $link) {
 echo '<li><a href="cart.php?gid='.$gid.'">'.$link['name'].'</a>';
}
{/php}

[leemason]

nope, still an empty array, nothing printed (and yes i did correct the spelling mistakes).

 

ive used the select query before and you can pass an empty string for the where and it functions the same as passing an empty array.

 

but still nothing your way.

 

does anybody think t may be because i am viewing the theme using the ?systpl= get var, and not using it as the active theme?

[laszlof]

try var_dump($res) within the while loop, see if its returning anything. you can also try using full_query instead and see if that helps. It could be the "order" field, being that "order" is a protected sql command. Though I would think the sql helpers would escape that properly.

 

Is there any particular reason you need the "order" field, or were you just using it as a key? If you're simply wanting to display them in the proper "order" based on the database, you should use the ORDER BY clause anyways and let the array keys assign themselves. Heres what I mean:

 

{php}

$linksarray = array();
$data = select_query('tblproductgroups', 'id, name', array(), 'order', 'ASC');
while ($res = mysql_fetch_assoc($data)) {
 $linksarray[] = $res;
}

foreach ($linksarray as $key => $link) {
 echo '<li><a href="cart.php?gid='.$link['id'].'">'.$link['name'].'</a></li>';
}

{/php}

[laszlof]

Just tested the code I last posted, it works fine for me in the homepage.tpl template (it shouldnt matter what template file you use). I cant imagine defining systpl to something different would have any affect so long as you're specifying the template where the code is at.

[leemason]

cool thanks for the help, it is the "order" in the select fields taht was causing the issue.

 

never thought to use the order by, it must be my bed time.

 

thanks again

[laszlof]

I would have assumed that the helper functions would have stepped through and escaped (backticks) the select fields so that doesnt happen. Good to know thats not the case.

[leemason]

yes mee too.

 

also for anyone who come across this thread in need of help, because of franks solution you dont need to assign to array to get in the order.

 

just echo within the while:

 

{php}
$linksarray = array();
$data = select_query('tblproductgroups', 'id, name', array(), 'order', 'ASC');
while ($res = mysql_fetch_assoc($data)) {
 echo '<li><a href="cart.php?gid='.$res['id'].'">'.$res['name'].'</a></li>';
}
{/php}

[laszlof]

Another thing I'd probably do if it was mine, instead of using echo, just pass the variable into smarty and use a smarty foreach loop.

 

At the top of the template:

{php}
$linksarray = array(); 
$data = select_query('tblproductgroups', 'id, name', array(), 'order', 'ASC');
while ($res = mysql_fetch_assoc($data)) {
 $linkarray[] = $res;
}
$this->_tpl_vars['grouplinks'] = $linkarray;
{/php}

 

Then where ever you want to display the links:

 

{foreach from=$grouplinks key=num item=link}
 <li><a href="cart.php?gid={$link.id}">{$link.name}</a></li>
{/foreach}

 

I tend to keep PHP as separate as possible, so this would be a much cleaner solution ib my eyes.