Solved

Looping through DB Query

Posted on 2003-10-28
7
177 Views
Last Modified: 2013-12-24
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?
0
Comment
Question by:rcmb
  • 6
7 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 9639714
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
 
LVL 14

Expert Comment

by:hhammash
ID: 9657084
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
 
LVL 14

Expert Comment

by:hhammash
ID: 9657112
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 14

Expert Comment

by:hhammash
ID: 9657825
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
 
LVL 14

Expert Comment

by:hhammash
ID: 9657832
You are to Select from the database and order by Staff
0
 
LVL 14

Accepted Solution

by:
hhammash earned 500 total points
ID: 9662324
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
 
LVL 12

Author Comment

by:rcmb
ID: 9668016
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

3 Experts available now in Live!

Get 1:1 Help Now