Query Problem

1. Senior Member
Join Date
Dec 1969
Posts
121

## Query Problem

Hi,<BR><BR>I have been asked to create an ASP with results from a database. The info I need to display is:<BR><BR>1 table, 2 fields<BR><BR>Field1 = New Member<BR>Field2 = Referrer<BR><BR>record1: Member=5, Referrer=1<BR>record2: Member=19, Referrer=1<BR>record1: Member=421, Referrer=5<BR>record2: Member=225, Referrer=5<BR>record1: Member=346, Referrer=19<BR>record2: Member=678, Referrer=19<BR><BR>So you could end up with a pyramid effect for each member.<BR><BR>So member no 1 has a downline of:<BR><BR>[1]<BR>[5][19]<BR>[421][225][346][678]<BR><BR>Does anyone know how to accomplish this???<BR><BR>Darrell.....<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Redesign the DB

You need a "fullDownline" field that keeps the entire history.<BR>Member=1, Referrer=NULL, downline="0001"<BR>Member=5, Referrer=1, downline="0001-0005"<BR>Member=19, Referrer=1, downline="0001-0019"<BR>Member=421, Referrer=5, downline="0001-0005-0421"<BR>Member=225, Referrer=5, downline="0001-0005-0225"<BR>Member=346, Referrer=19, downline="0001-0019-0346"<BR>Member=678, Referrer=19, downline="0001-0019-0678"<BR><BR>Then it gets easy:<BR><BR>SELECT * FROM table WHERE downline LIKE &#039;%0001%&#039; ORDER BY downline<BR><BR>and it all falls neatly out.<BR><BR>Be sure to pad all the "pieces" of the downline (as well as the number you use for the LIKE test) to the same length.<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
121

## RE: Redesign the DB

Hi Bill,<BR><BR>Thanks for the feedback.<BR><BR>Sorry but I am struggling to understand how to get it to work!<BR><BR>At the minute a guest goes to an existing members web page and click "sign me up" this posts the existing members ID (Referrer) and then creates a new members ID (Member) and posts into db as mentioned above.<BR><BR>How would you collate the info to go into the downline field?<BR><BR>Many Thanks<BR><BR>Darrell.....<BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## I'll give it a shot...

5. Senior Member
Join Date
Dec 1969
Posts
121

## RE: I'll give it a shot...

Bill, thanks a million.<BR><BR>That will give me a basis to work on.<BR><BR>Many Thanks<BR><BR>Darrell.....

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oops...MAJOR GOOF in that!

&#039; and update this new member:<BR>SQLfix = "UPDATE membersTable SET downline = &#039;" & newDownline & "&#039;[hl="yellow"] WHERE memberID=" & newID[/hl]<BR><BR>If you don&#039;t put the WHERE on the UPDATE you&#039;ll change *all* the records. DOH on me!<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•