在数据库中按评级排序 - 在哪里放这个SQL？ （PHP / MySQL） [英] Sorting by ratings in a database - Where to put this SQL? (PHP/MySQL)

问题描述

OK - 我会直截了当的 - 这里是PHP代码：

 < h2>最高评分：< / h2> 
 
<？php 
 
 //我们的查询库
 $ query = $ this-> db-> query（SELECT * FROM code ORDER BY评级DESC）; 
 
 foreach（$ query-> result（）as $ row）{
？> 
< h3><？php echo $ row-> title。ID：; echo $ row-> id; ？>< / h3> 
< p class =author><？php $ query2 = $ this-> db-> query（SELECT email FROM users WHERE id =。$ row-> author）; 
 echo $ query2-> row（'email'）;？>< / p> 
<？php echo（$ this-> bbcode-> Parse（$ row-> code））; ？> 
 
<？php}？> 
  

对不起，这有点混乱，它还是一个草稿。无论如何，我研究了使用评级系统的方法 - 以前我有一个评级字段，你可以看到SELECT * FROM代码ORDER BY评级DESC。然而，我很快就意识到这样的计算平均值是不可行的，所以我创建了五个新的列 - rating1，rating2，rating3，rating4，rating5。所以当5个用户评价4颗星，rating4说5 ...这是否有意义？每个ratingx列都会计算评级的次数。

所以无论如何：我有这个SQL语句：

SELECT id，（ifnull（rating1,0）+ ifnull（rating2,0）+ ifnull（rating3,0）+ ifnull（rating4,0）+ ifnull（rating5,0））/ $ b $（rating1 IS NOT NULL）+（rating2 IS NOT NULL）+（rating3 IS NOT NULL）+（rating4 IS NOT NULL）+（rating4 IS NOT NULL）+（rating5 IS NOT NULL））AS平均FROM代码 / p>

再次凌乱，但嘿。现在我需要知道的是如何将该SQL语句合并到我的脚本中？理想情况下，您会认为整体查询将是SELECT * FROM code ORDER BY（那个真正长时间查询我刚才说的）DESC'但是我看不到工作...怎么办做吗查询，将结果存储在一个变量中，这样的东西？

如果没有任何意义的抱歉！但我非常感谢帮助：）

杰克

解决方案

你应该完全回到绘图板。

 <？php 
 $ query = $ this-> db-> query（SELECT * FROM代码ORDER BY评级DESC）; 
 foreach（$ query-> result（）as $ row）{
 $ this-> db-> query（SELECT email FROM users WHERE id =。$ row-> author ; 
} 
  

任何时候你在代码中看到这个，停止你正在做的事情，这是JOIN的，你几乎不想循环查询的结果，并从该循环中发出多个查询。

  SELECT code。*，users.email 
 FROM code 
 JOIN users ON users.id = code.author 
 ORDER BY评级DESC 
  

此查询将获取单个结果集中的所有数据，从而删除N + 1查询问题。

我没有解决你的其余问题，直到你清理你的问题，并澄清你想要做什么。

OK - I'll get straight to the point - here's the PHP code in question:

<h2>Highest Rated:</h2>

    <?php 

        // Our query base               
        $query = $this->db->query("SELECT * FROM code ORDER BY rating DESC");

        foreach($query->result() as $row) {
    ?>  
        <h3><?php echo $row->title." ID: ";echo $row->id; ?></h3>
            <p class="author"><?php  $query2 = $this->db->query("SELECT email FROM users WHERE id = ".$row->author);
echo $query2->row('email');?></p>
            <?php   echo ($this->bbcode->Parse($row->code)); ?>

        <?php } ?>

Sorry it's a bit messy, it's still a draft. Anyway, I researched ways to use a Ratings system - previously I had a single 'rating' field as you can see by SELECT * FROM code ORDER BY rating DESC. However I quickly realised calculating averages like that wasn't feasible, so I created five new columns - rating1, rating2, rating3, rating4, rating5. So when 5 users rating something 4 stars, rating4 says 5... does that make sense? Each ratingx column counts the number of times the rating was given.

So anyway: I have this SQL statement:

SELECT id, (ifnull(rating1,0) + ifnull(rating2,0) + ifnull(rating3,0) + ifnull(rating4,0) + ifnull(rating5,0)) / ((rating1 IS NOT NULL) + (rating2 IS NOT NULL) + (rating3 IS NOT NULL) + (rating4 IS NOT NULL) + (rating5 IS NOT NULL)) AS average FROM code

Again messy, but hey. Now what I need to know is how can I incorporate that SQL statement into my script? Ideally you'd think the overall query would be 'SELECT * FROM code ORDER BY (that really long query i just stated) DESC' but I can't quite see that working... how do I do it? Query, store the result in a variable, something like that?

If that makes no sense sorry! But I really appreciate the help :)

Jack

解决方案

You should go back to the drawing board completely.

<?php
$query = $this->db->query("SELECT * FROM code ORDER BY rating DESC");
foreach($query->result() as $row) {
    $this->db->query("SELECT email FROM users WHERE id = ".$row->author;
}

Anytime you see this in your code, stop what you're doing immediately. This is what JOINs are for. You almost never want to loop over the results of a query and issue multiple queries from within that loop.

SELECT code.*, users.email 
FROM code
JOIN users ON users.id = code.author
ORDER BY rating DESC

This query will grab all that data in a single resultset, removing the N+1 query problem.

I'm not addressing the rest of your question until you clean up your question some and clarify what you're trying to do.

这篇关于在数据库中按评级排序 - 在哪里放这个SQL？ （PHP / MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！