Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Displaying Related Data From Another Table

Quote Reply
Displaying Related Data From Another Table
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!

Last edited by:

danah: Dec 30, 2003, 8:03 AM
Quote Reply
Re: [danah] Displaying Related Data From Another Table In reply to
Hi,

In this case, you should use the 'Query' feature which provides you a way to select data from related tables. Take a look at the 'Relationships' help pages for more information.

TheStone.

B.
Quote Reply
Re: [TheStone] Displaying Related Data From Another Table In reply to
Thanks Stone.

OK. I have created a relationship and a query. How do I cycle through this queries records in my custom template?
Quote Reply
Re: [danah] Displaying Related Data From Another Table In reply to
To display the query's search form in DBMan SQL after it has been created:

db.cgi?db=table_names&do=qsearch_form&q=query_name

If you want to customize the search_form or search_results form, you have to use:

<input name="table_name.field_name" value="" >

or

<%loop results%>
<%table_name.field_name%>
<%endloop%>

To find out more information about this feature, take a look at the help pages.

Hope that helps.

TheStone.

B.