I am using dbmansql to provide a search facility on a craft website. The search displays thumbnails and names of products. When a thumbnail is clicked, dbmansql displays further product information and a large image. I want to extend the further information displayed by pulling in information from other tables. For example, I want to list related products: -

Tables

Products: Prod_Code(PK), Prod_Name, Desc, List_Price, Our_Price

Related_Products: Rel_Prod_Code(FK), Rel_Code, Rel_Desc

(A one to many relationship between Products and Related_Products)

I have modified the homes templates which came with the installation. Specifically the search_results.html and record_form.html templates. search_results calls record_form as follows: -

<%loop results%>
<p><%include record_form.html%>
<%endloop%>

and record_form simply formats and displays each product attribute. How can I lookup the multiple rows of related information from the Related_Products table?

In record_form I would like to do the following: -

Select * from Related_Products where Rel_Prod_Code = <%Prod_Code%>

Loop for each result

Display Rel_Code and Rel_Name

End Loop

I am sorry that this may appear a very basic problem but I have spent hours trying to understand the relationship between globals, templates, functions etc and I would just like someone to point me in the right direction.

Thanks!