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
Post a Comment