php - How get multiple values from first mysql table and use it in second query? -

table1

id | author       | number_of_view | ---+--------------+----------------- 1  | john         | 10     2  | jack         | 20    3  | anna         | 80     4  | neri         | 100   

below have php script checks mysql database table , gives badge users. script works prefectly.

$from1=10; $to1=15;  $from2=20; $to2=30;  $query=mysql_query("select sum( number_of_view ) view_number, author `table1`  group author");   while ($row=  mysql_fetch_array($query))         {             $view_number=$row['view_number'];             $author=$row['author'];               if ($viewnumber>$from1 && $viewnumber<=$to1)             {                 echo "special user";              }             elseif ($viewnumber>$from2 && $viewnumber<=$to2)             {                 echo "plain user";             }         } } 

but problem want $from , $to variable values database table:

table2

id | badge       | |to ---+-------------+------+--- 1  | special user| 10   |15 2  | plain user  | 20   |30 

how can that? thank in advance help.

i thought quite simple, mistaken in that, there complicating factors have aware of.

first of, you'll have cross product of 2 tables. cleanest way have 2 tables in from statement. you'll this:

select sum(number_of_view) view_number, author, badge table1, table2 

this each row first table once each badge. haven't summed views yet, that's next thing do. first try this:

select sum(number_of_view) view_number, author, badge table1, table2 group number_of_view 

however, you'll see view counts aren't right , we'll see 1 badge per person, while wanted row each badge each user. in fact, badges grouped well, why displayed number_of_view actual number of view(s) times number of badges in system. fix add badge group column, doesn't squashed other results:

select sum(number_of_view) view_number, author, badge table1, table2 group number_of_view, badges 

if second table has id column, use instead because better performance (depending bit on actual type used badge field), there none in table give , work.

now, need filter out badges user hasn't earned make sure right badges left. go this:

select sum(number_of_view) view_number, author, badge table1, table2 group number_of_view, badges having number_of_view >= table2.`from` ,    number_of_view <  table2.to 

(note had escape from, keyword in sql.) here find, though, query doesn't know table2.from column. because having part of query doesn't @ tables (where that) looks @ selected columns instead (since filtered group by). so, need add these columns selected ones:

select sum(number_of_view) view_number, author, badge, `from`, table1, table2 group number_of_view, badges having number_of_view >= table2.`from` ,    number_of_view <  table2.to 

we want. can see query in action here: http://sqlfiddle.com/#!8/e78c8/1