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

Question

table1

id | author       | number_of_view |
---+--------------+-----------------
1  | john         | 10    
2  | Jack         | 20   
3  | Anna         | 80    
4  | Neri         | 100

Below I have a PHP script which checks MySQL database table and gives badges to users. The script works perfectly.

$from1=10;
$to1=15;

$from2=20;
$to2=30;

$query=mysql_query("SELECT SUM( number_of_view ) AS view_number, author FROM `table1`  GROUP BY 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 is that I want to get $from and $to variable values from database table:

table2

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

Here it is second query and PHP script for the second table:

$query2=mysql_query("SELECT * FROM table2");
while ($row= mysql_fetch_array($query2)) {
    $badgeName=$row['view_number'];
 //I would like to use this variable above. So instead of "echo 'special user';" I would like use "echo $badgeName" $from=$row['author'];
// I would like to use this variable above instead of $from1 and $from2 $to=$row['to'];
// I would like to use this variable above instead of $to1 and $to2 }

How can I do that?

I don't see a second query. Explain, how you'd like to use the query result in your second query. — SteAp
– SteAp, Commented Aug 10, 2013 at 0:46
You can do a subquery, e.g. SELECT id, first_name FROM student_details WHERE first_name IN (SELECT first_name FROM student_details WHERE subject='Science'). You'd have to provide more details to get a query that works for your particular situation but this is probably the general idea you want to use. — Craig W
– Craig W, Commented Aug 10, 2013 at 0:50

Jasper · Accepted Answer · 2013-08-10 03:32:28Z

I originally thought this was quite simple, but I was mistaken in that, as there are some complicating factors you have to be aware of.

First of, you'll have to do a cross product of the two tables. The cleanest way to do this is to have two tables in the FROM statement. You'll get something like this:

SELECT SUM(number_of_view) AS view_number, author, badge
FROM table1, table2

This will each row from the first table once for each badge. We haven't summed up the views yet, so that's the next thing we do. The first try is this:

SELECT SUM(number_of_view) AS view_number, author, badge
FROM table1, table2
GROUP BY number_of_view

However, you'll see now that the view counts aren't right and we'll see only one badge per person, while we wanted a row with each badge for each user. In fact, the badges are grouped as well, which is why the displayed number_of_view is the actual number of view(s) times the number of badges in the system. To fix this we do add the badge to the group by column, so it doesn't get squashed into the other results:

SELECT SUM(number_of_view) AS view_number, author, badge
FROM table1, table2
GROUP BY number_of_view, badges

If your second table has an id column, I would use that instead because it is better for performance (depending a bit on the actual type used for the badge field), but there was none in the table as give and this does work.

Now, we need to filter out the badges that a user hasn't earned to make sure that only the right badges are left. This would go something like this:

SELECT SUM(number_of_view) AS view_number, author, badge
FROM table1, table2
GROUP BY number_of_view, badges
HAVING number_of_view >= table2.`from`
AND    number_of_view <  table2.to

(Note that I had to escape from, as it is a keyword in SQL.) Here we will find, though, that the query doesn't know the table2.from column. This is because the HAVING part of a query doesn't look at the tables (WHERE does that) but looks at the selected columns instead (since that is what was filtered by GROUP BY). So, we need to add these columns to the selected ones:

SELECT SUM(number_of_view) AS view_number, author, badge, `from`, to
FROM table1, table2
GROUP BY number_of_view, badges
HAVING number_of_view >= table2.`from`
AND    number_of_view <  table2.to

We finally get what we want. You can see the query in action here: http://sqlfiddle.com/#!8/e78c8/1

sagibb · Accepted Answer · 2013-08-10 09:45:03Z

0

You should run a JOIN query between the two tables. I've modified table2 field names from "from" and "to" to "start" and "end", as FROM is a reserved word.

SELECT author, number_of_view, badge
FROM authors LEFT JOIN scores 
ON score >start AND score < end

Please notice that the above JOIN query will return all results from table1, even if they do not have a match in table2. The badge value in such cases will be NULL. If you do not want to get these values, you can replace the LEFT JOIN with an INNER JOIN.

edited Aug 10, 2013 at 9:45

answered Aug 10, 2013 at 0:55

sagibb

9562 gold badges9 silver badges21 bronze badges

1 Comment

sagibb Over a year ago

no problem, updated the answer. Not sure why you've used the GROUP BY function, as the values in table1 seem unique. In any case, you can add or remove it if necessary.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related