按数据库中的评级排序 - 在哪里放置此SQL？ （PHP / MySQL的）

Question

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

Answer 1

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.