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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
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

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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
hhammashCommented:
You are to Select from the database and order by Staff
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.