Solved

Looping through DB Query

Posted on 2003-10-28
7
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
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…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

707 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