display results of mysql query in template

[fogsys]

$result = mysql_query("SELECT * FROM product inner join members ON product_member_id = members_sid WHERE members_username='username'") ;	

while($row = mysql_fetch_array($result))
  {
	  $template->assign_vars(array(
		'PRODUCT' => $row['product_title'],
		'USERNAME' => $row['members_username'],
		'COUNTRY' => $row['members_country'],
		
	));

when I use this code, I try to display all the products linked to the 'username', it only displays ONE product.
I am trying to get it to display all of the products.I used the while loop ...it still displays just one.
any suggestions? Thank you

[Atli]

Hey.

Knowing nothing about the template class you are using, I would guess that you keep overwriting the same variables each loop, therefore only displaying the last row returned by your query.

If you need to pass multiple values for the same variable, you need to use an array.

[fogsys]

any example?
to assign a variable to the template you use :
$template->assign_vars(ar ray(
'PRODUCT' => $row['product_title']
'variable' => $variable
etc etc))

[Atli]

What you are assigning the template right now is a list of variables that looks something like this:
[code=php]$var = array(
'PRODUCT' => 'p1',
'USERNAME' => 'u1',
'COUNTRY' => 'c1'
);[/code]
I'm assuming here that each of the element names there ("PRODUCT", "USERNAME" and "COUNTRY") represents a variable name available to your template.

In that case, to assign a list of values for each of them you would want their values to be an array. Somewhat more like this:
[code=php]$var = array(
'PRODUCT' => array('p1', 'p2', 'p3'),
'USERNAME' => array('u1', 'u2', 'u3'),
'COUNTRY' => array('c1', 'c2', 'c3')
);[/code]

However, a better approach might be to assign a single two-dimensional array, containing a list of product. That makes it easier to loop through in your template. (Assuming, of course, that your template system is capable of that.)
[code=php]$var = array(
'products' => array(
array(
'title' => 'product 1',
'username' => 'user 1',
'country' => 'country 1'
),
array(
'title' => 'product 2',
'username' => 'user 2',
'country' => 'country 2'
),
array(
'title' => 'product N',
'username' => 'user N',
'country' => 'country N'
)
)
);[/code]
Then you would only have a single variable, "products", available in your template, which would essentially be a table of products, each element representing a data row.

In a Smarty template, you could use that as:
[code=html]{foreach from=$products item=product}
<p>
Title: {$product.title }<br>
User : {$product.usern ame}<br>
Country: {$product.count ry}
</p>
{/foreach}[/code]

[fogsys]

thank you sir. I am trying it right now. will confirm as soon as I implement it

[fogsys]

oh and just for info,im using the phplib template
if you google it its the first link.But Im trying to figure it out myself.thank you again