how to build a dynamic runtime query in mysql php?

Question

I do not know how to classify this question. Vaguely, its about using calculated value in the WHERE clause of a mysql query performed using a php script.

Here's the scenario - I've a mysql table with structure like this: table_id[int], item_id[int], item_rating[int] Now the item_rating column can have either a "1" or a "0" value in it. The table_id column is set to auto_increment and the item_id column can have duplicate values also.

So a typical table will look like below -

table_id item_id item_rating
1           item1     1
2           item5     0
3           item1     1
4           item1     1
5           item5     1
6           item1     0

What i intend to do i for each item_id, i count the number of item_rating = 1 and item_rating = 0 and then take the difference of item_rating values to get the final rating for that item (final_item_rating = item_rating(with value=1) - item_rating(with value=0) ).

Now the issue: I have a php script that takes values from these tables, and then displays the item details ordered on the "final_item_rating" value - something like: select * from table_name order by final_item_rating desc

only problem is, since this final_item_rating is not a column in itself, and is actually based on run time value of the query, how do i build a query?

hope i have the question clear :)

Pushpendra · Accepted Answer · 2011-05-21 07:21:04Z

2

This query may help you:

SELECT sum(item_rating) AS SumRatings
FROM table_name
WHERE item_rating=1
GROUP BY item_id
ORDER BY SumRatings;

answered May 21, 2011 at 7:21

Pushpendra

4,4025 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andomar Over a year ago

The question asks for item_rating(with value=1) - item_rating(with value=0), but this query does not count value=0 as a negative

arun nair Over a year ago

thanks everyone, this one really helped! i did some modification to the db structure, and replaced all 0 with "-1" so that counting/ summation can be done in one step. i used a slightly modified version of the above query supplied by @Pushpendra; the modified one is as below - 'SELECT t_painting_feedback.painting_id, sum(t_painting_feedback.rating) AS happpiness FROM t_painting_feedback WHERE rating in ('1', '-1') GROUP BY painting_id ORDER BY happpiness DESC'

Andomar · Accepted Answer · 2011-05-21 07:13:17Z

0

This query would sum the ratings, and order the result with the highest rating on top:

select  item_id
,       sum(case when item_rating = 1 then 1 else -1 end) as rating
from    YourTable
group by
        item_id
order by
        sum(case when item_rating = 1 then 1 else -1 end) desc

answered May 21, 2011 at 7:13

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

how to build a dynamic runtime query in mysql php?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related