Query has something wrong

[Gotharious]

Hey all,

 

I get many warnings  saying "Warning: extract() [function.extract]: First argument should be an array in /home/www/waw-eg.com/Users.php on line 93

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/www/waw-eg.com/Users.php on line 93"

 

 

while($row = mysql_fetch_array($result))
  {
  	  $query = "SELECT ".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = ".$row['id'].") AS count_1,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_1) AS count_2,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_2) AS count_3,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_3) AS count_4,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_4) AS count_5,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_5) AS count_6,".
      " (SELECT COUNT(*) FROM users WHERE recruiteris = count_6) AS count_7,";

$res = mysql_query($query);
extract( mysql_fetch_assoc($res) );

                $total = $count_1 + $count_2 + $count_3 + $count_4 + $count_5 + $count_6;
                $var3 = '10';
                $commission = $total * $var3; 

[Pikachu2000]

That query is not going to work as it's written. What are you trying to do there?

[Gotharious]

I want the result of the first query to be used in the second and so on

 

for example, search for user id from users where recruiterid = 1

so I get 2 results 2 and 3

so the second query search for user id from users where reruiterid= 2

and so on and the result of each query would be used in the query after

[Pikachu2000]

I have to admit I'm not completely clear on what your end goal is, but I don't think COUNT() is what you're after here. It appears you're executing a query in a loop also, which should be avoided as it's (almost) never necessary or wise.

 

Maybe if you post some sample data and expected results, someone can help you get this figured out. This seems like it's going to be a MySQL issue rather than php, so I'll move the thread there . . .

[Gotharious]

Ok, thanks anyway

[Gotharious]

What I want to do is, calculate a commission of a user by summing the number of people he recruited and the number of people they recruited and so on till the last group recruited who will be the 13th group if we consider the first group he recruited is the 1st and the group they recruited is the 2nd

 

and then multiply the number by 10 and echo the result

[mikosiko]

post your table(s) description(s)

[Gotharious]

Field              Typr        Null        Default              Key                        Extra

id               int(11) NO         NULL              PRI            auto_increment

fname           text NO NULL

mname           text NO NULL

lname           text NO NULL

mobile           text NO NULL

tel                  text  NO NULL

job                   text NO NULL

company           text NO NULL

address           text NO NULL

email           text NO NULL

recruiteris       int(11) NO NULL

nid                   text NO NULL

password           text NO NULL

type                   text NO NULL

position varchar(35) NO NULL

[mikosiko]

here is a couple links that could help you:

 

http://www.sitepoint.com/hierarchical-data-database/

 

and this presentation is a must...

http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

slides 48 to 77 show you alternatives... pay attention to the  "Closure Table" implementation... the implementation should allow you to solve your query easily (and many more)

 

[Gotharious]

Thanks A lot, mate

 

Will post here when I figure this out or have any more questions

[fenway]

I'm sorry, you want to do what/

[Gotharious]

Ok, this is a multi level marketing thingy, wher each person recruits 2 people, and each of those two recruit 2 people and so on

each person gets commission of the 2 he recruited and every new recruit down his downline

 

so if I recruited 2 people, each recruited 2, and then each of the ones they recruited recruited 2

thta's 2 + 4 + 8 = 14

So to calculate my comission, lets say it's $10 per recruit, then it would be $140 for me, and for the 2 people below me it would be $60 each

 

now I have only one table where all this information is stored in user id and recruiter id

 

the recruiter id of the 2 people i recruit would be my user id

 

what I want to do now is to query the table to count the number of people in my downline

 

I figured to do that, I have to query my userid in the recruiter id column and then use the result of the two people and query their user ids in the recruiter id column to find out the number they recruited and so on

 

is it clear now?

[fenway]

Yup, the previous comments were correct -- you're dealing with a hierarchy here.

 

If you would store the "depth" when you store the original data, life would be easy.