Looping through DB Query

I have two tables for personnel (Staff, Children). I use the SSN as the link from Staff to Children. What I would like to do is list each staff member on one line and have the query loop through the Children Table to list all children of the staff member and then continue on to the next staff member. Everytime I try it I end up with the staff member being listed for every child. Any ideas?
LVL 12
rcmbAsked:
Who is Participating?
 
hhammashCommented:
Hi again,

To have the same output of the page that I have posted above, you can use the <ul> and <li> tags instead of the &nbsp within your table.  Here is a sample of my code,  please notice the use of <ul> and <li> in the code.

<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%
If curCountry <> FP_Field(fp_rs, "Country") Then
%>

<TR>
   <TD><b>
   <%=FP_FieldVal(fp_rs,"Country")%> &nbsp;<TD></b>
   <tr>
      <%
   curCountry = FP_Field(fp_rs, "Country")
End If
%>
<tr>
   <td><ul>
      <li><%=FP_FieldVal(fp_rs,"CompanyName")%></li></td>
<tr>
</ul>
<!--#include file="_fpclass/fpdbrgn2.inc"-->


regards
hhammash
0
 
hhammashCommented:
Hi,

I used this for my Customers table, I wanted to display country name then customers and cities.  Here is the code and the explanation, you can modify to suite your data.  I don't know exactly your fields and their names.  But here I have the Country and City and Customers.  Example:
I was able to display
USA
   City1
     Customer 1
     Customer 2
   City 2
     Customer 1
     Customer 2
     Customer 3
France
   City 1
     Customer 1
     Customer 2

In your case it would be
Staff Name
    Child 1
    Child 2
    Child 3

Explanation:
--------------
When you look at the dieted DRW code you can see that it loops through the DB and puts out the results...

<td><%=FP_FieldVal(fp_rs, "country")%></td>
<td><%=FP_FieldVal(fp_rs, "City")%></td>
<td><%=FP_FieldVal(fp_rs, "Customer")%></td>

You need to do a bit of custom stuff.

Add two variables to the DRW variable list...

Dim curCountry, curCity

then edit the loop


code:--------------------------------------------------------------------------------
<%
If curCountry <> FP_Field(fp_rs, "Country") Then
%>
      <tr>
          <td><%=FP_FieldVal(fp_rs, "Country")%></td>
      </tr>
<%
      curCountry = FP_Field(fp_rs, "Country")
End If

If curCity <> FP_Field(fp_rs, "City") Then
%>
      <tr>
          <td><%=FP_FieldVal(fp_rs, "City")%></td>
      </tr>
<%
      curCity = FP_Field(fp_rs, "City")
End If
%>
<tr>
      <td><%=FP_FieldVal(fp_rs, "Customer")%></td>
</tr>

Hope it works for you

Regards
hhammash
0
 
hhammashCommented:
Hi again,  your code with two fields should look like this:


fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
----------------- The above 5 lines are in your page already ----------------
-----From Here the code starts -----------

Dim curStaff
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<%
If curCountry <> FP_Field(fp_rs, "Staff") Then
%>

<TR>
   <TD><b>
   <%=FP_FieldVal(fp_rs,"Staff")%> &nbsp;<TD></b>
   </tr>
   <%
   curCountry = FP_Field(fp_rs, "Staff")
End If
%>
<tr>
   <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <%=FP_FieldVal(fp_rs,"Children")%>&nbsp;</td>
     </TR>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</TBODY>
</TABLE>

</BODY>


Hope it works

hhammash
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
hhammashCommented:
Hi,

I made a mistake on top, I don't know why we can't edit our post:  Take this please:

Now,  add this variable to the end of your variables list:

Dim curStaff


----- After doing this Your list of variables should look like -----
fp_sMenuValue=""
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID

Dim curStaff
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->

----- Start editing the code from here ---------------

<%
If curStaff <> FP_Field(fp_rs, "Staff") Then
%>

<TR>
   <TD><b>
   <%=FP_FieldVal(fp_rs,"Staff")%> &nbsp;<TD></b>
   </tr>
   <%
   curStaff = FP_Field(fp_rs, "Staff")
End If
%>
<tr>
   <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  <%=FP_FieldVal(fp_rs,"Children")%>&nbsp;</td>
     </TR>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</TBODY>
</TABLE>

</BODY>
</html>

Those many &nbsp; is to give space to indent the children. The staff name will be in
bold and the child's name will be normal and indented.  You can remove them if you like.

Regards
hhammash

0
 
hhammashCommented:
Hi,

Check the result here:

I am displaying the country and the companies in it.

http://www.fmhs.uaeu.ac.ae/nml/ListForCountry.asp

regards
hhammash
0
 
hhammashCommented:
You are to Select from the database and order by Staff
0
 
rcmbAuthor Commented:
hhammash,
Actually your first option worked fine. I did a little manipulation but got it displaying just as you said. Thanks for your help.
RCMB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.