Solved

Looping through DB Query

Posted on 2003-10-28
7
185 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
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 
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

Report: Liquid Web beats Amazon, Rackspace & More

A study by performance analyst firm Cloud Spectator finds that Liquid Web beats rivals Amazon, Rackspace and DigitalOcean when it comes to website and cloud application performance.

Question has a verified solution.

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

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

736 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