I am trying to create a display of multiple levels of membership, and wondered about the pros and cons of doing recursive DB queries vs. drawing in all data in a single query, and then developing a means of manipulating the data in the array into a format that would be presentable?

My thoughts are that the less you have to hit the DB, the better off you will be performance wise, but then if you consider that the membership has the potential to grow into the 10's of thousands or more, is it unreasonable to think that all that data could be manipulated efficiently from a single array?

The query is simple and only gets 4 fields
ref_id, mem_id, username, status

What I want to do is show a sort of family tree for the first 3 levels, and then basically for each member on level 3, show a count of the people on all the remaining levels that are in their downline

Does this sound like something that could be managed with a single query and manipulating the data in a single array?

I've generated sample data inserts into a table users and did a line item display of the data to view the results. The mem_id was auto incremented, and the ref_id was randomly selected from all available mem_id's, pushing each new one as it was created onto the array. This was the best way I could figure to come up with a random sampling, as if it were real life membership data.

I was thinking that I could possibly create an array for each of the levels 1, 2, and 3, that would contain only the mem_id and the corresponding ref_id, and then display the data by using 3 levels of embedded loops, but I don't know how to go about getting the count for the balance of the records that are related to each of the 3rd level records.

I hope this isn't too confusing. The more I type, the more I question what I am doing...

Any suggestions would be greatly appreciated, especially if I am heading down a road that I shouldn't be on.

thanks in advance for your feedback.

Doug

BTW, here is the table of data that I generated starting with mem101, and the L1 members are referred by 101, the L2 members referred by the previous L1, i.e. 106 referred 107, 123, 126, and 174, etc.

[pre]start L1 L2 L3 L4 L5 L6
101 103
104
106 107 114 142 146 171
197
120 143 188
205
124 135
136 152 161
193
198
123 138
194
126 154
174 190
108 110 113 160 191
116 102
117 122 158
130 145
184
162
148 187
139
144 186
195
109 112 134 165 168
181
115 121
127 175 189
132 157 169
156 204
179 185
111 170
118 125 164 192
133 147 155 183
141 182
201
119 129 131 200
153 196
203
173 177 180
137 163
151 199
159
166 167
172
128 140 150
149
176
178[/pre]

    Guess you can ignore the table of data... The formatting is no longer in place, so it has no meaning at all.

    Sorry...

    MOD EDIT: Added [pre] tags to preserve formatting.

      showman13;10946952 wrote:

      I was thinking that I could possibly create an array for each of the levels 1, 2, and 3, that would contain only the mem_id and the corresponding ref_id, and then display the data by using 3 levels of embedded loops, but I don't know how to go about getting the count for the balance of the records that are related to each of the 3rd level records.

      I hope this isn't too confusing. The more I type, the more I question what I am doing...

      Any suggestions would be greatly appreciated, especially if I am heading down a road that I shouldn't be on.

      thanks in advance for your feedback.

      Doug

      Your requirement is you have a level hierarchy concept is it ? This is a recurring theme in a lot of business world-wide that the proprietary rcle database has come up with their own optimzed SQL call CONNECT PRIORY BY syntax.

      Basically within a single SQL you can extract the level hierarchy information. This is proprietary solution of cuz but if it can give you the performance and the data you need so why not use it isn't it ?

      Thanks.

      PS Please ignore my suggestion if you are not using the rcle database.

        sohguanh;10946955 wrote:

        Your requirement is you have a level hierarchy concept is it ? This is a recurring theme in a lot of business world-wide that the proprietary rcle database has come up with their own optimzed SQL call CONNECT PRIORY BY syntax.

        using MySql. and yes, it is a sort of a multi-level hierarchy.

        Thanks for the reply

          showman13;10946956 wrote:

          using MySql. and yes, it is a sort of a multi-level hierarchy.

          Thanks for the reply

          If you do a Google search, this is an often requested feature in the MySQL forums and I guess you got no choice but to do it the iterative or recursive way you mention in Thread#1. Maybe the next MySQL release will incorporate the CONNECT PRIOR BY syntax ?

          I am sorry I can't help you.

          Thanks.

            Thanks for trying anyway.

            Yes, Google is the first place that I go, always. And actually phpbuilder is generally the last place that I come, because I don't want to be a pest, plus I know that I always get the answers I need here, if I can't find them anywhere else.

              Actually, I realized that is isn't important to have the count of downline members after the 3rd level, so all I really need to be able to do is find 3 levels of referrals, and display them in a tree type format:

              level1
level1
    level2
    level2
        level3
    level2
        level3
        level3
        level3
    level2
level1
    level2

              ect...

              Should be very simple, but I have gotten totally lost in the process.

              Whatever help there is out there, I could use it..

              thanks
              doug 😕

                Well, I put it together in the only way I knew how, but it entails using numerous DB queries, and I was hoping that I could find a way to do it with one. As it stands, with just 102 sample data records in the DB, it took 44 DB queries to accomplish what I needed, returning a total of 64 records.

                I will show you the code in this message, but first , if anybody has any suggestions on a better way to accomplish this, I would love to hear them. When this DB gets into the thousands and tens of thousands, I can't imagine how many queries it will take to produce the same end result.

                Thanks, and I hope I get some feedback from someone very soon.

                Used this function to do DB queries:

                function find_refs($aff_id){
                $sql="SELECT mem_id, user, status FROM users WHERE ref_id = $aff_id ORDER BY mem_id";
                $result=mysql_query($sql) or die(mysql_error()."<br><br>".$sql);
                return($result);

                Main body of display script:

                // Get username of member requesting their downline display
                $sql = "SELECT user FROM users WHERE mem_id = '$aff_id' LIMIT 1";
                $result=mysql_query($sql) or die(mysql_error()."<br><br>".$sql);
                $row=mysql_fetch_array($result);
                $top_user=$row[0];// starting point for referrals listing

                // Set up display table  w 3 columns

                echo "<table align=\"center\" width=\"450\" cellpadding=\"2\" cellspacing=\"0\">";
                echo"<tr><td colspan=\"3\" align=\"left\"><strong>".$top_user."</strong></td></tr>";
                echo"<tr><td align=\"center\"><strong>Level 1</strong></td><td align=\"center\"><strong>Level 2</strong></td><td align=\"center\"><strong>Level 3</strong></td></tr>";

                $returned1=find_refs($aff_id); // call function with target refid
$rows1=mysql_num_rows($returned1);
for ($i1=0; $i1<$rows1; ++$i1){ // step through 1st level referrals
    $lev1[$i1]=mysql_fetch_array($returned1); // assign values to lev1 row
    // print each level 1 referral
    echo"<tr bgcolor=\"#5CB3FF\"><td height=\"5\">".$lev1[$i1][1]."</td><td colspan=\"2\">&nbsp;</td></tr>";

    $returned2=find_refs($lev1[$i1][0]); // call function with lev 1 target refid
    $rows2=mysql_num_rows($returned2);
    if ($rows2>0){  // if there are level 2 referrals for this level 1 member
      for ($i2=0; $i2<$rows2; ++$i2){// step through 2nd level referrals
        $lev2[$i2]=mysql_fetch_array($returned2);
          echo"<tr bgcolor=\"#56A5EC\"><td height=\"5\">&nbsp;</td><td>".$lev2[$i2][1]."</td><td>&nbsp;</td></tr>";
          // print each level 2 referral

        $returned3=find_refs($lev2[$i2][0]); // call function with lev 2 target refid
        $rows3=mysql_num_rows($returned3);
        if ($rows3>0){
          for ($i3=0; $i3<$rows3; ++$i3){ //step through 3rd level referrals
            $lev3[$i3]=mysql_fetch_array($returned3);
            echo"<tr bgcolor=\"#488AC7\"><td colspan=\"2\" height=\"5\">&nbsp;</td><td>".$lev3[$i3][1]."</td></tr>";
           // print each level 3 referral
          }
        }
      }
    }
  }

                echo "</table>";

                  Write a Reply...