mysqli - Determine ranking with single mysql query -



mysqli - Determine ranking with single mysql query -

i selecting set of items table , determine ranking display on page, code selecting items:

`<? $attra_query=mysqli_query($link, "select * table category ='4'"); if(mysqli_num_rows($attra_query)> 0){ while($attra_data=mysqli_fetch_array($attra_query,1)){ ?>`

in while loop determine ranking each of items so:

select count(mi.location) + 1 rank table m left bring together ( select id,location,country, round(coalesce(total_rating/total_rating_amount,0),10) rating_per_vote table category = '4' ) mi on mi.location = m.location , mi.country = m.country , mi.rating_per_vote > round(coalesce(m.total_rating/m.total_rating_amount,0),10) m.id = '$attra_id';

i figure highly inefficient, there way combine 2 queries single 1 don't have run ranking query each item separately ?

//edit sample data:

id | location | country | category | total_rating | total_rating_amount 1 berlin de 4 12 2 2 munich de 4 9 1

vote scheme 1-10 points, sample info berlin has received total rating of 12 2 votes, munich has received rating of 9 1 vote, berlin have rating of 6/10 , munich rating of 9/10 , hence should ranked #1

select count(m.id) rank, m.id (select * table category = '4') m left bring together ( select id,location,country, round(coalesce(total_rating/total_rating_amount,0),10) rating_per_vote table category = '4' ) mi on (mi.location = m.location , mi.country = m.country , mi.rating_per_vote > round(coalesce(m.total_rating/m.total_rating_amount,0),10)) or mi.id=m.id grouping m.id

this should suppose. don't know if best possible solution.

mysql mysqli

Comments

Popular posts from this blog

formatting - SAS SQL Datepart function returning odd values -

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) -

php - Yii 2: Unable to find a class into the extension 'yii2-admin' -