StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am adding some more people just to be clear I understand what you need:</p> <pre><code>userid | name | subof 1 | John | 0 2 | Joe | 1 3 | Jill | 0 4 | Janet | 2 5 | Dawn | 4 6 | James | 4 7 | Mary | 3 8 | Doug | 6 </code></pre> <p>So say your boss asks for the people under Joe. You want to get: Janet, Dawn, James, Doug -- right?</p> <p>Instead of adding a new column, how about changing definition of subof (in my example I have made it a varchar)? </p> <p>So your table would like this:</p> <pre><code>userid name subof 1 John 0 2 Joe 0.1 3 Jill 0 4 Janet 0.1.2 5 Dawn 0.1.2.4 6 James 0.1.2.4 7 Mary 0.3 8 Doug 0.1.2.4.6 </code></pre> <p>The top of the pyramid are 0, so John and Jill are still at the top. Then you know who is under each by the sequences following 0. </p> <ul> <li>changed john and jill to 0 instead of 0.0 to make updates easier </li> </ul> <p>Doing it this way you could get the results you need in the following query:</p> <pre><code> select * from temporary WHERE subof like '0.1.2%' ORDER BY userid ASC; //this is joe's subof +'.'+ his userid </code></pre> <p>So your next issue is how to insert a new recruit. OK. Bill comes on under Doug. So what would the insert be for Bill?</p> <p>// First get the subof and userid</p> <pre><code>SELECT subof, userid FROM tablename WHERE name = 'doug'; #answer 0.1.2.4.6 $subof, $userid = mysql_fetch; //pseudo code </code></pre> <p>//Then insert the new row which would be subof.userid</p> <pre><code>INSERT into tablename (userid, name, subof) VALUES ('9', 'Bill', '$subof.userid'); </code></pre> <p>So now you have another row:</p> <pre><code>9 Bill 0.1.2.4.6.8 </code></pre> <p>But wait ... there's more! </p> <hr> <p><em>Replaced example with James and Doug for new table with George and Juan to focus on modified question</em>: </p> <p><strong>===== new example with George and Juan</strong></p> <pre><code>userid | name | subof 1 | John | 0 2 | Joe | 0.1 3 | Jill | 0 4 | Janet | 0.1.2 5 | Juan | 0.1 6 | George| 0.1.2 </code></pre> <blockquote> <p>John and Jill are at the top, Joe and Juan are below John, and Janet and George are below Joe. The tier-ing is used for passing up commission. </p> </blockquote> <p><strong>QUESTION</strong></p> <blockquote> <p>My client wants to be able to see How many users are below any given user, (at least it's restricted to 8 tiers out)</p> </blockquote> <p><strong>ANSWER</strong></p> <pre><code> SELECT count(*) FROM tablename WHERE subof LIKE 'subof_of_any_given_user+that_users_userid%'; //get those under Joe by using '0.1.2' (Joe's subof + his userid) </code></pre> <p><strong>QUESTION</strong></p> <blockquote> <p>it get's hairy when my client comes and says "Oh, George meant to join under Juan, please move him"</p> </blockquote> <p><strong>ANSWER</strong></p> <p>SELECT userid, name, subof FROM tablename WHERE name in('Juan','George');</p> <pre><code>//$juan_userid = 5 //$juan_subof = 0.1 //$updatevalue = $juan_subof.'.'.$juan_userid; //0.1.5 //$george_userid = 6 //$george_subof = 0.1.2 /$subofmatch = $george_subof.'.'.$george_userid; //0.1.2.6 </code></pre> <p>so your automated query would look something like this:</p> <pre><code>UPDATE tablename SET subof = (REPLACE(subof, '$george_subof', '$updatevalue')) WHERE (subof like '$subofmatch%' OR userid = '$george_userid') // here it is with number values to make it easier to understand // UPDATE tablename SET subof = (REPLACE(subof, '0.1.2', '0.1.5')) WHERE (subof like '0.1.2.6%' OR userid = '6'); </code></pre> <p>Giving you this new result:</p> <pre><code>userid name subof 1 John 0 2 Joe 0.1 3 Jill 0 4 Janet 0.1.2 5 Juan 0.1 6 George 0.1.5 </code></pre> <p>Enjoy!</p> <p>Dawn</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload